UUID vs ULID vs Auto-Increment

Also Known As

TL;DR

Explanation

Auto-increment (BIGINT): simple, compact, sequential — poor for distributed systems (coordination needed). UUID v4: globally unique, no coordination, but fully random — causes B-tree index fragmentation and poor cache locality. UUID v7 (2023): UUID format with millisecond timestamp prefix — sortable, globally unique, good index performance. ULID: 26-char Base32, timestamp-sortable, URL-safe. For most PHP apps: BIGINT auto-increment for internal tables, UUID v7 or ULID for externally visible IDs (APIs, URLs). MySQL uses utf8mb4 for UUID storage; PostgreSQL has a native uuid type.

Common Misconception

Why It Matters

Common Mistakes

• UUID v4 as primary key on high-write tables — random inserts fragment the B-tree index.
• Storing UUIDs as VARCHAR(36) instead of BINARY(16) or native uuid type — 3× the storage and slower indexes.
• Exposing auto-increment IDs in URLs — reveals business data (order count, user count) and enables enumeration.
• ULIDs not sorted correctly in PHP — use a ULID library that returns lexicographically sortable strings.

Avoid When

• When you need cryptographic security or unpredictability — use a proper random number generator or cryptographic hash instead.
• When storage space is severely constrained and you cannot afford UUID's 16-byte footprint — auto-increment BIGINT is the only viable option.
• When you're building a single-server monolith with no distributed system plans — auto-increment's simplicity and performance dominates.
• When your primary concern is human readability or customer-facing sequential order numbers — these strategies are implementation details, not user-facing identifiers.

When To Use

• Use auto-increment BIGINT for internal database tables where ordering is simple, coordination is local, and you need maximum query speed.
• Use UUID v7 or ULID for user-facing IDs in APIs and URLs to avoid exposing sequential patterns and to support distributed services without central coordination.
• Use UUID v7 over ULID when you need native database type support (PostgreSQL uuid) and strict RFC 4122 compliance.
• Use ULID over UUID v7 when you prioritize human readability, URL-safe encoding, or need to parse timestamps directly from the ID without database queries.

Code Examples

-- UUID v4 as PK — random, index-fragmenting:
CREATE TABLE orders (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),  -- Random, large, slow
    total DECIMAL(10,2)
);
-- 1M inserts: B-tree constantly reorganised, cache misses everywhere

-- UUID v7 (sortable) or ULID:
CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY,  -- UUID v7: timestamp-prefixed, efficient
    total DECIMAL(10,2)
);

-- PHP with ramsey/uuid:
use Ramsey\Uuid\Uuid;
$id = Uuid::uuid7()->toString(); // 01956b3c-...: sortable, globally unique

-- Or ULID:
$id = (new Ulid())->toRfc4122(); // 01J3K2... — timestamp sortable

Tags

Related Terms

References

• https://github.com/ulid/spec