UUID as a Primary Key — Should You Use It?

UUID primary keys solve a real problem: how do you create unique IDs across multiple servers without a central counter? The answer — a 128-bit random identifier with a collision probability so low it is effectively impossible — is why every major database supports UUID natively. The question is whether that benefit outweighs the storage and index cost.

This guide covers the actual tradeoffs so you can choose the right primary key type for your use case, with SQL examples for PostgreSQL, MySQL, and SQL Server.

UUID vs Integer Primary Key: The Real Tradeoff

Integer primary keys are 4 bytes (INT) or 8 bytes (BIGINT). A UUID is 36 characters as a string — or 16 bytes as a binary value. That size difference compounds across every index, every foreign key, and every join.

Factor	Integer (BIGINT)	UUID v4
Storage per value	8 bytes	16 bytes (binary) / 36 bytes (text)
Index size	Smaller, faster scans	2–4x larger
Sequential inserts	Naturally ordered — fast B-tree inserts	Random — causes page fragmentation
Distributed generation	Requires central counter or coordination	Any node generates independently
Guessability	Sequential — easy to enumerate records	Random — not guessable via URL
Human readability	Easy to type/share	Verbose, error-prone

The short version: integers win on performance in single-node systems. UUIDs win on scalability and security in distributed or public-facing systems.

When to Use UUID as a Primary Key

Use UUID when:

• Distributed writes — multiple services or database shards create records simultaneously. No central sequence needed.
• Public-facing IDs — you expose the ID in URLs or APIs. Sequential integers let anyone enumerate your records: /orders/1, /orders/2. A UUID reveals nothing.
• Microservices — each service can generate its own IDs without contacting a central registry.
• Data merging — when you periodically merge records from separate databases or data sources, UUID collision is practically impossible. Integer collision is certain.
• Replication-heavy systems — multi-master replication with integer keys requires sequence coordination. UUIDs don't.

Use integer when:

• Single-node application with one database
• Heavy analytics queries (range scans are faster on sequential keys)
• Storage is constrained (IoT sensors, embedded systems)
• The table has billions of rows and index size matters significantly

Sell Custom Apparel — We Handle Printing & Free Shipping

How to Use UUID as a Primary Key in PostgreSQL, MySQL, and SQL Server

PostgreSQL (recommended approach)

-- Enable the extension (once per database)
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Or in PostgreSQL 13+, pgcrypto is built-in via gen_random_uuid()
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

PostgreSQL stores UUID natively as 16 bytes, so you don't pay the 36-byte text penalty.

MySQL 8+

CREATE TABLE orders (
  id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
  customer_id BINARY(16) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Read back as string:
SELECT BIN_TO_UUID(id, 1) as id FROM orders;

The 1 flag in UUID_TO_BIN(UUID(), 1) reorders the time component, making sequential UUIDs for better index performance — this is essentially UUID v6 behavior.

SQL Server

CREATE TABLE orders (
  id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
  customer_id UNIQUEIDENTIFIER NOT NULL,
  created_at DATETIME2 DEFAULT GETUTCDATE()
);

Use NEWSEQUENTIALID() instead of NEWID() in SQL Server — it generates sequential GUIDs that avoid page fragmentation.

The Index Fragmentation Problem — and Why UUID v7 Solves It

The biggest performance complaint about UUID v4 primary keys is index fragmentation. Because v4 UUIDs are completely random, new rows insert at random positions in the B-tree index — not at the end. This causes "page splits" where the database has to shuffle existing data to make room, slowing inserts significantly on high-write tables.

There are three solutions:

• UUID v7 — time-ordered UUID format. The first 48 bits are a millisecond timestamp, so new UUIDs are always larger than old ones. Inserts go to the end of the index, eliminating fragmentation. Available natively in PostgreSQL 17 via uuidv7(), and via libraries in most languages. See our UUID version comparison guide for details.
• MySQL's UUID_TO_BIN(UUID(), 1) — reorders the UUID bytes to make them sequential. Effectively what UUID v6 does.
• SQL Server's NEWSEQUENTIALID() — generates sequential GUIDs specifically to prevent fragmentation.

If you are on a high-insert table (millions of rows/day), UUID v7 or an ordered variant is worth the upgrade. For most applications, standard UUID v4 performance is more than adequate.

Generating Test UUIDs Quickly

When you need to populate test data, seed a migration, or verify a query, you need UUIDs fast. The WildandFree UUID Generator generates v4 UUIDs instantly in your browser — one at a time or ten at a time, click to copy, no signup.

For bulk test data directly in SQL:

-- PostgreSQL: generate 1000 test UUIDs
SELECT gen_random_uuid() FROM generate_series(1, 1000);

-- MySQL 8+
SELECT UUID() FROM information_schema.columns LIMIT 1000;

-- SQL Server
SELECT NEWID() FROM sys.objects;

For application-level generation, see our guides on UUID in JavaScript/Node.js and UUID in Python.

Frequently Asked Questions

Is UUID really unique enough to use as a primary key?

Yes. UUID v4 has 122 random bits, giving 2^122 possible values. If you generated 1 billion UUIDs per second for 100 years, the probability of a single collision would be roughly 50%. For any realistic database workload, collisions are not a practical concern.

Should I store UUIDs as strings or binary?

Binary (16 bytes) is always preferable for primary keys and indexed columns. String storage (36 chars as VARCHAR or CHAR) costs 2-4x more storage and makes indexes larger. PostgreSQL has a native UUID type that stores as 16 bytes. MySQL should use BINARY(16) with UUID_TO_BIN().

Can I combine a UUID primary key with an auto-increment ID?

Yes — this is a common pattern. Use an internal auto-increment integer ID for joins and performance, and a UUID as a public-facing "external ID" exposed via your API. This gives you the performance of integers internally and the security of UUIDs externally.

What is the difference between UUID and GUID?

Nothing meaningful for practical use. GUID (Globally Unique Identifier) is Microsoft's term for the same 128-bit identifier format. SQL Server uses UNIQUEIDENTIFIER/NEWID()/NEWSEQUENTIALID(). PostgreSQL and MySQL use UUID. The underlying format is identical. See our full GUID vs UUID guide.