When should a primary key be bigint instead of integer in PostgreSQL?
integer tops out at about 2.1 billion. If a table could ever reach that many rows over its life, including rows deleted along the way, use bigint from the start, because changing the type later rewrites the table and every foreign key.
Explain it simply
An integer is a car odometer with a fixed number of digits. When it reaches its maximum, it does not roll over quietly; PostgreSQL raises an error and inserts fail. The limit for integer is 2,147,483,647, and for bigint it is about 9.2 quintillion.
2.1 billion sounds distant, but sequences never reuse numbers. A table that handles 10 million inserts and rollbacks per day burns through that range in about 215 days of activity.
Example
SELECT 2147483647::integer + 1;
-- ERROR: integer out of range
CREATE TABLE upi_events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload text
);Fixing it later means ALTER TABLE ... ALTER COLUMN id TYPE bigint, which rewrites the table under a heavy lock, and you must also change every column that references it.
What to say in the interview
"For any table that can grow large, like events, orders or logs, I pick bigint for the key up front. It costs 4 more bytes per row, which is small next to the pain of migrating a live table once integer overflows. For tiny lookup tables, integer or smallint is fine."
Common mistakes
- Forgetting that failed inserts and deleted rows still consume sequence numbers.
- Making the key bigint but leaving the foreign key column as integer.