Postgresql Sequence Hopping

Question

I have recently migrated (dump and restore) my database from AWS RDS PostgreSQL v12 to Aurora Serverless v2 PostgreSQL v14.6. The issue is we have setup sequencing in a table on old RDS, now in the new serverless v2 the sequencing is hopping from one digit to another. Hopping Example - 1,2,3,4,5,20,21,22,23,24,25,32. I have tried to delete the old sequence and created new one, but no luck.

Please let me know your thoughts in sorting this issue.

Answer 1

That is expected behavior with sequences. They skip values whenever there is a crash, an error, or even when you disconnect from the database (depending on your CACHE setting). See the warning in the documentation:

To avoid blocking concurrent transactions that obtain numbers from the same sequence, the value obtained by nextval is not reclaimed for re-use if the calling transaction later aborts. This means that transaction aborts or database crashes can result in gaps in the sequence of assigned values. That can happen without a transaction abort, too. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Thus, PostgreSQL sequence objects cannot be used to obtain “gapless” sequences.

Read my article for details.