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.
The cleanup, when it's already too late
Deduplicating a PK-less table is its own small ordeal, and it teaches why the
key should have existed. The standard trick leans on ctid, the
physical row address:
DELETE FROM events a
USING events b
WHERE a.ctid < b.ctid
AND a.event_type = b.event_type
AND a.payload = b.payload
AND a.created_at = b.created_at;Every column has to participate in the comparison (which is exactly the
"REPLICA IDENTITY FULL" problem in another costume), the query is a full
self-join, and ctid isn't stable across VACUUM FULL, so this only
works while nothing else moves the rows. An id column would have
made it a one-line GROUP BY.
"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
(WHERE id > $last ORDER BY id LIMIT 100 doesn't skip or repeat
rows the way OFFSET does under concurrent writes), 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: ALTER TABLE ... ADD COLUMN
id BIGINT GENERATED ALWAYS AS IDENTITY rewrites the whole table under an
exclusive lock.
CREATE TABLE events (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);Natural vs technical keys
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. The email that was "obviously unique and stable" meets its first account merge, the national id meets its first typo correction, and now the correction is a multi-table migration. A technical key (identity/UUID) plus a UNIQUE on the natural key gives the same effect without the coupling: the uniqueness rule is enforced, the join key never changes. Whether that technical key should be a BIGINT or a UUID is its own trade-off; either beats not having one.