UUID v4 vs. v7 vs. ULID vs. Snowflake — which ID when?

When creating a new table, most developers reflexively reach for UUIDv4 as the primary key — it's available everywhere, unique, and simple. But v4 has a stubborn performance problem in large tables that UUIDv7 (RFC 9562, 2024), ULID, and Snowflake all solve elegantly. This article classifies the four variants and gives a pragmatic recommendation.

Why use random IDs at all?

Classic auto-incrementing integer IDs are compact and sortable but have two hard problems. First, they leak business information: anyone hitting /invoices/4711 immediately knows there are about 4,711 invoices — and can discover further tickets, orders, or user profiles by incrementing. Second, they scale poorly across distributed systems because they require a central sequence.

Random or pseudo-random IDs solve both problems: they're not enumerable, and any node can generate them independently without collision risk. The standard for this has been the UUID for decades — a 128-bit value with different properties depending on the version.

UUIDv4 — the classic

UUIDv4 is essentially 122 bits of randomness plus 6 bits of version and variant marker. It's generated from a cryptographically secure random number generator. The collision probability is astronomically low — even at a trillion IDs per second over 100 years, you wouldn't statistically expect a duplicate.

That randomness is both a strength and a weakness. Strength: no central coordination, no leak, no timestamp inside. Weakness: in a B-tree index, successive inserts are scattered across the entire index structure — each insert touches a different page, the cache barely helps, and the index fragments.

The index problem in practice

Example: a MySQL table with 100 million rows and a UUIDv4 primary key has a multi-GB InnoDB B-tree. Inserting a new row lands somewhere in the middle of the index; a page has to be read, modified, and written back. At 10,000 inserts per second, that's 10,000 random disk seeks (or at least cache misses) — write performance collapses.

With a monotonically growing ID (auto-increment, timestamp-based, etc.), inserts always land at the end of the index, on the same or a neighboring page. The cache almost always hits, the index stays compact. This is exactly where the newer ID schemes come in.

UUIDv7 — timestamp prefix per RFC 9562

UUIDv7 was standardized in 2024 with RFC 9562. It combines the first 48 bits as a Unix timestamp in milliseconds with 74 bits of randomness plus the usual 6 bits of version marker. The result: the ID is sortable by time but, with 2^74 possible random suffixes per millisecond, practically collision-free.

For databases this is a game-changer: inserts happen almost monotonically increasing, the index stays sequential, and write performance is practically that of integer IDs. At the same time, the advantages of random UUIDs are preserved — no enumeration, no central sequence, multi-region capable. Postgres, MySQL, and SQL Server have had native support or extensions since 2024/25.

ULID — the unofficial predecessor

