9

I have tried doing some research for quite a while on the performance implications of the built-in upsert (INSERT ... ON CONFLICT UPDATE...) when a lot of upserts are made. The scale is something like 1 million records/hour, that is split up in groups of around 300 records each.

The table is fairly simple, just a few int/bool fields and a date field. The total size of the table is somewhere between 50-60 million records. Essentially, all the rows are supposed to stay up-to-date within a certain cycle (that is currently about 2 days). Sometimes a lot of information changes, sometimes very little/no information changes (e.g. 300 records get upserted but they are identical to the existing records).

So far, one hypothesis is that this project seems to be suffering from the large amount of writes that happen constantly since even if the upsert results in no inserts/updates, the "failed" inserts from the upsert will still get written somewhere (according to our knowledge). Therefore, the idea is to utilize old-fashioned upserts (writeable CTEs) and do more granular operations that can make sure to only insert data that doesn't already exist, and only update data that has actually changed. Naturally, however, this will put more read-load on the DB and increase query complexity.

The question is, are we right in our assumptions that the built-in upsert is useless from a performance perspective (e.g. it's only good for much smaller tasks) or are we wrong? I read a bit about HOT updates and autovacuum tuning, but nothing that references something more similar to this question.

Worth mentioning is that this DB (PostgreSQL 10.9) is running on Heroku so we are not able to tune it to our needs. We are planning to move at some point, depending on how important it ends up being. Finally, it is also worth mentioning that this DB also has one follower (i.e. read replica).

Would really appreciate some good insight on this! Thanks beforehand.

Improve this question

1 Answer 1

Reset to default
19

INSERT ... ON CONFLICT is the fastest way to upsert data. If it is fast enough for you can only be seen from a performance test.

It matters a lot if you use ON CONFLICT DO NOTHING or if you use an UPDATE clause:

With DO NOTHING, there won't be any dead tuples that have to be cleaned up. If you perform an UPDATE, there will be a dead tuple, and cleaning up these dead tuples may turn out to be the bottleneck in your processing.

Use a fillfactor of 70 or so and make sure that none of the updated columns are indexed. Then you can get HOT updates which may save your day.

Improve this answer
6
  • I see. Aren't dead tuples created from failed inserts? I thought it first wrote the data to somewhere, then checked conflicts, and if conflicted, mark the data as dead. The rest of what you said makes sense, I will have to read up on the fillfactor stuff, thanks for your help!
    – fgblomqvist
    Commented Sep 5, 2019 at 11:53
  • 1
    That is not a failed insertm and no dead tuple will be generated. It is called a speculative insert.
    – Laurenz Albe
    Commented Sep 5, 2019 at 12:50
  • Oh okay, that's good. Are there any cons of setting a lower fillfactor? It sounds like it might cause more pages having to be loaded into memory since less data fits on each page. I suppose that's not necessarily a bad thing though if we can avoid having to aggressively auto vacuum.
    – fgblomqvist
    Commented Sep 5, 2019 at 14:12
  • 1
    Right. fillfactor wastes some space, but you'll gladly pay that price if it allows you to to survive lots of updates. Remember that this is not necessary if you use ON CONFLICT DO NOTHING.
    – Laurenz Albe
    Commented Sep 5, 2019 at 14:15
  • Right okay yeah makes sense. Thanks again! :)
    – fgblomqvist
    Commented Sep 5, 2019 at 14:26

Your Answer

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

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