[CapriciousCptl]

Good writeup! It's an interesting gotcha because Postgres and SQLite docs expressly disclaim that their sequences/AUTOINCREMENT are gapless but experienced and talented programmers still use them as such. Is the type of thing that doesn't bite you until production.

Postgres docs-- https://www.postgresql.org/docs/13/sql-createsequence.html > Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.