On 6/23/20 8:32 AM, Jason Ralph wrote:
Hello List,
PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (R
ed Hat 4.4.7-23), 64-bit
I am planning an update on a table with 20Million records, I have been
researching the best practices. I will remove all indexes and foreign
keys prior to the update, however I am not sure if I should use a
transaction or not.
My thought process is that a transaction would be easier to recover if
something fails, however it would take more time to write to the WAL log
in a transaction.
Are you updating *every* row in the table?
Are you updating indexed fields? (If not, then leave the indexes and FKs,
since they won't be touched.)
Would it make sense to make a back up of the table then execute update
without a transaction?
Always make a backup.
How would you guys do it?
It depends on what percentage of the rows are being updated, which columns
are being updated and how big the records are.
--
Angular momentum makes the world go 'round.
