3 ms·

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 thousan

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.

singron 4y ago
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.
SPBS 4y ago
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 https://www.db-fiddle.com/f/6s4uYA987owJxu3CEvfu8t/0
- richbell 4y ago
  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 https://en.m.wikipedia.org/wiki/Double-checked_locking. Thanks for sharing.
btown 4y ago
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.

singron 4y ago

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.

SPBS 4y ago

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 https://www.db-fiddle.com/f/6s4uYA987owJxu3CEvfu8t/0

richbell 4y ago
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 https://en.m.wikipedia.org/wiki/Double-checked_locking. Thanks for sharing.

btown 4y ago

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.