---
title: "How much room is left in a PostgreSQL integer ID | Proofline"
description: "A serial primary key stops at 2,147,483,647, and inserts fail from the next one."
url: https://proofline.sh/learn/postgres-integer-id-headroom/
---

[Learning center](/learn/)

# How much room is left in a PostgreSQL integer ID

A serial primary key stops at 2,147,483,647, and inserts fail from the next one. Read the sequence's headroom now, while the change is still a planned migration. When you widen the key, widen every column that stores it too: a foreign key column left as integer fails on the first new ID above the limit.

Capacity overruns The Proofline team November 20, 2026 3 min read

## Run a sequence out of values

We ran this on PostgreSQL 16.15. `setval` moves the sequence close to its end so the lab does not need two billion rows.

```
CREATE TABLE audit_log (id serial PRIMARY KEY, action text NOT NULL);
SELECT setval('audit_log_id_seq', 2147483645);

INSERT INTO audit_log (action) VALUES ('a'), ('b');
-- INSERT 0 2
INSERT INTO audit_log (action) VALUES ('c');
```

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

Every insert into the table now fails. An audit or event table is often the first to reach the limit, because it grows fastest and nobody reads its IDs.

## Read the headroom

`pg_sequences` reports each sequence's type, last value and maximum:

```
SELECT sequencename, data_type, last_value, max_value,
       round(100.0 * last_value / max_value, 2) AS pct_used
FROM pg_sequences
WHERE last_value IS NOT NULL
ORDER BY pct_used DESC;
```

| sequencename | data\_type | last\_value | max\_value | pct\_used |
| --- | --- | --- | --- | --- |
| audit\_log\_id\_seq | integer | 2147483647 | 2147483647 | 100.00 |

The percentage is not a row count. Failed inserts, rolled-back transactions and cached sequence values all consume numbers that never reach the table. To turn the headroom into a date, sample `last_value` a week apart and divide the remaining values by the weekly increase.

## Widening the sequence alone is not enough

`ALTER SEQUENCE ... AS bigint` raises the sequence's limit, but the column still has the old type:

```
ALTER SEQUENCE audit_log_id_seq AS bigint MAXVALUE 9223372036854775807;
INSERT INTO audit_log (action) VALUES ('c');
-- ERROR:  integer out of range
```

This query lists each column fed by a sequence, with both types, so a mismatch like this one stands out:

```
SELECT c.oid::regclass AS table_name, a.attname AS column_name,
       format_type(a.atttypid, a.atttypmod) AS column_type,
       format_type(s.seqtypid, NULL) AS sequence_type
FROM pg_depend d
JOIN pg_sequence s ON s.seqrelid = d.objid
JOIN pg_class c ON c.oid = d.refobjid
JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE d.classid = 'pg_class'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.deptype IN ('a', 'i');
```

| table\_name | column\_name | column\_type | sequence\_type |
| --- | --- | --- | --- |
| audit\_log | id | integer | bigint |

## Widen the columns that store the key

A note table references the audit log. Widening the primary key now lets the parent table insert again, and the child fails at the same boundary:

```
CREATE TABLE audit_notes (
    id serial PRIMARY KEY,
    audit_id integer NOT NULL REFERENCES audit_log (id)
);
ALTER TABLE audit_log ALTER COLUMN id TYPE bigint;

INSERT INTO audit_log (action) VALUES ('c') RETURNING id;
-- 2147483649

INSERT INTO audit_notes (audit_id) VALUES (2147483648);
-- ERROR:  integer out of range
```

PostgreSQL accepted a foreign key from `integer` to `bigint`, so nothing warned about the narrower child column. The returned ID also skipped 2147483648, which the earlier failed insert had consumed. List every column that references the key with `\d+ audit_log` or `pg_constraint`, and include columns that hold the ID without a constraint, such as a column in another service's database or an analytics export.

## Plan the migration

`ALTER COLUMN ... TYPE bigint` rewrites the table and its indexes while holding an `ACCESS EXCLUSIVE` lock, as [which column type changes rewrite the table](/learn/postgres-alter-column-type-rewrite/) shows. For a small table that may be acceptable. For a large one, add a `bigint` column, fill it in batches, keep it current with a trigger, and swap it in a short transaction. Rehearse that swap, including the primary key and every foreign key that points at it.

Check the application too. A 32-bit integer field in Java or C#, or a schema that validates the ID as a 32-bit number, fails the same way as the database column.

## What to rehearse

Run the headroom and type queries against production now and record the date you expect each sequence to run out. On a staging copy, set the sequence just below 2,147,483,647, apply the migration, and insert through every path that writes the key or a reference to it. The rehearsal passes when each insert succeeds with an ID above the old limit.

Find the other changes that need a rehearsal in [which PostgreSQL migration needs a rehearsal](/learn/postgres-migration-rehearsal/).

## One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. [Eligibility and fair-use terms](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Which PostgreSQL column type changes rewrite the table](/learn/postgres-alter-column-type-rewrite/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
    
-   ### [Which PostgreSQL migration needs a rehearsal before the next deploy](/learn/postgres-migration-rehearsal/)
    
    Breaking migrations The Proofline team November 20, 2026 4 min read
