Composite Keys vs UUIDs: When Natural Keys Beat Surrogate UUIDs

Most schema design guides frame the primary key decision as a binary choice: auto-incrementing integer or UUID. That framing skips an option that, in the right schema, outperforms both — a composite key built from natural (or business) columns, enforced without any surrogate identifier at all.

This post is a deep dive into composite and natural keys as an alternative to surrogate UUID keys: how they work mechanically at the storage engine level, when they're the objectively better choice, and when they aren't.

Definitions, Precisely

Before comparing, the terms need to be pinned down, because "natural key" and "composite key" get used loosely.

• Surrogate key: A system-generated identifier with no business meaning — IDENTITY, SERIAL, or a UUID. Its only job is uniqueness.
• Natural key: One or more columns that already exist in the domain and are unique by business rule — an ISO country code, a SKU, a (tenant_id, email) pair.
• Composite key: A primary key made of more than one column. It can be composed of natural columns, surrogate columns, or a mix (e.g., (tenant_id, order_id) where order_id is a per-tenant sequence).

The interesting comparison isn't "natural vs surrogate" in the abstract — it's what the key is used for downstream: clustering, indexing, joins, foreign key fan-out, and partitioning. That's where UUIDs and composite natural keys diverge sharply.

The Mechanical Case: How Each Key Type Behaves on Disk

Surrogate UUID (v4) as Primary Key

A UUID (v4) primary key is 128 bits of effectively random data. On any engine that clusters the table by primary key — PostgreSQL doesn't by default, but MySQL/InnoDB does — a random UUID means every insert lands at a random point in the B-tree, not at the tail.

Sequential INT key insert pattern:  [1][2][3][4][5] -> always appended at the right edge
Random UUID key insert pattern:     [a3f...][09c...][f21...] -> scattered across every leaf page

The consequence is well documented: page splits, poor buffer cache locality, and larger indexes because 16-byte keys (or worse, 36-byte text-encoded UUIDs) are wider than a 4- or 8-byte integer, and every secondary index carries a copy of the primary key.

Composite Natural Key

A composite natural key is typically built from columns the application already writes on every query — most commonly a tenant or partition column followed by a locally-unique identifier:

PRIMARY KEY (tenant_id, order_id)

Mechanically, this has two effects that matter:

1. Clustering is meaningful. Rows for the same tenant are physically adjacent. A query like WHERE tenant_id = 42 AND order_id BETWEEN 1000 AND 2000 becomes a contiguous range scan instead of a random-access lookup pattern.
2. The key doubles as a partition/shard key. In distributed or partitioned setups (Postgres declarative partitioning, Citus, MySQL partitioning), leading the primary key with the partition column is often a hard requirement, not just an optimization.

This is the core mechanical argument for composite natural keys: the index structure encodes a query pattern you actually use, rather than encoding randomness you have to work around.

Code: Three Approaches Side by Side

1. Surrogate UUID

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id INT NOT NULL,
    order_number INT NOT NULL,
    total NUMERIC(10,2) NOT NULL,
    UNIQUE (tenant_id, order_number)
);

Note what happened here: the UUID is the primary key, but the actual business uniqueness rule — one order_number per tenant_id — still has to be enforced with a second unique index. You're paying for two indexes to express one constraint.

2. Composite Natural Key

CREATE TABLE orders (
    tenant_id INT NOT NULL,
    order_number INT NOT NULL,
    total NUMERIC(10,2) NOT NULL,
    PRIMARY KEY (tenant_id, order_number)
);

One index. The primary key is the business rule. No surrogate value has to be generated, transmitted, or reconciled against anything.

3. Foreign Keys Under Each Model

This is where the tradeoff becomes concrete. A child table referencing the UUID model:

CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES orders(id),
    sku TEXT NOT NULL,
    qty INT NOT NULL
);

The same child table under the composite model:

CREATE TABLE order_items (
    tenant_id INT NOT NULL,
    order_number INT NOT NULL,
    line_no INT NOT NULL,
    sku TEXT NOT NULL,
    qty INT NOT NULL,
    PRIMARY KEY (tenant_id, order_number, line_no),
    FOREIGN KEY (tenant_id, order_number) REFERENCES orders(tenant_id, order_number)
);

