I want to update 3 million records within some seconds. i have tried doing below things

ALTER TABLE imagingresourceplanning.opnav_fact_revenue_costs SET UNLOGGED ;    
ALTER TABLE imagingresourceplanning.opnav_fact_revenue_costs DISABLE TRIGGER ALL;   

once the update is done i am reverting this settings. with this change i have noticed that my update time is now 2 minutes which was eariler taking 10 min.

What other settings i can disable in PostgreSQL to make update even more faster. Data in my table is inserted and update by nightly job.

My postgresql version is 9.5


  • Note the risks in what you're doing. You cannot have a valid backup set that will allow an emergency restore including the updated data (fixable as soon as you can run a full backup). Similarly, any activity performed by the triggers will not be performed. You should look at the changes you're making and what the triggers do, to make sure that the trigger code is really unneeded here.. If some (but not all) of it is necessary, you can try to incorporate it into your update code. – RDFozz Nov 6 '17 at 18:05

You should be able to increase your update speed by "disabling" foreign keys check as explained here.

|improve this answer|||||
  • 1
    That assumes that user2274074 is actually updating a FK column – a_horse_with_no_name Nov 6 '17 at 15:28

PostgreSQL maintains a list of optimizations you could make, all of which come at a cost of durability over time.

|improve this answer|||||

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.