Hacker News Reader

Comment by throw03172019 | Hacker News Reader

throw03172019

Great to see!

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

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.

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.

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.

Don’t rely on sequences having no gaps?

Why do you even need that?

for i smell jank code

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.

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

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.

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.

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.

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.

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.

Reply on news.ycombinator.com