6 ms·

Great to see! My biggest gripe with ON CONFLICT upserts are the IDs (sequences) having gaps in them. Any good ways to prevent that?

by throw03172019 4y ago

loloquwowndueo 4y ago
Don’t rely on sequences having no gaps? Why do you even need that?
- leetbulb 4y ago
  for i smell jank code
- throw03172019 4y ago
  I don’t rely on them having no gaps. If the sequence is an integer, it’s much quicker to wrap if the table gets a lot of upserts.
- btgeekboy 4y ago
  If they're not accounting for it, integer overflow could be a real problem. (And it's not clear they would account for it in a table that normally has 10s of thousands of entries.) Someone else mentioned it here too: https://news.ycombinator.com/item?id=33242669 https://news.ycombinator.com/item?id=33242669
  - eusto 4y ago
    bigint is the default data type for sequences and it's highly unlikely that somebody would reach an overflow with it. I've worked on various database solutions, both rdbms and analytical and I find sequences to be one of the most misunderstood features in the industry. The only guarantee they make is that they generate unique values. Some of the newer distributed rdbms don't even guarantee they'll be monotonic. Relying on them generating consecutive values is a sure way to get vendor lock-in to whatever database has made that guarantee.
    - richbell 4y ago
      The problem isn't that values aren't consecutive, it's that the sequence is exhausted quicker than you'd expect. Someone could define a PK as `int4 GENERATED ALWAYS AS IDENTITY` because their expected number of entries is below the cap, only for that cap to be exhausted due to the behavior (in fact, that's been mentioned a few times). It 'violates' the principle of least surprise. Intuitively you'd expect `ON CONFLICT .... DO NOTHING` to do nothing, but it will increment the PK every time.
    - btgeekboy 4y ago
      I wouldn’t be worried about the sequence itself running out, but the column it’s stored in in the table may not be large enough to handle those numbers.
richbell 4y ago
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.
- 4y ago
Merad 4y ago
Based on some quick testing MERGE does not appear to cause id gaps, though that does make me wonder if it's able to do so by providing less strict guarantees than ON CONFLICT.
dewey 4y ago
This only seems to be an issue from an ideological point of view and not a technical one. On small projects it bothered me that I can't immediately see if something is an old entry or a new one just by glancing at the ID. In any bigger project it's just noise and IDs are not helpful information anyway, not even at a glance. It's better to just ignore them, use bigint and move on.

Great to see! My biggest gripe with ON CONFLICT upserts are the IDs (sequences) having gaps in them. Any good ways to prevent that?

loloquwowndueo 4y ago

Don’t rely on sequences having no gaps? Why do you even need that?

leetbulb 4y ago
for i smell jank code
throw03172019 4y ago
I don’t rely on them having no gaps. If the sequence is an integer, it’s much quicker to wrap if the table gets a lot of upserts.
btgeekboy 4y ago
If they're not accounting for it, integer overflow could be a real problem. (And it's not clear they would account for it in a table that normally has 10s of thousands of entries.) Someone else mentioned it here too: https://news.ycombinator.com/item?id=33242669 https://news.ycombinator.com/item?id=33242669
- eusto 4y ago
  bigint is the default data type for sequences and it's highly unlikely that somebody would reach an overflow with it. I've worked on various database solutions, both rdbms and analytical and I find sequences to be one of the most misunderstood features in the industry. The only guarantee they make is that they generate unique values. Some of the newer distributed rdbms don't even guarantee they'll be monotonic. Relying on them generating consecutive values is a sure way to get vendor lock-in to whatever database has made that guarantee.
  - richbell 4y ago
    The problem isn't that values aren't consecutive, it's that the sequence is exhausted quicker than you'd expect. Someone could define a PK as `int4 GENERATED ALWAYS AS IDENTITY` because their expected number of entries is below the cap, only for that cap to be exhausted due to the behavior (in fact, that's been mentioned a few times). It 'violates' the principle of least surprise. Intuitively you'd expect `ON CONFLICT .... DO NOTHING` to do nothing, but it will increment the PK every time.
  - btgeekboy 4y ago
    I wouldn’t be worried about the sequence itself running out, but the column it’s stored in in the table may not be large enough to handle those numbers.

richbell 4y ago

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.
4y ago

Merad 4y ago

Based on some quick testing MERGE does not appear to cause id gaps, though that does make me wonder if it's able to do so by providing less strict guarantees than ON CONFLICT.

dewey 4y ago

This only seems to be an issue from an ideological point of view and not a technical one. On small projects it bothered me that I can't immediately see if something is an old entry or a new one just by glancing at the ID. In any bigger project it's just noise and IDs are not helpful information anyway, not even at a glance. It's better to just ignore them, use bigint and move on.