Why are my entity's ID's skipping numbers?

This is a particularity of PostgreSQL, which is our database on the backend.

Here is a document you might find interesting, particularly this bit:

Can There Be “Gaps” In The Values Generated By A Sequence?

Yes, there can. Sequences are intended for generating unique identifiers — not necessarily identifiers that are strictly sequential. If two concurrent database clients both attempt to get a value from a sequence (using nextval()), each client will get a different sequence value. If one of those clients subsequently aborts their transaction, the sequence value that was generated for that client will be unused, creating a gap in the sequence.

This can’t easily be fixed without incurring a significant performance penalty. For more information, see Elein Mustein’s “Gapless Sequences for Primary Keys” in the General Bits Newsletter.

Further to this, as an optimization in certain circumstances, a process may want to prefetch some ids. If these ids are not all consumed by the process, they are dropped because the sequence has since moved past those ids. This will result in larger or and apparently regular gaps on the boundaries of the prefetched id groups.

I can understand this might look alarming at first when you’re not used to this database, but this is definitely an implementation detail you shouldn’t worry about.

1 Like