It’s absolutely normal to have “holes” in table identity sequences - even if you never deleted a row. I wrote about this in this earlier answer .
PostgreSQL sequences, at their discretion, are exempt from normal transactional rules. If they were not then, then only one transaction at a time could get the identifier, so you can never have more than one session inserted into the table at a time. This will result in reduced performance.
This is explained in the PostgreSQL tutorial on calling nextval - a call that gets values from sequences:
It is important . To avoid blocking concurrent transactions that receive numbers from the same sequence, the nextval operation is never rolled back; that is, after a value has been selected, it is considered used even if the transaction that later made the next one is aborted. This means that interrupted transactions can leave unused "holes" in the sequence of assigned values.
In theory, PostgreSQL can maintain a list of deleted, abandoned, and unused identifiers, but in practice this is prohibitively expensive in terms of performance and extremely difficult to implement. When an application receives an id with nextval , it can use it at any time in the future, and some applications use this approach, caching identifier blocks for better internal concurrency.
Consider the generated identifiers as a unique line number - and that’s it. This does not tell you how many lines there are. This does not tell you if one row was inserted after another row (for this you can use the created_time , possibly supported by a trigger). It does not tell you whether one row was committed after another row (the xmin columm system tells you that with certain restrictions). All this tells you how to find the string.
Cm: