Why are there gaps in my Postgres id sequence?

Most database tables have a primary key that is a made-up number, and that number is usually made by a sequence.

You might be surprised to know that gaps in sequence IDs are a normal aspect of working with relational databases in general. It's a common scenario that can seem puzzling at first, but there are several reasons why these gaps occur, and sometimes, this is typically expected and does not indicate a problem with your database.

It's also important to understand the distinction that sequences guarantee uniqueness, but not consecutiveness, and this should not imply any issues relating to data integrity with your database.

How to check the name of your sequence#

If you don't know the name of your sequence, it's often formed based on a standard naming convention: table_name_id_seq, where table_name is the name of your table and id is the name of your serial column.

Common reasons for gaps in sequences#

1. Rollbacks One of the most common reasons for a gap is the rollback of a transaction. If you initiate a transaction that includes an insert operation, the sequence responsible for generating the ID for the new row increments. If, for any reason, the transaction doesn't complete successfully—perhaps due to a constraint violation or a deliberate decision to rollback—the insert operation is undone, but the sequence value used is not returned or reused. The documentation explains that as well:

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Such cases will leave unused “holes” in the sequence of assigned values.

2. Deletions Removing rows from your table also creates gaps in the sequence of primary key values. Although deletions do not affect the sequence directly, they contribute to the non-sequential appearance of IDs in your table.

3. Manual Sequence Adjustments Another source of gaps can be manual interventions, such as incrementing a sequence number directly or setting it to a new value.

4. Upserts When an upsert is executed, it can still increase the sequence even if it is set to do nothing on conflicts

Checking for gaps#

To check for gaps in the sequence of IDs in a PostgreSQL table, you can use a SQL query that compares the sequence of IDs to a generated series of numbers that spans the same range:

This query should help you pinpoint where gaps exist.

Encountering errors and adjusting sequences#

In operations involving sequences, you might encounter an error message such as:

Failed to run sql query: duplicate key value violates unique constraint "{table}_pkey"

This error suggests a discrepancy between the sequence-generated IDs and the actual IDs in the table.

To address this, you can check the current value of your sequence or the highest ID in your table with:

And reset the sequence value to match the highest ID plus one: SELECT setval('{table}_{column}_seq', (SELECT max(id) FROM <table_name>) + 1);

Or, alternatively, adjust the sequence to a specific new value: ALTER SEQUENCE '{table}_{column}_seq' RESTART WITH new_value;

Implementing a gapless ID sequence#

If your application requires contiguous IDs and you decide to build a gapless sequence. Think twice about this, as it will serialize all transactions that use that “sequence” which will then deteriorate your data modification performance considerably. However, if your application truly requires gapless IDs, then you can use a Trigger: After a successful insert, use a database trigger to assign an ID based on a custom logic that finds the next available gapless ID.