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.

  • Did you do already a timed test of both scenario's of the creation of the constraints? How frequent is this bulk update? How long does it take now? – Marco Jun 19 '18 at 12:45
  • @Marco: interesting comment. Let me work this out. Of course, i'll prepare this on an idle system, as the DB in an operative environment is under heavy updates – arthur Jun 19 '18 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.