PostgreSQL gaps in sequences

Rafik Mulla
Rafik Mulla
  • Updated

This article explores the causes of sequence gaps, the surprising possibility of sequences jumping backwards, and an example of creating a gapless sequence.

1. Gaps in Sequences Caused by Rollback

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.

Example: Forming a Gap in a Sequence

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.

2. Gaps in Sequences Due to Caching

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.

Example: Gaps Due to Caching

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.

Was this article helpful?

0 out of 0 found this helpful