Hacker News Reader

Comment by richbell | Hacker News Reader

This is my gripe as well. We used `ON CONFLICT ... DO NOTHING` for a table with ~10k entries that are frequently upserted.

By the time we reached a few thousand entries, we had primary keys in the millions. I personally don't care that there are gaps in the sequence, but gaps of hundreds of thousands definitely leaves a lot to he desired. I think you can circumvent this behaviour by changing from `DO NOTHING` to `DO UPDATE` and doing a dummy write, but that too leaves much to be desired.

I also discovered that `ON CONFLICT` doesn't really work with a high number of concurrent writes. We had to implement our own upsert logic using advisory locks.

View on HN

If you're inserting a lot of data with a high likelihood of conflicts you're probably better off using `INSERT ... SELECT .. WHERE NOT EXISTS` instead, that way you can have gapless sequences.

    -- Will leave gaps
    INSERT INTO t (val) VALUES ('abc'), ('def') ON CONFLICT DO NOTHING;

    -- Won't leave gaps
    INSERT INTO t (val)
    SELECT * FROM (VALUES ('abc'), ('def')) AS tmp (val)
    WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.val = tmp.val);

https://www.db-fiddle.com/f/6s4uYA987owJxu3CEvfu8t/0

It's been a while but I'm pretty sure that's exactly what we ended up doing. The advisory lock was because downstream logic required returning the primary key — basically https://en.m.wikipedia.org/wiki/Double-checked_locking.

Thanks for sharing.

One of the weirdest outages I've ever had to debug was when one of our tables with millions of rows (but in the low millions, at least so we thought) overran the maximum value for a 32 bit signed integer, due to it being primarily updated with INSERT ON CONFLICT on a nightly basis. At the time we had no idea why the primary keys were climbing so high! That was a tough one to figure out.

re concurrent writes: The issue we ran into was deadlocks when we upserted batches of records within a transaction. E.g. transaction A updates row 1 then row 2 and transaction B updates row 2 then row 1. If you sort so that you update rows in the same order across all transactions, then it won't deadlock. E.g. sort records by primary key and then upsert. If you can't do that, then advisory locks seems like a good idea.

Reply on news.ycombinator.com