Why are there gaps in my Postgres id sequence? #22299
Locked
monicakh
announced in
Troubleshooting
Replies: 0 comments
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
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
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:
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.
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.
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.
Beta Was this translation helpful? Give feedback.
All reactions