UUID as Database Primary Key: Pros, Cons, and Best Practices

Why Use UUIDs as Primary Keys?

Auto-incrementing integers have been the default primary key strategy for decades, and they work well for single-server databases. But modern architectures often involve distributed systems, microservices, multi-region deployments, and client-generated IDs. In these scenarios, sequential integers break down because they require a centralized authority (the database) to assign the next value.

UUIDs solve this problem by allowing any node, service, or client to generate a globally unique identifier without coordination. This enables several powerful patterns:

• Client-generated IDs: Mobile apps and SPAs can create resource IDs before the first API call, enabling optimistic UI updates and offline-first architectures.
• Database merging: When merging data from multiple database shards or independent systems, UUIDs guarantee no primary key conflicts.
• Privacy and security: Sequential integers are trivially enumerable. An attacker who knows /api/users/1042 exists can easily discover /api/users/1043. UUIDs make resource enumeration impractical.
• Microservice independence: Each service can generate IDs without depending on a shared sequence generator, reducing coupling and eliminating a single point of failure.

The Performance Problem: Random UUIDs and B-Trees

The most common criticism of UUIDs as primary keys is performance. When using UUID v4 (random), every insert targets a random position in the primary key index. To understand why this is problematic, you need to understand how B-tree indexes work.

A B-tree stores data in sorted order across a hierarchy of pages. When you insert a row with a sequential key (like an auto-increment integer), the new row always goes to the last page. The database can keep this page in memory, and writes are fast and sequential on disk. But when the key is random (like UUID v4), each insert targets a potentially different page scattered across the index. This causes three compounding performance problems:

• Page splits: When a page is full and a new row needs to be inserted in the middle, the database splits the page into two half-full pages. Over time, this creates significant wasted space (typically 30-50% of index pages are only half full).
• Cache misses: Random keys spread writes across the entire index. If the index is larger than available memory, each insert may require reading a page from disk, which is orders of magnitude slower than memory access.
• Write amplification: Page splits create additional disk I/O. Each split may cascade, requiring parent pages to be updated as well. This multiplies the actual disk writes per logical insert.

In practice, the performance difference between sequential and random primary keys grows with table size. For small tables (under 1 million rows), the difference is often negligible. For large tables (10 million+ rows), random UUIDs can be 2 to 5 times slower for inserts and significantly slower for range scans.

UUID v7: The Solution to Random Fragmentation

UUID v7, standardized in RFC 9562, was specifically designed to solve this problem. By placing a 48-bit millisecond-precision timestamp in the most significant bits, v7 UUIDs are monotonically increasing over time. This means they exhibit the same sequential insert pattern as auto-increment integers, while retaining the distributed generation benefits of UUIDs.

With UUID v7, new rows always insert near the end of the B-tree index. Page splits are rare, cache utilization is excellent, and write amplification is minimal. Benchmarks on PostgreSQL consistently show UUID v7 insert performance within 5-15% of auto-increment integers, a dramatic improvement over UUID v4. For more details on how v7 works internally, see our guide on UUID Versions Explained.

Storage Formats: How to Store UUIDs Efficiently

How you store a UUID in your database has a significant impact on both storage size and query performance. There are three common approaches:

CHAR(36): Human-Readable but Wasteful

Storing the canonical string representation (550e8400-e29b-41d4-a716-446655440000) as CHAR(36) is the simplest approach but the least efficient. It uses 36 bytes per row (or 37 with null terminator), more than double the 16 bytes needed for the raw binary value. Comparisons are also slower because string comparison is more expensive than binary comparison, especially with case-insensitive collations.

BINARY(16): Compact and Fast

Storing the raw 128-bit binary value as BINARY(16) is the most space-efficient option. It uses exactly 16 bytes per row, makes binary comparisons fast, and reduces index size by more than half compared to CHAR(36). The trade-off is readability: binary values are displayed as hex in query results, which makes debugging more difficult.

-- MySQL: Store as BINARY(16)
CREATE TABLE users (
  id BINARY(16) PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

-- Insert: Convert string to binary
INSERT INTO users (id, name) VALUES (
  UNHEX(REPLACE('550e8400-e29b-41d4-a716-446655440000', '-', '')),
  'Alice'
);

-- Select: Convert binary back to string
SELECT
  LOWER(CONCAT_WS('-',
    HEX(SUBSTR(id, 1, 4)),
    HEX(SUBSTR(id, 5, 2)),
    HEX(SUBSTR(id, 7, 2)),
    HEX(SUBSTR(id, 9, 2)),
    HEX(SUBSTR(id, 11, 6))
  )) AS id,
  name
FROM users;

Native UUID Type: The Best of Both Worlds

PostgreSQL provides a native uuid data type that stores the value as 16 bytes internally but accepts and displays the standard string format. This gives you the storage efficiency of binary with the readability of strings. It is the recommended approach for PostgreSQL.

-- PostgreSQL: Native UUID type
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL
);

