Primary Key Design

UUID vs serial.

Overview

Primary key choice is one of the few database decisions that is hard to undo and easy to get wrong. BIGSERIAL is sequential and B-tree-friendly but coordinates through the database. UUIDv4 is globally unique but its randomness destroys B-tree locality on inserts and bloats indexes. UUIDv7 and ULID are time-sortable, keeping the locality benefit while still allowing client-side generation. Composite keys (tenant_id plus sequential) win for multi-tenant systems where isolation matters more than global ordering.

• BIGSERIAL. Sequential 64-bit integer; B-tree-friendly inserts; coordinates through the database; the safe OLTP default.
• UUIDv4. 128-bit random; client-generated; globally unique; destroys B-tree insert locality, costs index size and write performance.
• UUIDv7 / ULID. Time-prefixed 128-bit; client-generated; preserves B-tree locality; the right answer for most distributed systems built today.
• Composite keys. tenant_id plus sequential; matches multi-tenant systems where the tenant boundary is the natural shard.

The approach

The practical approach is BIGSERIAL by default for single-database OLTP, UUIDv7 when clients need to generate IDs without coordination, composite keys for multi-tenant isolation, and an explicit per-table PK rationale committed to the schema documentation. The cost of a wrong PK is months of migration work; the cost of a deliberate choice is 30 minutes of thought.

• BIGSERIAL default. Single-database OLTP almost always wants this; coordination overhead is negligible at the scale most teams operate.
• UUIDv7 for distributed. When clients generate IDs (mobile offline, multi-region writes), UUIDv7 keeps the locality benefit BIGSERIAL would have given.
• Composite for multi-tenant. tenant_id plus serial; the tenant becomes the shard key; cross-tenant queries become explicit.
• Documented choice. Per-table PK rationale committed to schema docs; the next engineer inherits the reasoning, not just the column.

Why this compounds

PK design compounds across the lifetime of the table. The right choice on day one prevents the painful migration on year three; the wrong choice multiplies as foreign keys and indexes accumulate. Teams that develop a vocabulary for PK choice early stop relitigating it on every new table.

• Query performance. Right PK preserves B-tree locality; index size stays bounded; insert performance does not degrade as the table grows.
• Operational fit. Right PK for the use case; client-generated IDs work where coordination is impossible; sequential IDs work where coordination is cheap.
• Migration cost avoided. Right PK avoids the year-three rewrite; the cost of changing a PK on a 100M-row table with foreign keys is enormous.
• Institutional knowledge. Each table teaches PK tradeoffs; the team builds a shared vocabulary that makes new schema decisions faster.

PK design is a database discipline that pays off across years. Nova AI Ops integrates with database telemetry, surfaces index patterns, and supports the team’s schema design discipline.