Your Postgres ID columns will run out of numbers. Here's a 10-second check (and the trap that hides it)

There's a Postgres outage that gives you no warning at all. One day every INSERT into your busiest table fails:

ERROR:  nextval: reached maximum value of sequence "orders_id_seq" (2147483647)

or, depending on how the table was created:

ERROR:  integer out of range

The cause is mundane. A column declared serial or integer holds values up to 2,147,483,647. That sounds infinite until you do the arithmetic: 2 million inserts a day uses it up in under three years. Tables that churn through IDs without keeping the rows (sessions, events, job queues) get there first, because a sequence never gives numbers back, even after you delete the rows.

The fix for a big table is a planned migration that can take weeks to roll out safely. Finding out the day inserts start failing is the worst possible time.

The check

This query finds every auto-incrementing ID column and tells you how much of its range is used:

WITH col_seq AS (
  SELECT
    tn.nspname || '.' || t.relname || '.' || a.attname AS id_column,
    format_type(a.atttypid, a.atttypmod)               AS column_type,
    ps.last_value,
    CASE a.atttypid
      WHEN 'int2'::regtype THEN 32767
      WHEN 'int4'::regtype THEN 2147483647
      WHEN 'int8'::regtype THEN 9223372036854775807
    END::numeric                                        AS column_max,
    ps.max_value::numeric                               AS sequence_max,
    (has_sequence_privilege(s.oid, 'SELECT')
       OR has_sequence_privilege(s.oid, 'USAGE'))       AS can_read
  FROM pg_depend d
  JOIN pg_class s      ON s.oid = d.objid AND s.relkind = 'S'
  JOIN pg_namespace sn ON sn.oid = s.relnamespace
  JOIN pg_class t      ON t.oid = d.refobjid
  JOIN pg_namespace tn ON tn.oid = t.relnamespace
  JOIN pg_attribute a  ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
  JOIN pg_sequences ps ON ps.schemaname = sn.nspname AND ps.sequencename = s.relname
  WHERE d.classid = 'pg_class'::regclass
    AND d.refclassid = 'pg_class'::regclass
    AND d.deptype IN ('a', 'i')          -- serial-owned or identity columns
)
SELECT id_column, column_type, last_value,
       CASE WHEN can_read
            THEN round(100.0 * coalesce(last_value, 0) / least(column_max, sequence_max), 2)
       END AS pct_used,
       CASE WHEN NOT can_read THEN 'UNKNOWN: no permission to read this sequence'
            WHEN 100.0 * coalesce(last_value, 0) / least(column_max, sequence_max) >= 75 THEN 'ACT'
            WHEN 100.0 * coalesce(last_value, 0) / least(column_max, sequence_max) >= 50 THEN 'WATCH'
            ELSE 'OK' END AS verdict
FROM col_seq
WHERE column_max IS NOT NULL
ORDER BY can_read, coalesce(last_value, 0) / least(column_max, sequence_max) DESC;

On a test database with two planted problems, it returns:

       id_column        | column_type | last_value | pct_used | verdict
------------------------+-------------+------------+----------+---------
 public.orders.id       | integer     | 2000000000 |    93.13 | ACT
 public.legacy_users.id | integer     | 1700000000 |    79.16 | ACT
 public.audit_log.id    | integer     | 1300000000 |    60.54 | WATCH

Notice two details in that query. Both exist because the obvious version gets it wrong.

Trap 1: checking the sequence instead of the column

Look at legacy_users above. Its sequence is a 64-bit bigint sequence, which is common in databases that started life on older Postgres versions. Its maximum is 9.2 quintillion. A check that only looks at the sequence reports it as 0.00000002% used.

But the column is a 32-bit integer. It overflows at 2.1 billion no matter how big the sequence is. That's why the query compares against least(column_max, sequence_max). In this test, a sequence-only check misses a table that's 79% of the way to an outage.

Trap 2: a blank that looks like a zero

This one is easy to miss, because the obvious version works perfectly when you test it as a superuser. Run it the way most people would on RDS or Cloud SQL, as a restricted monitoring user with the built-in pg_monitor role, and it reports orders.id, the column at 93%, as 0% used. OK.

Postgres doesn't raise an error when you can't read a sequence's value. pg_sequences.last_value quietly comes back NULL, and coalesce(last_value, 0) turned "I'm not allowed to see this" into "this is empty". A health check that gives a false all-clear on the one check most likely to cause an outage is worse than no check at all.

The fix is the has_sequence_privilege() test. When the value is hidden, the query says UNKNOWN instead of guessing, and sorts those rows to the top. To make them readable:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO your_monitoring_user;

If you're already at ACT

For small tables, ALTER TABLE orders ALTER COLUMN id TYPE bigint; works, but it rewrites the table and locks it for the duration. Change every foreign key that references it, too. For big tables, migrate online: add a bigint column, sync it with a trigger, backfill in batches, build a unique index CONCURRENTLY, then swap.

And if inserts are failing right now, this buys you another ~2.1 billion IDs while you do the real fix:

ALTER SEQUENCE orders_id_seq MINVALUE -2147483648 RESTART WITH -2147483648;

In testing, inserts resumed immediately with negative IDs. It's a tourniquet, though: anything that assumes IDs are positive, or sorts by ID to find the newest rows, will misbehave.

Run it monthly

Overflow is the most predictable outage there is. Put this query in a monthly job and you'll see it coming a year ahead.

This check is one of three I've published free, with the root-blocker finder and a one-screen health overview, at github.com/CosmicPotter/postgres-health-checks. Plain SQL, MIT licensed: copy them into your own monitoring.

---

This article was written with the help of AI. Every query in it was tested on PostgreSQL 14 to 18, and the outputs shown come from those test runs.