UUID vs ULID vs Auto-Increment
debt(d7/e5/b7/t7)
Closest to 'only careful code review or runtime testing' (d7). The term's detection_hints lists percona-toolkit and mysql-slow-query-log, both runtime/operational tools rather than static analysis. Index fragmentation from UUID v4 only manifests under load, and auto-increment enumeration vulnerabilities require manual security review. No automated static tool catches these patterns at development time.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix suggests switching from UUID v4 to UUID v7/ULID, but this requires migrating existing data, updating schema definitions, changing ID generation code, and potentially modifying foreign key relationships. On large tables, this is a significant migration effort within the database layer, not a one-line fix.
Closest to 'strong gravitational pull' (b7). Primary key strategy is a foundational database design choice that affects every table, every foreign key relationship, and every query pattern. The term applies_to all PHP contexts (web, cli, queue-worker), indicating system-wide reach. Once you've committed to UUID v4 on high-write tables, the fragmentation tax is paid on every INSERT and the migration cost shapes future decisions.
Closest to 'serious trap' (t7). The misconception explicitly states that UUID v4 is commonly assumed to be the best choice for globally unique IDs, when its randomness actually causes severe B-tree index fragmentation. This contradicts intuition from other ecosystems where UUIDs are recommended without performance caveats. The common_mistakes list reinforces multiple non-obvious gotchas: VARCHAR storage, auto-increment enumeration, and ULID sorting issues.
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