Thank you for uncovering this edge case. To summarize, Postgres reserves a batch of 32 serial numbers from its sequence objects. Then, in the case of a crash, or the promotion of a "follower", that batch is lost.

I consider ID numbers somewhat opaque, like GUIDs but maybe not that opaque. Fretting about gaps in ID numbers can cause hair loss. This is just one of many ways gaps can happen.

It is just an artifact of "sequences", the database object in Postgres that autogenerates the "next" ID number for a column --- automatically set up if you declare a column of type "serial", but that's all a serial column is. Serial just means: make the column of type integer, and create a sequence for it, and set the column's default value to nextval(sequence).

You can mitigate gaps somewhat, like if you're testing and retesting a bunch of inserts, rolling back each time. Well, after a few tests, the next number in the sequence is far beyond the last number in the table. So you can call another sequence function, setval, to reset it. Something like:

   select setval('sequence_name', (select max(id) from table));

Further reading: https://www.postgresql.org/docs/current/functions-sequence.h...