UUID as a Database Primary Key: Best Practices

A UUID can be an excellent database primary key, but it is not a free upgrade from an integer. It buys decentralized ID generation and safer public identifiers at the cost of wider indexes. The version and storage type you choose determine whether that trade is modest or painful.

When a UUID primary key is useful

UUIDs are especially useful when multiple services or offline clients create records, databases will later be merged, or an object needs its final ID before insertion. They also avoid exposing an obvious sequential customer or order count in a public URL. If none of those apply, a BIGINT may remain simpler; compare both in UUID vs auto-increment IDs.

Why random UUID v4 can stress an index

Most relational primary-key indexes are B-trees. An increasing integer tends to append near the right edge. A random UUID v4 can land on any leaf page, which reduces locality and can increase page splits, cache misses, and write amplification as a table grows. This does not make v4 universally bad—it means workload and scale matter.

Why UUID v7 changes the trade-off

UUID v7 places a Unix millisecond timestamp at the start of the value and fills the rest with version, variant, and random data. New values therefore sort roughly by creation time. That preserves distributed generation while giving the database more-local inserts. UUID v7 is standardized in RFC 9562, which replaced RFC 4122.

Use the UUID Generator to compare a batch of v4 and v7 values. You will see v7 prefixes advance with time while v4 values remain visually random.

Store 128 bits, not formatted text

The familiar UUID string has 36 characters because it contains 32 hex digits and four hyphens. The underlying value is only 16 bytes. Prefer PostgreSQL's uuid type, SQL Server's uniqueidentifier, or a 16-byte binary representation where no native type exists. Smaller keys improve index density and reduce the size of secondary indexes that carry the primary key.

PostgreSQL UUID primary key

For v4, a compact baseline is:

CREATE TABLE events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  payload jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

UUID v7 database functions depend on your PostgreSQL release or extension. Confirm availability in the exact server version you operate before making it a schema default.

MySQL UUID primary key

MySQL users commonly store UUIDs in BINARY(16) and convert at the application boundary:

CREATE TABLE events (
  id BINARY(16) PRIMARY KEY,
  payload JSON NOT NULL,
  created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
);

Avoid mixing byte-order transformations between writers. Pick one encoding, document it, and test round trips before migrating production values.

SQL Server GUID primary key

SQL Server calls the type uniqueidentifier and the value a GUID. Random NEWID() values have the same locality concern as v4 UUIDs. Sequential strategies can improve inserts but have platform-specific ordering and disclosure behavior, so benchmark against your schema rather than assuming equivalent ordering across databases.

Operational best practices

• Keep the primary-key or unique constraint. It catches implementation bugs as well as a theoretical collision.
• Index only what queries need; wider UUIDs make unnecessary indexes more expensive.
• Generate IDs in one consistent layer or document every allowed writer.
• Benchmark realistic table sizes, secondary indexes, and concurrent inserts.
• Treat public UUIDs as identifiers, not authorization. An unguessable-looking URL still needs access control.
• Remember v7 reveals approximate creation time; use v4 if that is sensitive.

Recommended decision

For a new distributed system that needs UUID primary keys, v7 in a native UUID or 16-byte column is a strong starting point. Choose v4 when broad compatibility or timestamp privacy is more important. Choose BIGINT when centralized generation, minimal index size, and raw throughput outweigh decentralized IDs. The right answer is a schema decision backed by measurements, not a universal slogan.