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.

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

References

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

Tags

Related Terms