PostgreSQL Sequences: A Simple Way to Spot Unexpected Gaps
Sequence gaps in PostgreSQL are often attributed to transaction rollbacks or concurrent access, but a lesser-known cause can be reproduced with surprising ease. Consider a basic table and sequence setup:
check_seqwith an integer ID and two attribute columnscheck_seq_seqconfigured to increment by 1, starting at 1, with no cycle
The quirk emerges when using jsonb_populate_record to insert rows. By building a JSON object that calls nextval() for the ID field, then populating the record from that JSON, the sequence advances in a way that can leave visible holes in the ID assignment.
Executing two identical inserts with this method yields a result that shows the gaps plainly, as evidenced by the linked output image. The pattern demonstrates that the JSON population path interacts with sequence calls differently than a direct nextval() insert would.
The takeaway: if you rely on jsonb_populate_record for inserts, verify your sequence behavior — gaps may appear where you least expect them, without any rollback or concurrent transaction involved.
