I am doing bulk UPSERT on a table with 30 million rows. The table has just two columns (varchar as a primary key and integer). The input data is imported to the temporary table first, and then bulk upsert is executed (using INSERT ... ON CONFLICT DO UPDATE statement). Batch size is 4000.
My question is - what performance tips can you give me? When the table was smaller (5-10 million records), the performance was good enough. With 30 million rows it is not good enough, single bulk of 4000 records lasts from 2 to 30 seconds.
Of course, I have few services which do this import in parallel, so I am using advisory locks to synchronize them (only one bulk upsert is being executed at a time). Should I remove advisory locks to execute upserts in parallel? Then I will have to handle deadlocks (and use smaller batch size to reduce deadlock chance?).
What can I do to increase performance of bulk upsert?
Here is my_big_table:
CREATE TABLE my_big_table ( sender VARCHAR(30) PRIMARY KEY, count INTEGER NOT NULL DEFAULT 0 ) WITH ( fillfactor = 80, autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 40000 );
Here is the UPSERT query:
INSERT INTO my_big_table AS MBT (sender, count) SELECT destination, count(*) as received_count FROM my_temp_table GROUP BY destination ON CONFLICT (sender) DO UPDATE SET count = MBT.count + excluded.count;
Here is execution plan: https://explain.depesz.com/s/HGfw
- I am using Postgres version 11.2
- my_big_table has fill factor of 80%
- my data set cannot fit into memory (RAM)