Inserted Primary IDs not sequential

[OskarEichler]

After performing an import our primary IDs are jumping up like crazy. Instead of inserting sequential (1,2,3,4,5,6,7,8,9,10) it more looks something like (1,4,60,110,500,3000,5996,49999).

Is there a reason that IDs are being skipped? We are on Postgres 13.

[jkowens]

My best guess is that it is due to a transaction rollback or inserts with ON CONFLICT.

From PostgreSQL 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

If many rows are "updated" on conflict, then the computed sequence values will be lost. Unfortunately, I don't have a suggestion for resolving that issue.

[OskarEichler]

Ahh damn that makes sense - so mostly PostgreSQL behavior. Thanks for looking into this @jkowens 🙌🏼