What is this issue?
Sequences generate unique numbers, commonly used for primary keys.
Each sequence has a maximum value based on its data type:
- smallint: 32,767
- integer: 2,147,483,647 (~2 billion)
- bigint: 9,223,372,036,854,775,807 (~9 quintillion)
When a sequence reaches its maximum (and isn't set to cycle), it will
raise an error and no new values can be generated.
Severity levels:
- Critical: < 1% remaining
- Warning: 1-5% remaining
- Notice: 5-10% remaining
Why it matters
Application Failure
INSERTs fail when sequence is exhausted
No Automatic Recovery
Requires manual intervention to fix
Complex Migration
Changing sequence type may require table rewrite
Sudden Impact
Often discovered during peak traffic
How PG Pilot detects it
```sql SELECT sequencename, last_value, max_value, max_value - last_value AS remaining, round(100.0 * last_value / max_value, 2) AS percent_used FROM pg_sequences WHERE last_value::float / max_value > 0.9 ORDER BY percent_used DESC; ```
How to fix it
Assess current usage
Check sequence details:
SELECT * FROM pg_sequences
WHERE sequencename = 'your_sequence';Note the data_type, last_value, and max_value.
Convert to bigint if needed
Change the sequence to bigint:
ALTER SEQUENCE your_sequence AS bigint;This instantly expands the maximum value.
Update the related column
If the column using this sequence is integer:
ALTER TABLE your_table
ALTER COLUMN id TYPE bigint;This may require a table rewrite for large tables.
Check for gaps
Sometimes sequences skip ahead due to rollbacks. You may have
more room than the last_value suggests.
Reset if there's a large gap:
SELECT setval('your_sequence', (SELECT max(id) FROM your_table));Prevention
- Use bigint/bigserial for new primary keys
- Monitor sequence usage with PG Pilot
- Plan migrations before reaching 50% capacity
- Consider UUIDs for distributed systems