Postgres sequences hand out numbers with a cache
A PostgreSQL sequence cache reserves values for a database backend, not for an application object or one transaction. The distinction explains why the sequence’s stored state can run ahead of values actually returned, why reconnecting can create holes, and why making CACHE smaller cannot make identifiers gapless. Start by separating allocation state from the rows the application successfully committed. ...