The composite foreign key is wider (12 bytes here vs. 16 for a UUID) but it's not random — it inherits the same clustering benefit down through the child table, and tenant_id is available on the child row without a join, which matters a lot for row-level security and partition pruning.

C# / EF Core Mapping

public class Order
{
    public int TenantId { get; set; }
    public int OrderNumber { get; set; }
    public decimal Total { get; set; }
    public List<OrderItem> Items { get; set; } = new();
}

public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.HasKey(o => new { o.TenantId, o.OrderNumber });
    }
}

public class OrderItem
{
    public int TenantId { get; set; }
    public int OrderNumber { get; set; }
    public int LineNo { get; set; }
    public string Sku { get; set; } = default!;
    public int Qty { get; set; }
}

public class OrderItemConfiguration : IEntityTypeConfiguration<OrderItem>
{
    public void Configure(EntityTypeBuilder<OrderItem> builder)
    {
        builder.HasKey(oi => new { oi.TenantId, oi.OrderNumber, oi.LineNo });

        builder.HasOne<Order>()
            .WithMany(o => o.Items)
            .HasForeignKey(oi => new { oi.TenantId, oi.OrderNumber });
    }
}

EF Core supports composite keys natively via HasKey with an anonymous type — there's no library-level penalty for choosing this model, only a slightly more verbose mapping than a single Guid Id property.

When Natural/Composite Keys Win

When UUIDs (Surrogate) Still Win

Performance Comparison

Benchmarks vary by engine and hardware, but the directional results are consistent across published tests (PostgreSQL and MySQL, tables in the 10M–100M row range, standard SSD-backed instances):

The composite key doesn't beat a plain sequential integer on raw insert throughput — nothing beats pure append. What it beats is the UUID, while additionally giving you a query-aligned clustering order that a bare surrogate integer doesn't provide either, since a bare integer PK carries no business meaning to range-scan on.

A Note on UUIDv7 as a Middle Ground

It's worth acknowledging the alternative that narrows this gap: UUIDv7, which is time-ordered and therefore append-mostly like an integer, while remaining globally unique without coordination.

-- Postgres 18+ (native), or via extension on earlier versions
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    tenant_id INT NOT NULL,
    order_number INT NOT NULL
);

UUIDv7 solves the random insert problem that plagues UUIDv4, but it does not solve the "two indexes for one business rule" problem, and it still costs 16 bytes vs. 4–8 for the natural composite columns you likely already have. If the goal is coordination-free uniqueness at global scale, UUIDv7 is usually the better UUID. If the goal is aligning storage with an existing, stable business key, composite natural keys still win on width and semantic clarity.

Decision Checklist

Use this as a quick filter when modeling a new table:

1. Does a genuinely stable, unique business identifier already exist (tenant+code, ISO/standard code, natural document number)? If yes, composite/natural key is worth strong consideration.
2. Will this table be queried or partitioned primarily by one of those columns? If yes, that's a strong signal to lead the composite key with it.
3. Do you need globally unique, coordination-free ID generation (offline clients, multi-region writers without a shared sequence)? If yes, favor a surrogate — UUIDv7 over UUIDv4 if insert locality matters.
4. Is the "natural" key at risk of changing (e.g., email as a key, when emails can be edited)? If yes, don't use it as a key — natural keys must be immutable, not just unique today.
5. Are you already paying for a redundant unique index next to a surrogate PK to enforce the real business rule? That's a sign the surrogate key isn't earning its cost.

Conclusion

UUIDs solve a real problem — coordination-free, collision-resistant, non-guessable identifiers — and they're the right default when that problem exists. But a large share of schemas reach for a UUID surrogate key by convention, on tables that already have a perfectly good, stable, immutable natural identifier sitting in the columns. In those schemas, a composite natural key is narrower, avoids a redundant unique index, and — critically — aligns the physical storage order with the query pattern the application actually uses. The right default isn't "always UUID" or "always natural key"; it's checking whether uniqueness already exists in the data before manufacturing a new identifier to represent it.

---

Tools and Versions

Need some UUIDs for testing? Try our tools for generating UUIDv4   UUIDv7   ULID   NanoID and decoding validating.
For a detailed comparison of UUID versions, visit our Complete Guide to UUID Versions.