-- Insert with string (automatically parsed)
INSERT INTO users (id, name) VALUES (
  '550e8400-e29b-41d4-a716-446655440000',
  'Alice'
);

-- For UUID v7, generate in application code
-- and pass as parameter

Database-Specific Recommendations

PostgreSQL

PostgreSQL is the most UUID-friendly database. Its native uuid type stores values as 16 bytes, supports efficient indexing, and handles string-to-binary conversion transparently. The built-in gen_random_uuid() function generates UUID v4 values. For UUID v7, generate in application code (most languages now have v7 libraries) or use the pg_uuidv7 extension.

PostgreSQL recommendation: Use the native uuid type with UUID v7 generated in application code. If using UUID v4, consider the BRIN index type for very large tables with temporal access patterns.

MySQL / MariaDB

MySQL does not have a native UUID type. The most efficient approach is BINARY(16) with helper functions for conversion. MySQL 8.0 introduced UUID_TO_BIN() and BIN_TO_UUID() functions, and critically, the UUID_TO_BIN(uuid, 1) variant reorders bytes to place the timestamp first, which improves index performance for UUID v1 (but is not needed for v7, which is already time-ordered).

-- MySQL 8.0: Binary storage with helper functions
CREATE TABLE users (
  id BINARY(16) PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

-- Insert
INSERT INTO users (id, name) VALUES (
  UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000'),
  'Alice'
);

-- Select
SELECT BIN_TO_UUID(id) AS id, name FROM users;

SQLite

SQLite has no native UUID type. You have two options: store as TEXT (36 bytes, human-readable) or BLOB (16 bytes, compact). For most SQLite use cases (embedded applications, mobile apps, small datasets), the text representation is fine because the performance difference is negligible at typical SQLite scale.

Indexing Strategies

In InnoDB (MySQL) and similar storage engines that use clustered indexes, the primary key determines the physical storage order of rows. A random UUID primary key means rows are physically scattered on disk, which degrades not just insert performance but also range scan performance. UUID v7 as a clustered primary key solves this.

An alternative approach is to use an auto-increment integer as the clustered primary key (for physical ordering) and a UUID as a secondary unique index (for external reference). This gives you sequential disk layout for internal operations and UUID-based access for API consumers. The cost is an additional index (16 bytes per row plus B-tree overhead).

-- Hybrid approach: integer PK + UUID unique index
CREATE TABLE orders (
  pk BIGSERIAL PRIMARY KEY,      -- Internal, sequential
  id UUID UNIQUE NOT NULL,        -- External, for API
  customer_id UUID NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- API queries use UUID
SELECT * FROM orders WHERE id = $1;

-- Internal joins use integer PK (faster)
SELECT * FROM orders WHERE pk BETWEEN $1 AND $2;

Common Pitfalls

• Storing as VARCHAR instead of a fixed type: VARCHAR(36) adds length-prefix overhead on every row. Use CHAR(36) or, better yet, BINARY(16) or a native UUID type.
• Case sensitivity: UUID hex characters can be uppercase or lowercase. Ensure your storage and comparison logic is case-insensitive, or normalize to lowercase before storing.
• Forgetting to index: If a UUID column is used in joins or WHERE clauses but is not the primary key, add an explicit index. UUID comparisons without an index trigger full table scans.
• Generating in the database when you could generate in the application: Database-side UUID generation (gen_random_uuid()) works but means the ID is not known until after the insert. Application-side generation (especially with UUID v7) gives you the ID upfront, enabling optimistic patterns and reducing round trips.

Practical Recommendations

Based on current best practices and the availability of UUID v7:

• New projects: Use UUID v7 as the primary key. Generate in application code. Use PostgreSQL's native uuid type or MySQL's BINARY(16).
• Existing projects with UUID v4: Consider migrating to UUID v7 for new tables. Existing v4 columns work fine alongside v7 since they share the same format.
• Performance-critical systems: Use the hybrid approach (integer clustered PK + UUID unique index) for the best of both worlds.
• APIs that expose IDs: Always use UUIDs (v4 or v7) for external-facing identifiers, even if your internal primary key is an integer. This prevents enumeration and decouples your internal schema from your public API.

Try It Yourself

Generate UUID v4 and v7 values side by side with our UUID Generator to see the difference in structure and ordering. Paste UUIDs into the Validate tab to extract timestamps from v7 values and see the binary breakdown. If your API returns JSON with UUID fields, use our JSON Formatter to inspect and validate the response.