Sequence Near Exhaustion

Overview

Postgres SERIAL and BIGSERIAL columns use sequences that increment monotonically. An INTEGER (int4) sequence maxes out at 2,147,483,647 (2.1 billion). A BIGINT (int8) sequence maxes out at 9.2 x 10^18. When a sequence is exhausted, any INSERT that triggers a nextval() fails with "integer out of range"—a hard outage.

Many applications start with SERIAL (int4), assuming they'll never exceed 2 billion rows, then find themselves there faster than expected due to high-volume writes, soft-deletes, or ephemeral records. insightral fires at 75% utilisation, giving lead time for the migration. The migration from int4 to int8 requires a table rewrite, which should be planned as a maintenance event.

Detection SQL

Run this query with a read-only role. No writes, no locks.

-- Sequences where current value is > 75% of the max value
SELECT
  s.schemaname,
  s.sequencename,
  s.data_type,
  s.last_value,
  s.max_value,
  round(
    s.last_value::numeric / s.max_value::numeric * 100,
    2
  )                           AS used_pct,
  s.max_value - s.last_value  AS remaining
FROM pg_sequences s
WHERE s.last_value IS NOT NULL
  AND s.last_value::numeric / s.max_value::numeric > 0.75
ORDER BY used_pct DESC;

Fix SQL

Replace placeholders in <angle brackets> with values from the detection output before running.

-- For a SERIAL (int4) column: migrate to bigint (requires table rewrite)
-- Step 1: add the new bigint column
ALTER TABLE <table_name> ADD COLUMN id_new bigint;

-- Step 2: backfill (can be batched)
UPDATE <table_name> SET id_new = id WHERE id_new IS NULL;

-- Step 3: swap and clean up (in a transaction)
BEGIN;
  ALTER TABLE <table_name> DROP CONSTRAINT <pk_name>;
  ALTER TABLE <table_name> RENAME COLUMN id TO id_old;
  ALTER TABLE <table_name> RENAME COLUMN id_new TO id;
  ALTER TABLE <table_name> ADD PRIMARY KEY (id);
  ALTER TABLE <table_name> DROP COLUMN id_old;
COMMIT;

-- Simpler option if the column type is already bigint: just reset the sequence start
SELECT setval('<sequence_name>', <new_start_value>);

Worked example

Common false positives

• Sequences used for sharding key generation that wrap intentionally — these use CYCLE and are not subject to exhaustion.
• BIGSERIAL sequences showing high percentage — at 9.2 x 10^18 max, even 75% utilisation is astronomically far from practical exhaustion; this rule primarily targets int4 sequences.