A few years ago, I wrote a blog post about generating gap-free sequence numbers in PostgreSQL: Gap-Free Sequence
Recently, I introduced the technique described in that article to a developer, who came up with a more sophisticated approach. Instead of using a user-defined function to generate sequence numbers, he used a Common Table Expression (CTE) to allocate them directly within a SQL statement executed by the application.
Let's take a look at how his approach works.
Creating a Sequence Table We'll use a table called COM_SEQ to store a pool of pre-allocated sequence numbers. Each logical sequence is identified by a SEQ_NAME, and each number has a status indicating whether it is available for allocation.
--tested on PostgreSQL 18 CREATE TABLE COM_SEQ ( SEQ_NAME VARCHAR(100) NOT NULL, SEQ_VAL BIGINT NOT NULL, SEQ_STS BOOLEAN NOT NULL DEFAULT TRUE, CONSTRAINT COM_SEQ_PK PRIMARY KEY (SEQ_NAME, SEQ_VAL)
);
INSERT INTO com_seq SELECT 'REGISTRATION_SEQUENCE', i, true
FROM generate_series(1,10000) a(i);
For this example, I've pre-allocated 10,000 numbers for a sequence named REGISTRATION_SEQUENCE. You can allocate as many numbers as your application requires.
Each row represents a pre-allocated sequence number. The SEQ_STS column indicates whether that number is available for allocation: - TRUE: The number is available. - FALSE: The number has been allocated and commited.
Because all the numbers have already been created, the application doesn't need to generate new rows each time it requires a number. Instead, it can select an available number and mark it as allocated. Depending on the application, you may also need a process to reset or replenish the pool periodically - for example, at the beginning of each year.
Allocating a Number with a CTE In my previous article, I created a functioin that application programs could call to obtain a sequence number. This time, we'll use a CTE to allocate a number directly in SQL:
with w as ( update com_seq set seq_sts = false --sets its status to FALSE where seq_name = 'REGISTRATION_SEQUENCE' and seq_val = (select seq_val --fetch the first avaliable non locked row from com_seq where seq_name = 'REGISTRATION_SEQUENCE' and seq_sts is true order by seq_val fetch next 1 rows only for update skip locked) returning seq_val ) --insert into application_tab (sev_val, ...) --we can do something with the sequence number
select seq_val from w;
The key part of this query is FOR UPDATE SKIP LOCKED. This clause allows multiple transactions to allocate sequence numbers concurrently. When a transaction encounters a row locked by another transaction, it skips that row and looks for another available one.
Here's how the query works: 1. Find the smallest sequence number whose status is TRUE. 2. Lock that row, skipping any rows that are already locked by other transactions. 3. Update the selected row's status to FALSE. 4. Return the allocated sequence number through the CTE or do some operations with the allocated sequence number.
The UPDATE .. RETURNING clause lets us retrieve the allocated number in the same SQL statement. Let's see what happens when multiple sessions request sequence numbers simultaneously.
Testing the Allocation Logic First, start a transaction in Session 1 and allocate a sequence number.
-- Session 1
begin; with w as ( update com_seq set seq_sts = false --sets its status to FALSE where seq_name = 'REGISTRATION_SEQUENCE' and seq_val = (select seq_val --fetch the first avaliable non locked row from com_seq where seq_name = 'REGISTRATION_SEQUENCE' and seq_sts is true order by seq_val fetch next 1 rows only for update skip locked) returning seq_val ) select seq_val from w;
get_seqno| ---------+
1|
Let's inspect the first few rows from the same session:
select * from com_seq order by seq_val fetch next 10 rows only;
Although the transaction has not committed, Session 1 sees its own update: the status of sequence number 1 is now FALSE.
Now, withoug committing the transaction, let's request another number from the same session. -- Session 1 with w as ( update com_seq set seq_sts = false --sets its status to FALSE where seq_name = 'REGISTRATION_SEQUENCE' and seq_val = (select seq_val --fetch the first avaliable non locked row from com_seq where seq_name = 'REGISTRATION_SEQUENCE' and seq_sts is true order by seq_val fetch next 1 rows only for update skip locked) returning seq_val ) --insert into application_tab (sev_val, ....) --we do something with the sequence number select seq_val from w;
This time, the query returns:
get_seqno| ---------+ 2|
SELECT * FROM COM_SEQ order by seq_val fetch next 10 rows only;
Why are the first two rows still TRUE? Because Session 1 has not committed its transaction. Under PostgreSQL's default READ COMMITTED isolation level, Session 2 sees the previously committed versions of those rows. It can not see Session 1's uncommitted changes. However, the rows for sequence numbers 1 and 2 are locked by Session 1. This is where SKIP LOCKED becomes important.
Let's request a sequence number from Session 2.
begin; with w as ( update com_seq set seq_sts = false --sets its status to FALSE where seq_name = 'REGISTRATION_SEQUENCE' and seq_val = (select seq_val --fetch the first avaliable non locked row from com_seq where seq_name = 'REGISTRATION_SEQUENCE' and seq_sts is true order by seq_val fetch next 1 rows only for update skip locked) returning seq_val ) select seq_val from w;
The query returns:
get_seqno| ---------+ 3
commit; --Note that I have committed this transaction.
Session 2 skips rows 1 and 2 because they are locked by Session 1, even though their committed versions still show SEQ_STS = TRUE.
After committing the transaction, sequence number 3 has been committed as allocated, while Session 1's allocations remain uncommitted.
Now let's roll back Session 1's transaction:
-- Session 1 rollback;
The updates made by Session 1 are undone. Sequence numbers 1 and 2 become available again, while the committed update made by Session 2 remains in effect.
Let's inspect the table again on Session 1:
SELECT * FROM COM_SEQ order by seq_val fetch next 10 rows only;
Only sequence number 3 remains marked as allocated because Session 2 committed its transaction. If another session now requests a number, it can allocate sequence number 1, the smallest available number.
This approach allows multiple sessions to allocate sequence numbers simultaneously without allocating the same number twice. However, there is an important trade-off: concurrent allocation does not guarantee that numbers will be handed out in numerical order.
Conclusion If your application requires gap-free numbering, a pre-populated sequence table combined with a CTE and FOR UPDATE SKIP LOCKED can be a useful alternative to a conventional PostgreSQL sequence. The main advantages are that the numbers can be reused after a transaction rolls back and that sessions don't have to wait for every lower-numbered allocation to finish before proceeding. As always, the right solution depends on your business requirements. If you require strictly consecutive, committed business numbers with no gap under all circumstances, you may need additional coordination or serialization, which can reduce concurrency. For applications where using numbers from rolled-back transactoins is important and some out-of-order allocation is acceptable, this technique is worth considering.
Addendum 1 One important distinction is that this method can help maintain a gap-free series of committed allocations when allocation and the corresponding business operation are performed in the same transaction. It can not guarantee that every application-level record will remain gapless if the number allocation and business operation are committed separately.
Addendum 2 PostgreSQL's built-in SEQUENCE objects are desinged for concurrency and performance, not gapless numbering.
