Postgresql – What option we can disable temporary in PostgreSQL make database update faster

loggingpostgresqlupdate

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

Thanks

Best Answer

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

Related Solutions

PostgreSQL Trigger – Best Approach for Efficient Data Insertion

The fragment you posted so far can be simplified to:

INSERT INTO table2 (id, name, date)  -- why "date" if you insert a timestamp?
SELECT NEW.id, t1.name, NEW.timestamp
FROM   table1 t1
WHERE  ST_DWithin(NEW.position
                , ST_SetSRID(ST_MakePoint(t1.longitudedecimal, t1.latitudedecimal), 4326)
                , 0.01447534783)
AND    t1.id > 0;  -- probably redundant!

IF NOT FOUND THEN ...

It is very inefficient to run separate queries with assignments in plpgsql instead of a single query.
Basic type names like date or timestamp lead to confusing error messages and other conflicts, id and name are the worst possible column names, non-descriptive and with countless duplicates all over your tables. Revisit your naming convention ...

You probably do not need any of this. Inserting millions of rows shouldn't be handled by triggers which are fired for each row. Extremely expensive. You need a set-based solution without triggers.

Probably best to COPY to a tmeporary staging table and INSERT / UPDATE from there. But basic information is missing.

Thesql update query almost 5 minutes slower in 5.6 vs 5.5

OPTIMIZE TABLE is essentially never needed for InnoDB.

New with 5.6: ALTER TABLE uses ALGORITHM=INPLACE for many actions (add/drop column/index, etc.). I would expect 5.6 to be faster.

What is that list of commands trying to do? Something to do with Normalization? Seems like it could be done in a couple of queries, without needing the extra table.

innodb_buffer_pool_size should be set to about 70% of available RAM. That will improve performance on any version.

Don't index flags (deleted), it will slow down INSERTs, without speeding up SELECTs.

Don't use 4-byte integers (INT) for flags; use the 1-byte TINYINT.

No currency in the world needs 10 decimal places: price decimal(23,10). And I doubt if room for 10 trillion 'dollars' is needed.

What's the value of autocommit during the UPDATEs?

Best Answer

Related Solutions

PostgreSQL Trigger – Best Approach for Efficient Data Insertion

Thesql update query almost 5 minutes slower in 5.6 vs 5.5

Related Question