Tables without a primary key: what actually goes wrong
A table without a primary key works. It takes INSERTs, answers SELECTs, passes the test. The problems arrive later, always in a group:
- You can't point at a specific row. Without a PK, the
UPDATE or DELETE of "that duplicate row" becomes a hack with
ctidor a fragile subquery. - Silent duplicates. The job that ran twice inserts everything twice, and nothing complains. You find out from the wrong report, weeks later.
- Logical replication and CDC need identity. Postgres asks
for
REPLICA IDENTITYto replicate UPDATE/DELETE; without a PK it either doesn't work or degrades to FULL (comparing the whole row). - The ORM won't map it. JPA/Hibernate require
@Id. A table with no PK stays out of the model or gets an improvised synthetic id in the code that doesn't exist in the database.
"But it's a log/staging table"
The legitimate case exists: bulk-load staging, an append-only table nobody
updates. Even there, an id BIGINT GENERATED ALWAYS AS IDENTITY costs
8 bytes per row and gives you back addressability, stable keyset pagination and
the option to deduplicate. The cost of adding it later, with the table huge and
production live, is much higher than being born with it.
CREATE TABLE events (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);A natural PK (national id, email, external code) deserves suspicion: a value that comes from outside changes, and changing a PK drags FKs, indexes and caches along. A technical key (identity/UUID) plus a UNIQUE on the natural key gives the same effect without the coupling.