Database Best Practices: Primary Keys That Scale

A random UUID primary key costs about twice the index space of a bigint and spreads every insert across random leaf pages. That is fine at 200 writes a day and painful at 2,000 writes a second. The database best practices primary key rule is narrower than most advice admits: use bigint identity for internal, insert-hot tables, UUIDv7 for keys that leave the database, and avoid UUIDv4 primary keys on high-write tables. Below you get the thresholds and a test that reproduces the bloat yourself.

What are database best practices primary key selection rules?

The best primary key is narrow, monotonic, and meaningless to clients. Narrowness keeps secondary indexes small because Postgres copies the primary key into each one. Monotonicity keeps inserts at the right edge of the B-tree. Meaninglessness stops a key from being reused or leaked. Sacrificing any of the three is a deliberate trade, not a default.

In PostgreSQL 16 and newer, an identity column satisfies all three without extensions:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  total_cents bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

Verify it with \d orders in psql, or query information_schema.columns where is_identity = 'YES'. Both work across PostgreSQL 12 and newer, so the check survives upgrades.

Why the key choice is really an index choice

A primary key in Postgres is a unique B-tree named <table>_pkey. Every foreign key and every secondary index stores the key value. A bigint is 8 bytes; a uuid is 16 bytes. On a table with four secondary indexes and 50 million rows that difference compounds into gigabytes of pages and earlier disk reads as shared buffers fill.

Integer identity vs UUIDv4 vs UUIDv7: comparison

Integer identity wins on storage and insert locality. UUIDv7 wins on distribution and URL safety while keeping insert locality. UUIDv4 wins only on unguessability, with no locality. Rank them on the properties that affect production systems, not on style.

PostgreSQL 18 added a built-in uuidv7() function, which removes the application dependency if you run 18 or later. Check with SELECT version(); and read the PostgreSQL UUID functions documentation for the exact signature in your release.

Failure mode: how UUIDv4 primary keys fragment the index

Random UUIDv4 inserts write to random leaf pages. Each write dirties a page that is probably not in shared buffers, splits pages that are full, and leaves half-empty pages behind. Symptoms: index size growing faster than row count, higher WAL volume per insert, and a cache hit ratio that falls as the working set grows.

Reproduce the bloat in five steps

1. Start PostgreSQL 16 with docker run --name pgtest -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:16 and connect via psql -h localhost -U postgres.
2. Create two identical tables, one with id uuid DEFAULT gen_random_uuid() PRIMARY KEY and one with id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY.
3. Insert one million rows into each with INSERT INTO t SELECT ... FROM generate_series(1,1000000).
4. Compare SELECT pg_size_pretty(pg_relation_size('t_pkey')) for both, then read SELECT relname, n_live_tup FROM pg_stat_user_tables.
5. Run VACUUM on both and compare sizes again. The UUIDv4 index will not shrink to the same size because the empty pages are structurally required.

The exact ratio depends on row width and fill factor, so measure on your hardware. What you should see is the UUIDv4 index holding materially more pages for the same row count, with the gap widening as the table grows.

When to flip: thresholds, not taste

Flip from bigint to UUIDv7 when the key is exposed to clients or crosses service boundaries and you have more than one writer generating IDs. Keep bigint when the table is internal, join-heavy, and written by a single database. The reverse flip, from UUIDv4 to UUIDv7 or bigint, pays off when sustained writes exceed a few hundred per second or when the primary key index is the largest relation in pg_stat_user_tables.

Those thresholds are workload-shaped. Confirm them for your system against three inputs: sustained write rate, number of secondary indexes, and RAM available for shared buffers. On a read-mostly table with 10,000 rows, UUIDv4 costs nothing measurable, so do not spend a migration on it.

Naming conventions still apply

Whichever type you pick, name the surrogate column id, foreign keys <table>_id, and treat uuid as a type, not a suffix. A column called order_uuid that holds a bigint is the kind of drift that slows schema review. Naming primary and unique constraints explicitly keeps migrations predictable; the PostgreSQL constraints documentation covers the syntax.

Migrations and ACID transactions around key changes

Changing a primary key type rewrites the table and every dependent index in PostgreSQL 16, so plan it as a staged migration. The safe sequence: add the new column, backfill in batches with short transactions, build the new unique index concurrently, switch reads, then drop the old column in a later deploy.

-- PostgreSQL 16: add and backfill without locking the table for the whole rewrite
ALTER TABLE orders ADD COLUMN id_v7 uuid;
UPDATE orders SET id_v7 = gen_random_uuid()
 WHERE id_v7 IS NULL AND id <= 100000;
CREATE UNIQUE INDEX CONCURRENTLY orders_id_v7_idx ON orders (id_v7);

Each batch is its own transaction, which keeps locks short and lets autovacuum keep up. Run the batch update from a queued job rather than inside Schema::table so web requests are not blocked. If you are tuning the surrounding transactions, the PostgreSQL row locking guide covers the lock modes that matter during the switch.

Recommendation and the common advice it rejects

Use bigint identity for internal tables with high insert volume; use UUIDv7 when IDs must be generated by clients, exposed in URLs, or exchanged across services; avoid UUIDv4 primary keys on hot tables because random insertion order fragments the B-tree and inflates every dependent index. The advice this rejects is the blanket recommendation to make every primary key a UUID so you can "scale later". That advice optimizes for a future you may never reach and charges the index today.

The recommendation flips if multiple writers cannot coordinate on IDs, or if leaking row counts is itself a security problem for an endpoint. In those cases UUIDv7 gives you distribution without giving up insert locality. The same B-tree mechanics explain join cost, which the post on SQL indexing debugging walks through from the planner side.

Key type is a storage decision before it is a style decision. Measure index size and insert rate, then choose.

Frequently asked questions

Can I use UUIDv7 as a primary key in PostgreSQL 16?

Not natively. The built-in uuidv7() function arrives in PostgreSQL 18. On 16 and 17, generate the value in the application with ramsey/uuid v7 or an installed database function, then confirm ordering with a quick sort test.

Does a UUID primary key break foreign keys or joins?

No, both work. The cost is size: each foreign key column and each index carrying the key grows from 8 to 16 bytes. On join-heavy schemas with many secondary indexes, that is why bigint remains the default.

Should I expose my integer primary key in API URLs?

Only if enumeration is acceptable. Sequential integers let anyone walk your list and infer volume. For public endpoints, route through a UUIDv7 column or an opaque slug while keeping bigint inside the database for joins and fast lookups.