Every now and then I need to update all the records (of one attribute) in a very large table. My default strategy is to create a new table, fill all the new values, create indexes, and rename the table to the new one at the end of the transaction.
This gives a huge performance speed up compared to a simple
update statement (for a 5GB sample case 63 sec vs 111 secs; for a real system 10 mins compared to 4 hours). I believe this is because postgres is able to employ sequential writes compared to random read and writes in update of all records. Is it possible to tell to postgres by sacrificing visibility / MVCC of the version of the rows (blocking all queries that depend on the table of interest?)
Having a simple check constraint (not null, or integer is positive) slows down the bulk insert/upload dramatically. How should one deal with such simple constrains (foreign, primary, unique key, etc. constrains I create after the bulk update)? Should one add those constrains during the creation of the table, or add afterwards, are there any other options?
For the toy example above (six not null constraints), having constraints in create table statement resulted in 110 secs or running time. Adding constrains afterwards was 115 secs. I wonder why operative environment changed the numbers so dramatically.