5

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

Additional info:

  • I am using Postgres version 11.2
  • my_big_table has fill factor of 80%
  • my data set cannot fit into memory (RAM)
Improve this question
7
  • Please share the full definition of you table (with indexes, triggers if there are any, and so on). Also, check bloat there.
    – dezso
    Apr 17, 2019 at 9:46
  • I did. I have solve the bloat problem, there is no more than 50-60k dead tuples in that table at a time. The execution plan (that I put here) was executed when autovacuum process was not running on that table.
    – elBastarde
    Apr 17, 2019 at 10:06
  • Hm, you mention a timestamp column, too, but it's not shown in the table definition... And how do you achieve batching?
    – dezso
    Apr 17, 2019 at 11:44
  • There is no timestamp column, my bad (I have edited the post). Batching is achieved in service, I have in memory queue with records, and the records are imported in 4k batches - take 4k records from the queue, import them to the temporary table and then execute upsert from temporary table. And I have several services which do that...
    – elBastarde
    Apr 17, 2019 at 11:57
  • Can you put some number on the sizes? How big is the tabe, the index, shared_buffers, and RAM? What kind of IO capacity do you have?
    – jjanes
    Apr 17, 2019 at 22:44

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.