When does an integer primary key run out?

32-bit integer primary key headed for exhaustion

Note: this works and blocks nothing, but a performance regression is likely.

What happens

An int primary key tops out at 2,147,483,647. Tables that get there discover it as an outage, and the fix, retyping the primary key and every referencing column, is one of the worst migrations there is. bigint costs 4 more bytes now and removes the cliff.

Why it is dangerous on a populated table

Nothing blocks, but the cost scales with the table and shows up as a slower query plan or a longer maintenance window rather than an outage.

Fires on

CREATE TABLE orders (id serial PRIMARY KEY);

The safe pattern

Make surrogate keys bigint from day one: 4 extra bytes per row against never having to retype a primary key and every foreign key that references it on a live system.

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (1)

int4 pk

CREATE TABLE clicks (id integer PRIMARY KEY, url text);

Stays silent (1)

bigint pk

CREATE TABLE clicks (id bigint PRIMARY KEY, url text);

How to check locally

Catch this before it ships

This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 104 rules

← BV043 BV045 →