Postgres sequences can skip 32 unexpectedly

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 &quot;follower&quot;, that batch is lost.<p>I consider ID numbers somewhat opaque, like GUIDs but maybe not <i>that</i> opaque. Fretting about gaps in ID numbers can cause hair loss. This is just one of many ways gaps can happen.<p>It is just an artifact of &quot;sequences&quot;, the database object in Postgres that autogenerates the &quot;next&quot; ID number for a column --- automatically set up if you declare a column of type &quot;serial&quot;, but that&#x27;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&#x27;s default value to nextval(sequence).<p>You can mitigate gaps somewhat, like if you&#x27;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:<p><pre><code> select setval(&#x27;sequence_name&#x27;, (select max(id) from table)); </code></pre> Further reading: <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;functions-sequence.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;functions-sequence.h...</a>

Good writeup! It&#x27;s an interesting gotcha because Postgres and SQLite docs expressly disclaim that their sequences&#x2F;AUTOINCREMENT are gapless but experienced and talented programmers still use them as such. Is the type of thing that doesn&#x27;t bite you until production.<p>Postgres docs-- <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;13&#x2F;sql-createsequence.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;13&#x2F;sql-createsequence.html</a> &gt; Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

This is why you should never expose your database IDs to the customer. They just complain about it, and it invites them wanting to assign meaning and have control over the values.

&gt; Sequences felt like a good use case for this when we started, [...] We’ve since moved to a different approach which enforces the behaviour we want more explicitly when creating incidents, and we learned something along the way.<p>The entire article shows that the author has a very good grasp as much of the technical side of development as of the business side. And the conclusion makes a lot of sense.<p>Sequences are a technical solution for a technical problem, how to uniquely identify new elements in a fast and reliable manner. And it is optimized for such use case. But, in the real world, users have expectations and when their mental model conflicts with the inner working of an application you end creating confusing and lack of trust. Depending on the situation, to educate the user can be the path to follow, but here it seems reasonable to just adapt the inner workings to the mental model of the users. It&#x27;s faster and scales easier as the number of customers increases.

&gt; we don’t just want a monotonically increasing sequence<p>Be careful about treating sequences as monotonic. For transactions in progress at the same time, the order of sequence values might not be consistent with the order of transaction commits and the logical order of serializable transactions.<p>One example where that could cause problems is if you filter a change stream using the last seen id. For such an approach an out-of-order id would lead to missed events.

We have a similar system (multi-tenant database) where each tenant (account) has objects that have unique identifiers for that specific account (customers, locations, jobs, invoices, etc).<p>Customer #C1010 may have 2 locations #L1899 and #L8443 and many invoices #IN1940 and #IN2399 for example.<p>When we first built the system, I considered using native Postgres sequences to track these, but decided against them because of how they are affected during a rollback. In our system, each account has a record in a table that controls the next value of the sequence.<p>We have an event in our ORM to automatically generate the next sequence value as part of the transaction so if the transaction is rolled back, the next sequence value is as well. Sure, it requires locking the sequence record but it&#x27;s a very small table and generating a sequence is quick. We wrapped everything up in a stored procedure named generate_sequence() which returns the next value of the sequence and increments it. It&#x27;s scaled to millions of records quite well without issue.

Another interesting cause of skipped sequence numbers in postgres is that INSERT ON CONFLICT increments the sequence number even if the row already exists. If the UPDATE is more common than the INSERT case, this will waste more values that it uses. This can be undesirable, even when the absence of gaps isn&#x27;t strictly required.

Oracle does this too, so do some other databases.<p>It&#x27;s quite a natural optimisation.

I&#x27;m sure someone will correct me if I&#x27;m wrong, but isn&#x27;t it simpler to manage this sort of functionality by putting a counter in a table and using a CTE to increment it along with the insert? Something like<p><pre><code> with u as ( update organizations set last_external_id = last_external_id + 1 where id = 123 returning last_external_id ) insert into incidents (organization_id, external_id, title) select 123, last_external_id, &#x27;Blah blah blah&#x27; from u; </code></pre> The risk here of course is running into lock contention around the organization table there&#x27;s a high volume of incident creation for the same organization, but considering the context (incident management) that seems pretty low risk.

Kevin Loney once gave an excellent talk, &quot;Controlled Flight into Terrain&quot;, at an Oracle users group conference. As I recall, one of the cases he mentioned of self-inflicted damage involved the company that wanted strict, gapless order for an ID column. It would have been entirely possible to avoid ID conflicts by using the INCREMENT BY feature of Oracle sequences, but the company had European systems fetching the next ID value from a server in the US.<p>I sympathize with customers who don&#x27;t expect gaps of 20 (Oracle) or 32 in ID sequences. But the relational model is one of unordered tuples, isn&#x27;t it?

Nice write-up. Something does not add up here though. Primary writes 32 ahead to the WAL when fetched the first time, then keeps a counter (log_cnt) which it decreases each time nextval is called. So, when sequence was initialized, nextval is 1 and WAL has 32. The replica sees 32 as fetched offset. How does incident sequence switch from 7 to 39? Shouldn&#x27;t it be 33 when the replica was made the primary? Same for incident 20 -&gt; 52, shouldn&#x27;t it be 33 when replica was made primary? Unless I am missing something here, i.e, 32 is added to the nextval and nextval is logged each time (20 or 7).

Yep, already walked that path with invoice numbers.<p>The only thing you can say for sure about a sequence is the next number will be greater, which is not good enough for many kinds of identifiers.

This was raised as a bug back in 2004, but the thread shows the contributors didn&#x27;t consider it so: <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;message-id&#x2F;20040419231413.EE480CF567F@www.postgresql.com" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;message-id&#x2F;20040419231413.EE480CF...</a>

Yeah, the main purpose is just distinctness. Allowing it to jump ahead helps it guarantee this.

Why do customers notice this? Does the I&#x27;d have significance to their use case?

The initial design was quite flawed, in addition to not using sequences they should not use one DB object per organization, but rather a single object with an &quot;organization&quot; field.

The article was interesting but I was disappointed it didn’t go into more detail on what the final solution was.

as someone who is somewhat anxious and apparently prone to hopefully temporarily hairless when stress hits the fan, what is the patch or best practices to avoid such failure? outside of using uuid for ids?

Isn&#x27;t creating a sequence a bad idea in general, anyway?<p>Aren&#x27;t there a zillion ways to compromise things if you know that some field is a sequence?