Hacker News Reader

Comment by btgeekboy | Hacker News Reader

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

View on HN

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.

Reply on news.ycombinator.com