How to choose between UUID and auto-increment IDs

Choosing between UUID and auto-increment integer IDs is one of the most common database design decisions. UUIDs are globally unique and can be generated client-side without a round-trip to the database; integers are compact, cache-friendly, and sort naturally. The right answer depends on your data volume, distribution model, and privacy requirements. This guide explains the trade-offs so you can make the right choice before your schema is in production.

Step-by-step guide

1. Choose UUID when you need global uniqueness or client-side generation: If your system is distributed (multiple writers, microservices, multi-tenant), or if clients generate IDs before writing to the server, use UUID v4. You can generate thousands of IDs without a database round-trip and merge data from different sources without conflicts.
2. Choose auto-increment when you need compact, sortable keys: Integer IDs are 4–8 bytes vs 16 bytes for UUID, sort by insertion order, and perform better in clustered indexes (B-tree pages stay ordered). Use them for high-volume tables where index fragmentation is a concern.
3. Use UUID v7 as a middle ground: UUID v7 (RFC 9562) encodes a millisecond timestamp in the high bits, making UUIDs sort chronologically. This gives you global uniqueness and client-side generation while preserving B-tree index locality. Generate a test UUID v7 with: const { v7: uuidv7 } = require('uuid'); console.log(uuidv7());

Frequently asked questions

Can I expose UUID primary keys in URLs?

Yes, and this is a common reason to prefer UUIDs over integers — integers in URLs reveal record counts and make sequential enumeration attacks trivial. A UUID in a URL (/orders/550e8400-e29b-41d4-a716-446655440000) reveals nothing about the total number of records.

How much storage does a UUID use vs an integer?

A UUID stored as BINARY(16) uses 16 bytes. A BIGINT uses 8 bytes. Stored as CHAR(36) with hyphens it uses 36 bytes. For large tables, the storage difference compounds in every index that includes the primary key. Use BINARY(16) if storage is a concern.