One, Two, Skip a Few...

Incident.io experienced an issue with their incident identifiers appearing to jump unexpectedly due to Postgres sequences running ahead of the number of records in the database. The company initially suspected transaction rollbacks as a cause, but later discovered that follower nodes can see the state "that would prevail on the primary if it were to crash", which led to sequence values jumping up to 32 numbers forward during a database promotion process after an upgrade. To fix this issue, they implemented a trigger that runs before insert and looks for the maximum external ID value in the incidents table for a given organization, then increments it by one.