[andorov]

Thank you for your work! Postgres is great to use and the regular release of useful features has been a very nice benefit.

One unobvious (to me) but possibly necessary side effect of the INSERT...ON CONFLICT pattern is that it reserves a primary id for every row ahead of time, presumably because it does not know what will be updated and what will be inserted. We have a heavily updated table that started failing inserts one day because the primary id sequence hit the max int (2.1b), despite the table only reaching 100mm rows. The immediate fix was to reset the sequence to -1 and have it start going down...

[petergeoghegan]

I hear that one from time to time. It's a consequence of the fact that sequences are generally not transactional, and the fact that the underlying "speculative insertion" infrastructure really does have to do most of the work of the insertion before it can decide whether or not the insertion really should go ahead.

I'm sympathetic, but unfortunately I cannot think of any tractable way of avoiding the problem at the implementation level. Maybe it would be possible to do something with an identity column (as opposed to a SERIAL column or a raw sequence) -- we have access to sufficient context there. Maybe we could skip consuming a sequence in the common case where there is a clear conflict at the start, and the sequence isn't accessed through the EXCLUDED pseudo table. We wouldn't actually promise anything more, but in practice we wouldn't burn through sequences at the same rate in cases where updates are common.

This is quite a significant project, but maybe it'll happen some day.

[edoceo]

insert isn't really reserving so much as just getting nextval from the seq, and may have to nextval a second time. I've found for tables like this to NOT use int-pk - I use ULIDs for these kinds (and actually, I've been using ULID for like everything lately)