Should You Use a GUID as a Primary Key?

Why People Choose GUID Keys

• Generate anywhere. An app, a mobile client or a background job can create the ID before talking to the database.
• No collisions when merging. Combining data from several databases or tenants is simpler when IDs are globally unique.
• Non-sequential IDs in URLs. Random GUIDs don't reveal how many records exist or let users guess neighbouring IDs.
• Simpler distributed systems. No coordination is needed between services to avoid duplicate IDs.

The Costs

Index Fragmentation

Databases usually store a primary key in a B-tree index, and in SQL Server and MySQL's InnoDB, the primary key also determines how table rows are physically ordered (a clustered index). With an auto-increment integer, new rows always go at the end. With random GUIDs, each insert lands somewhere random, so:

• Pages fill up unevenly and split, leaving partly empty pages.
• More of the index has to stay in memory, because inserts touch pages all over it.
• Writes and reads can slow as the table grows large.

The effect is small for small tables and more noticeable for large, write-heavy ones.

Size

The primary key is copied into every secondary index (in clustered designs) and every foreign key, so a 16-byte key adds up. Storing GUIDs as strings is the worst option: always use the native uniqueidentifier (SQL Server) or uuid (PostgreSQL) type, or BINARY(16) in MySQL.

Readability

3f2b8c1e-9d4a-4f6e-8b1a-2c7d5e9f0a13 is harder to read out over the phone or type into a support tool than 48213.

Sequential GUIDs and UUID v7

Time-ordered identifiers fix most of the fragmentation problem:

• UUID v7 starts with a 48-bit Unix timestamp in milliseconds, followed by random bits. New values are larger than older ones (allowing for clock differences between machines), so inserts land near the end of the index. .NET 9's Guid.CreateVersion7(), PostgreSQL 18's uuidv7() and libraries in most languages generate it. UUID v4 vs UUID v7 compares them directly.
• NEWSEQUENTIALID() in SQL Server generates increasing values on a given server for use as a column default. It's tuned to SQL Server's own GUID sort order. See how to generate a GUID in SQL Server.

One SQL Server caveat: its uniqueidentifier sort order compares the last bytes first, so a UUID v7 that's time-ordered as text isn't automatically ordered in a SQL Server index. Check how your library writes bytes for SQL Server.

The trade-off with time-ordered IDs is that they reveal approximately when a record was created, and IDs created close together are similar. For most applications that's acceptable; for some, it isn't.

Decision Guide

A popular hybrid: an integer primary key for joins and indexing, plus a unique UUID column used in URLs and APIs. UUID vs auto-increment ID explores this pattern.

Trying the Formats

The GUID generator creates version 4 and version 7 GUIDs (as well as v1, v3, v5 and v6) in bulk, so you can see how v7 values share an increasing prefix while v4 values are scattered. That's a quick way to build test fixtures for comparing index behaviour in your own database.

Summary

GUID keys are great for distributed ID generation and merging, but random v4 values fragment indexes. Use UUID v7 or sequential GUIDs for keys, native 16-byte types for storage, and consider an integer-plus-UUID hybrid.