Database upgrade made incident IDs skip 32 numbers
Multiple customer organizations saw their incident identifiers jump by up to 32 (e.g. from #INC-20 to #INC-52), eroding trust in the numbering system, though no data was lost or misattributed.
Never expose raw Postgres sequences as meaningful user-facing identifiers — sequences do not guarantee gap-free increments and any failover, crash, or upgrade can silently advance them by up to 32; use an explicit MAX+1 approach instead.
A routine database upgrade that spun up a follower node and promoted it to primary — the follower was carrying Postgres sequence values up to 32 ahead of the old primary, because Postgres pre-allocates 32 sequence values before writing them to the WAL.
Postgres pre-logs 32 sequence values in advance to avoid logging every single increment → follower nodes always track a state up to 32 ahead of the current committed value → promoting the follower as the new primary during the upgrade made all per-organization incident sequences jump by up to 32 → customers saw large unexpected gaps in their incident numbering.
How would you implement a per-organization counter that guarantees sequential, gap-free identifiers even across database failovers, follower promotions, and transaction rollbacks?
