When to Use UUIDs as Database Primary Keys

The Primary Key Decision

Choosing between auto-incrementing integers and UUIDs as primary keys affects your entire system. The right choice depends on your scale, architecture, and operational requirements. Neither option is universally better — each carries distinct tradeoffs.

Auto-Increment IDs: Simple and Fast

Advantages

Sequential integer keys provide the best insert performance because database engines optimize for sequential writes. B-tree indexes stay balanced, pages fill efficiently, and the database never needs to split pages for out-of-order inserts.

-- PostgreSQL auto-increment
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- Insert is fast — appends to end of index
INSERT INTO users (email) VALUES ('user@example.com');
-- id = 1, then 2, then 3...

Limitations

• Not unique across tables or databases: ID 42 in users means nothing without the table context
• Predictable: Attackers can enumerate records by incrementing IDs in API requests
• Collision in distributed systems: Two servers generating IDs independently will produce duplicate values
• Merge conflicts: Combining data from two databases with auto-increment keys requires remapping

UUIDs: Globally Unique Without Coordination

Advantages

UUIDs provide uniqueness without any central coordinator. Any system can generate a UUID at any time, and the probability of collision is negligible — approximately 1 in 2^122 for UUIDv4.

-- PostgreSQL UUID primary key
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE
);

-- Insert — UUID generated without coordination
INSERT INTO users (email) VALUES ('user@example.com');
-- id = a]b3f2c8-7d1a-4e9f-b5c6-1234567890ab

When UUIDs Win

Performance Considerations

Insert Performance

Random UUIDs (v4) cause B-tree page splits because new values can land anywhere in the index. PostgreSQL resolves this partially with its UUID primary key optimization, but sequential keys still outperform for heavy write workloads.

-- Benchmark approximation (10M rows)
-- SERIAL:          ~45 seconds
-- UUID v4:         ~75 seconds
-- UUID v7:         ~50 seconds (time-ordered)

Storage Size

Always store UUIDs as the native UUID type, not as TEXT. The binary format is 16 bytes versus 36 bytes for the string representation. This difference compounds across every foreign key reference and index.

Time-Ordered UUIDs

UUIDv7 solves the page-split problem by encoding a timestamp in the first 48 bits. New values arrive in roughly sequential order, preserving B-tree insertion efficiency:

// UUIDv7 structure (simplified)
// First 48 bits: Unix timestamp in milliseconds
// Remaining 74 bits: random data
// Result: roughly sequential, globally unique

Foreign Key Impact

Every foreign key column inherits the primary key type. If your primary key is a UUID, every user_id, order_id, and comment_id column across your entire schema stores 16 bytes instead of 4.

For a table with 100 million rows and 5 UUID foreign keys, that is 6 GB of additional storage compared to integer keys. Indexes on those foreign keys grow proportionally.

-- With UUID keys
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  product_id UUID NOT NULL REFERENCES products(id)
  -- Two UUID FKs = 32 bytes per row just for references
);

-- With integer keys
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id),
  product_id INT NOT NULL REFERENCES products(id)
  -- Two INT FKs = 8 bytes per row for references
);

Hybrid Approach

Many production systems use both: a sequential integer as the primary key for internal joins and indexing, and a UUID exposed externally as a public identifier.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,          -- internal, fast joins
  public_id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),  -- external, safe to expose
  email TEXT NOT NULL UNIQUE
);

This gives you the performance of integer keys internally while protecting against enumeration and simplifying distributed generation externally.

Key Takeaways

• Auto-increment integers provide the best insert performance and smallest storage footprint
• UUIDs eliminate coordination overhead in distributed and multi-master systems
• Random UUIDs (v4) cause B-tree page splits; time-ordered UUIDs (v7) mitigate this
• Always store UUIDs as native UUID type, never as TEXT
• Foreign key columns multiply the storage difference — consider the full schema impact
• A hybrid approach separates internal performance from external safety

Try It Yourself

Generate UUIDs in any version with our free UUID Generator. Create UUIDv4, UUIDv7, and other versions instantly — all processing happens locally in your browser.

Try the UUID Generator →