ULID (Universally Unique Lexicographically Sortable Identifier, 2016) anticipated UUIDv7 for years: 48 bits of timestamp, 80 bits of randomness, lexicographically sortable in Crockford Base32 (26 characters instead of UUID's 36). Many languages and databases got ULID libraries long before RFC 9562 was finalized.

Functionally, ULID is almost identical to UUIDv7. Differences: shorter string form (good for URLs), Crockford Base32 avoids visually similar characters, but there's no formal RFC standard. Starting fresh today, prefer UUIDv7 — already on ULID? No reason to migrate.

Snowflake — Twitter, Discord, X

Snowflake IDs are 64-bit integers (not UUID format!): typically 41 bits of timestamp, 10 bits of machine ID, 12 bits of sequence counter per millisecond. They fit into a BIGINT, are extremely compact, sortable by time, and can produce 4,096 IDs per millisecond per node across up to 1,024 nodes without coordination.

Trade-off: Snowflake leaks the timestamp very precisely (and with it the creation date of objects — as on Twitter, where the creation date of a tweet can be reconstructed directly from the ID). If that's a problem, use UUIDv7. Snowflake shines where extreme write rates and compact IDs matter — and implementations like Sonyflake or TSID extend the concept.

Decision guide

A compact rule of thumb:

• UUIDv4: for small tables, external API IDs, tokens — anywhere sorting doesn't matter and maximum randomness counts.
• UUIDv7: today's default for new tables with high write volume. Standardized, widely supported, monotonic, no index pain.
• ULID: when you already have legacy code with it, or when the shorter string form (26 characters) for URLs matters.
• Snowflake: at very high write rates (Twitter, Discord, trading), when 64-bit BIGINT instead of 128-bit UUID saves significant storage and a timestamp leak is acceptable.

My UUID v4 trauma and the switch to v7

In 2020 I chose UUID v4 as primary key for an e-commerce project doing 2 million orders per year. Worked great — until the table hit 60 million rows. Suddenly INSERTs took 80-150 ms instead of 2 ms. The reason: B-tree indices love sequential inserts (appending at the end is fast), but random UUIDs land everywhere in the index. Every INSERT triggers page splits and index rebuilds at scattered positions.

The migration to UUID v7 took a weekend. Existing v4 UUIDs stayed as they were (no data migration problem), only new IDs are now generated as v7. A trivial code change — instead of Uuid::uuid4() simply Uuid::uuid7(). INSERT performance was back at 2-5 ms a week later. The most valuable lesson: with every architecture decision, ask 'will I still want this ID strategy at 100x my current data?'

Additional lesson: UUID v7 leaks a timestamp. In a public context (e.g. URLs with IDs) an observer can see in what order resources were created and, with luck, the approximate time. Mostly fine — orders are sorted by time anyway. But for sensitive data (e.g. internal tickets where 'the first complaint was XYZ' matters) v4 or ULID with Crockford-Base32 (less obvious) is preferable.

Migration: how to switch from auto-increment to UUIDs

If your legacy system runs on auto-increment INTs and you want to migrate to UUIDs, you have three options. (1) Hybrid: keep existing INT IDs, add new UUID columns alongside. Pro: no data risk, parallel existence. Con: permanently more complex. (2) Big bang: migrate table schemas fully, all foreign keys included. Very labour-intensive, high risk, long downtime. (3) Phased: add UUID columns in step one and maintain in parallel, drop INT columns in step two.

My actual plan for an ERP system in 2024: first added uuid CHAR(36) NOT NULL DEFAULT (UUID_v7()) to all tables (MySQL 8.0+ supports this natively via a custom function). Ran parallel for 6 weeks, every write populated both IDs. Then switched APIs to UUIDs — the old INT ID remained as fallback in 5 % of calls. After 12 weeks the INT ID was only referenced in the migration script and was dropped. Total downtime: three windows of 15 minutes each for schema changes.

Most important tip: NEVER change existing IDs. If you add a UUID alongside, definitely keep the old INT ID in a legacy_id column. External systems (accounting, analytics, logs) often reference the old IDs. That column needs no index, costs little space, but enables forensics when needed — small insurance with high return.

Storage options in detail

How you physically store UUIDs/ULIDs in the database massively impacts performance and disk space. An overview of common options in 2026:

• CHAR(36) — string with hyphens. Readable, debug-friendly, but 36 bytes plus overhead. At 60 million rows that's 2.1 GB just for the ID column. Indexes correspondingly slow.
• BINARY(16) — compact variant. Exactly the 128 bits a UUID needs. 16 bytes per row, about 4x less than CHAR(36). Indexes correspondingly faster. Downside: WHERE clauses need UNHEX(REPLACE(uuid, '-', '')) — some code overhead.
• Native uuid types. Postgres has uuid, SQL Server has uniqueidentifier, MariaDB 10.7+ has uuid. MySQL still lacks a native type — you choose BINARY(16) or CHAR(36). If you use Postgres: just use uuid and forget the underlying storage format.
• Compression on top. InnoDB has supported page compression since 5.6. With ROW_FORMAT=COMPRESSED on a UUID table you often hit 40-60 % space savings at minimal CPU overhead. Combined with BINARY(16): 60 million rows × (16+overhead) bytes drops to about 600 MB with compression.

My default config for new MySQL projects in 2026: BINARY(16) for UUIDs, ROW_FORMAT=COMPRESSED for tables with > 1 million rows. For Postgres projects: native uuid type without complication. For MariaDB: native uuid type from 10.7. For Mongo: ObjectIDs are essentially sortable IDs similar to ULID — no migration needed.

Implementation in popular languages in 2026

How to actually generate UUID v4/v7 or ULID — the most important languages with current 2026 library recommendations:

• Python. Standard library: uuid.uuid4(). For v7: uuid6 library (pip install uuid6, then uuid6.uuid7()). For ULID: python-ulid (pip install python-ulid, then ulid.new()). UUID v7 in stdlib since Python 3.13 (planned).
• JavaScript/TypeScript. In the browser: crypto.randomUUID() returns v4 natively (all modern browsers). For v7: uuidv7 package (npm install uuidv7, uuidv7()). For ULID: ulid package (npm install ulid, ulid()). Both are tree-shakeable and tiny.
• PHP. Standard: ramsey/uuid (Composer, most widely used). Uuid::uuid4(), Uuid::uuid7(). ULID: robinvdvleuten/php-ulid. Native PHP uniqid() is NOT a UUID, just a milliseconds-based string — don't use it.
• Go. github.com/google/uuid for v4 (uuid.New()). For v7: github.com/gofrs/uuid/v5 (uuid.NewV7()). ULID: github.com/oklog/ulid/v2. Go's performance is marginally better than JS or Python — relevant at very high ID generation rates.
• PostgreSQL native. Since Postgres 13: gen_random_uuid() for v4 (the pgcrypto extension is auto-loaded). For v7 since Postgres 17 (planned): uuidv7(). ULID in Postgres: extension pg_ulid. Native DB generation is 5-10x faster than application-layer generation over network round-trips.

Frequently asked questions

Can I migrate from UUIDv4 to UUIDv7?

Existing IDs remain valid — the UUID format is backward compatible. From a certain cutoff date you can create new inserts with v7 and leave old v4 IDs untouched. The index then immediately benefits for new entries. A real backfill migration is usually not worthwhile because old UUIDs are linked from external systems.

Is UUIDv7 really multi-region safe?

Yes — the RFC requires millisecond-level clock synchronization, easily achievable via NTP. If two nodes generate IDs in the same millisecond window simultaneously, the 74 random bits make collisions practically impossible. Only for a strict global sort order do you still need explicit ordering — that's not guaranteed by the UUID standard.

Should I store UUIDs as BINARY(16) or as strings?

Performance-wise: always BINARY(16). A UUID as a string (CHAR(36)) takes 2.25x the space, loads slower, and bloats indexes. Postgres has a native uuid type; MySQL and SQL Server need BINARY(16) with helper functions. The only reason for strings: debug comfort when logging — better solved with hex/uuid display in your tools.

Are UUIDs really globally unique?

Practically yes, theoretically not guaranteed. UUID v4's collision probability over 122 random bits is extremely low — generating 1 billion UUIDs per second, you'd need over 600 years before the first collision occurs with 50 % probability (birthday paradox). For any real application: yes, unique. Mathematical guarantee: no, only statistical confidence.

What is NanoID and when should I use it?

NanoID is a more compact UUID alternative: 21 characters URL-safe string (vs. UUID's 36), same collision safety. Use cases: in URLs (shorter = nicer), as visible tokens (invite links, short share IDs). Not for database primary keys — NanoID has the same index performance issues as UUID v4 without the benefit of a sortable format.

Should I use UUIDs in URLs?

Careful. UUIDs in URLs (e.g. /orders/550e8400-e29b-41d4-a716-446655440000) are technically safe (not guessable) but user-unfriendly. Anyone sharing a URL complains about the length. Recommendation: UUIDs OK for internal API endpoints, for public user-facing URLs use an additional short slug system (/orders/abc123 with internal mapping to UUID). In B2B tools (admin panels) where UX is secondary, UUIDs in URLs are fine.