This article explores the causes of sequence gaps, the surprising possibility of sequences jumping backwards, and an example of creating a gapless sequence.
When a transaction encounters an issue and is rolled back, all changes made by the transaction are reverted to their previous state. However, sequence values in PostgreSQL do not follow this rule. If a transaction involving sequences is rolled back, the sequence values do not revert to their previous state, causing gaps.
Consider the following example demonstrating how a gap forms in a sequence:
CREATE TABLE test (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
value integer CHECK (value > 0)
);
CREATE TABLE
SELECT pg_get_serial_sequence('test', 'id');
pg_get_serial_sequence
------------------------
public.test_id_seq
-- Statement 1
INSERT INTO test (value) VALUES (2);
-- INSERT 0 1
-- Statement 2
INSERT INTO test (value) VALUES (-99);
-- ERROR: new row for relation "test" violates check constraint "test_value_check"
-- DETAIL: Failing row contains (2, -99).
-- Statement 3
INSERT INTO test (value) VALUES (105);
-- INSERT 0 1
SELECT * FROM test;
id | value
----+-------
1 | 2
3 | 105
(2 rows)
In the example above, the second statement was rolled back due to a constraint violation. However, the sequence value of 2 was not rolled back, resulting in a gap.
Using nextval
to obtain the next value from a sequence is efficient, but in a busy database with many concurrent transactions, the sequence can become a bottleneck. To mitigate this, PostgreSQL allows sequences to be set up with a CACHE
option, storing multiple values at once.
When nextval
is called in a session with caching enabled, several sequence values are fetched at once. Instead of accessing the sequence every time, the session uses the cached values. However, if the session ends before all cached values are used, the unused values are lost, creating gaps.
CREATE SEQUENCE test_seq CACHE 20;
CREATE SEQUENCE
SELECT nextval('test_seq');
nextval
---------
1
(1 row)
SELECT nextval('test_seq');
nextval
---------
2
(1 row)
-- Exit the session
\q
-- Start a new session
psql edb
psql (15.6.0)
Type "help" for help.
SELECT nextval('test_seq');
nextval
---------
21
(1 row)
In this example, the sequence was created with a cache of 20 values. When the session was restarted, the next sequence value jumped to 21, indicating that the cached values (3 to 20) were lost when the session ended.
For more information, refer to the PostgreSQL documentation on sequence functions.