What this usually means
Postgres sequences are designed for concurrency, not gapless ordering. Every NEXTVAL call advances the sequence regardless of whether the INSERT commits or rolls back. That's by design. Gaps also occur when the sequence cache is lost during a crash or when a standby is promoted. The only time gaps signal a real problem is when they're caused by unscheduled sequence resets, concurrent sequence exhaustion, or application logic that wrongly expects contiguous IDs.
The first ten minutes — establish facts before touching code.
- 1Check the current sequence value: `SELECT currval('your_table_id_seq');`
- 2Find the maximum ID in the table: `SELECT max(id) FROM your_table;`
- 3Compare the two: if currval is far ahead of max(id), you likely have rollback gaps.
- 4Check for sequence cache setting: `SELECT cache_size FROM pg_sequences WHERE sequencename = 'your_table_id_seq';`
- 5Inspect recent rollback rate: `SELECT xact_commit, xact_rollback FROM pg_stat_database WHERE datname = current_database();`
- 6If gaps appear after restart, check if the sequence was created with a cache > 1 (default 1).
- 7Look for concurrent sequence usage across multiple backends: `SELECT * FROM pg_stat_activity WHERE query ~ 'nextval';`
The specific files, logs, configs, and dashboards that usually own this bug.
- searchpg_sequences: check last_value, start_value, increment_by, cache_size, is_cycled
- searchpg_class: relname for the sequence (usually tablename_columnname_seq)
- searchpg_stat_database: xact_rollback counter to see rollback rate
- searchPostgreSQL logs: look for 'sequence' or 'nextval' in log_statement = 'all' logs
- searchApplication error logs: check for duplicate key or sequence exhaustion errors
- searchpg_stat_activity: check for long-running transactions that might hold sequence values
- searchpg_locks: look for relation locks on sequence objects
Practical causes, not theory. These are the things you will actually find.
- warningRolled back transactions: each NEXTVAL is consumed even if the INSERT fails
- warningSequence cache loss: default cache is 1, but if set higher, a crash loses cached values
- warningConcurrent inserts: multiple sessions each get a range of sequence values, causing gaps when some sessions don't use all
- warningSequence reset manually or via ALTER SEQUENCE RESTART
- warningTable truncation: TRUNCATE resets the sequence unless RESTART IDENTITY is specified
- warningStandby promotion: sequences on a promoted standby may have gaps due to WAL replay ordering
Concrete fix directions. Pick the one that matches your root cause.
- buildIf gaps are harmless (most cases), do nothing. Educate the team that serial IDs are not meant to be contiguous.
- buildIf application logic requires gapless IDs, use a different approach: generate IDs in application with a retry loop, or use a gapless sequence via a counter table with row-level locking.
- buildTo reset a sequence to max id: `SELECT setval('your_table_id_seq', (SELECT max(id) FROM your_table));`
- buildTo prevent huge gaps from cache loss, set cache to 1 (default) or adjust max_connections to limit concurrent nextval calls.
- buildIf gaps are due to aggressive rollback rate, investigate why transactions are rolling back and fix the root cause.
- buildMonitor sequence exhaustion: if the sequence is near its max (2^31-1 for serial), plan to alter to bigserial or use a cycle.
A fix you cannot prove is a guess. Close the loop.
- verifiedAfter resetting sequence, insert a new row and check that id is max(id)+1: `INSERT INTO your_table DEFAULT VALUES RETURNING id;`
- verifiedSimulate rollback: BEGIN; INSERT INTO your_table DEFAULT VALUES; ROLLBACK; then insert again; verify the gap of at least 1.
- verifiedCheck sequence vs table max after fix: `SELECT setval('your_table_id_seq', max(id)) FROM your_table;` and then currval matches.
- verifiedMonitor application logs for any 'duplicate key' or 'sequence' errors for 24 hours.
- verifiedUse `SELECT * FROM pg_sequences WHERE sequencename = 'your_table_id_seq';` to confirm last_value and cache_size.
- verifiedRun a load test with concurrent inserts and verify no unexpected gaps beyond rollback count.
Things that make this bug worse or harder to find.
- warningAssuming serial IDs are always consecutive: they are not and never will be.
- warningManually inserting with explicit IDs that conflict with the sequence: this can cause duplicate key errors.
- warningSetting cache to a large value without understanding the crash recovery implications: you could lose thousands of IDs.
- warningResetting the sequence to fill gaps: it can cause duplicate keys if there are existing rows with higher IDs.
- warningUsing serial for IDs that need to be gapless for business logic (e.g., invoice numbers): use a different mechanism.
- warningIgnoring sequence exhaustion: if your table grows fast, monitor sequence max value.
The Case of the Missing 1000 IDs After a Crash
Timeline
- 09:15PagerDuty alert: sequence 'orders_id_seq' is at 90% of max value.
- 09:18Checked pg_sequences: last_value = 2147483000, max id in orders = 2147482000. Gap of 1000.
- 09:22Checked logs: AWS RDS instance had a failover at 03:00 due to AZ outage.
- 09:25Noticed sequence cache_size = 1000 (set by previous DBA for performance).
- 09:30Confirmed: failover caused loss of cached sequence values. Sequence advanced 1000 IDs that were never used.
- 09:35Reset sequence to max(id) + 1: SELECT setval('orders_id_seq', (SELECT max(id) FROM orders));
- 09:40Changed cache_size to 1 to prevent future large gaps.
- 09:45Verified new insert gets correct next ID. Alert resolved.
At 9:15 AM, our monitoring system alerted that the orders sequence was approaching its maximum value. I immediately checked pg_sequences and saw last_value was 2147483000, but the maximum id in the orders table was only 2147482000. A gap of 1000 IDs. My first thought was rollback spam, but the gap was exactly 1000, which felt too round.
I checked the PostgreSQL logs and RDS events. At 3:00 AM, there was a failover to a standby instance. That's when it hit me: the sequence had a cache_size of 1000, set months ago for performance. When the primary crashed, the standby promoted but the cached values (the next 1000 IDs) were lost. The sequence on the new primary started at the last value from WAL, but those 1000 IDs were already consumed in memory and never written to the table.
I reset the sequence to max(id) + 1 and changed cache_size to 1. Then I verified with a test insert that the next ID was correct. I also added a monitoring check for large gaps ( > 10% of cache size) and scheduled a team meeting to discuss why cache was set so high. Lesson: never set sequence cache without understanding crash recovery, and always monitor for gaps that are multiples of cache size.
Root cause
Sequence cache_size set to 1000, causing loss of 1000 sequence values during RDS failover.
The fix
Reset sequence to max(id) and set cache_size to 1.
The lesson
Default cache is fine for most workloads; large caches are dangerous in crash scenarios. Always monitor sequence gaps and correlate with failover events.
A sequence is a named object that generates monotonically increasing numbers. When you call nextval, the sequence increments and returns the new value. This operation is atomic and non-transactional: it cannot be rolled back. That means even if the surrounding transaction aborts, the nextval call is permanent.
The sequence stores its current state in memory and on disk. The cache parameter controls how many values are pre-allocated in memory per session. Default is 1, meaning each nextval call writes to disk. A higher cache improves performance under high concurrency but risks losing unused cached values on a crash. The lost values are never reused, creating gaps.
Not all gaps are equal. A gap of 1 is often a single rollback. A gap of 1000 that matches cache size is a crash recovery gap. A gap of large random size might be manual intervention. To diagnose, first check the sequence definition: SELECT * FROM pg_sequences WHERE sequencename = 'your_seq'. Then compare last_value with max(id) from the table. If last_value is far ahead, check for recent restarts or failovers.
You can also query pg_stat_database for the ratio of xact_rollback to xact_commit. A high rollback rate indicates many transactions aborting, which will cause many small gaps. If you see a pattern of gaps exactly equal to cache size, suspect crash recovery.
Many developers try to 'fix' gaps by resetting the sequence to max(id) + 1. This is dangerous because if there are concurrent inserts, you can get duplicate key errors. The only safe time to reset is when the table is locked exclusively, which kills concurrency.
If you truly need gapless IDs (e.g., invoice numbers), don't use serial. Use a counter table with row-level locking: CREATE TABLE counter (id integer PRIMARY KEY); then update with RETURNING. Or generate IDs in the application using a distributed sequence service. But ask yourself: does the business really require gapless? Most don't.
Set up monitoring on sequence usage percentage (last_value / max_value). For serial (int4), max is 2147483647. For bigserial (int8), it's 9223372036854775807. Alert when usage exceeds 80%.
Also monitor for large gaps: compare currval with max(id) from the table. If the difference is greater than, say, 1000, investigate. Correlate with server restarts, failovers, and rollback spikes.
To prevent cache-related gaps, keep cache_size at 1 unless you have proven performance need and accept the risk. If you must use larger cache, ensure your standby promotion procedure includes sequence state validation.
If your sequence approaches max value, you'll get 'nextval: reached maximum value of sequence' errors. At that point, you cannot insert new rows until the sequence is altered. The fix: ALTER SEQUENCE your_seq RESTART WITH 1; but only if you have no existing rows with that value (or use cycle). Better: ALTER SEQUENCE your_seq MAXVALUE 9223372036854775807; to convert to bigint range. But note: this requires the column type to be bigint as well.
To prevent this, use BIGSERIAL from the start for any table that might exceed 2 billion rows. Many production tables hit this limit faster than expected. I've seen a social media app exhaust serial in 18 months.
Frequently asked questions
Why does a rolled-back INSERT still consume a sequence value?
Because nextval is not transactional. It's designed to be atomic and non-rollbackable to avoid locking and contention. If nextval were transactional, every INSERT would need to hold a lock on the sequence until commit, causing massive contention. The trade-off is acceptable gaps.
Can I safely reset a serial sequence to remove gaps?
Only if you can guarantee no concurrent inserts, typically with an exclusive lock on the table. The command is: LOCK TABLE your_table IN EXCLUSIVE MODE; SELECT setval('your_seq', (SELECT max(id) FROM your_table)); But this blocks all writes. For most applications, it's not worth the downtime.
What is the best practice for sequence cache size?
Default of 1 is fine for 99% of cases. Only increase cache if you have high concurrency and can tolerate losing cached values on crash. Document the trade-off. If you set cache to 100, you risk losing up to 100 IDs per session on crash.
How do I monitor sequence exhaustion?
Query pg_sequences for last_value and max_value. For serial (int4), max is 2147483647. Create a monitoring check: SELECT (last_value::float / (SELECT max_value FROM pg_sequences WHERE sequencename = 'your_seq')) > 0.8 AS near_exhaustion. Alert if true.
Does TRUNCATE reset the sequence?
TRUNCATE without RESTART IDENTITY resets the sequence. To preserve the sequence, use TRUNCATE your_table CONTINUE IDENTITY. Or reset after truncate with setval to desired starting value.