Time-Sortable IDs (Snowflake, ULID, UUIDv7)
High Level Design·intermediate·~20 min read
- system-design
- ids
- snowflake
- ulid
- sharding
- hld
- 1. The Problem - 2. Snowflake ID (Twitter, 2010) - 3. ULID — Modern Snowflake Replacement - 4. UUIDv7 — The Standardised Version - 5. Embedding Shard IDs — The Instagram Trick - 6. What Salesforce Does — Not Time-Sortable - 7. When NOT…
What you'll learn
1. The Problem
Posts table in a NoSQL store (DynamoDB / Cassandra). Partition key = userid, sort key = postid. You want: - Query A: all posts by user123, newest first. - Query B: posts by user123 before some timestamp (feed pagination, deep scroll). If…
2. Snowflake ID (Twitter, 2010)
A 64-bit integer laid out as: - Timestamp (41 bits) — ms since a custom epoch (Twitter picked 2010-11-04). 2^41 ms ≈ 69 years of range. - Machine ID (10 bits) — 1 024 unique generator nodes. - Sequence (12 bits) — up to 4 096 IDs per nod…
2a. Why 64 Bits
Fits in a BIGINT column, a Go int64, a Java long. Half the storage of a 128-bit UUID. Fits in a CPU register. Lexically-sortable as an integer without string conversion.
2b. Query Patterns It Unlocks
- Timeline for user: WHERE userid=X ORDER BY postid DESC LIMIT 20 — one disk seek, naturally chronological. - Before/after timestamp: build a Snowflake with the target timestamp and zero in the lower 22 bits — use it as a range bound. WH…
2c. The Clock Problem
Snowflake assumes monotonic time. Real-world clocks skew, NTP corrects them, and VMs sometimes jump backwards. - Guard the generator: if now < lastTimestamp, either wait until the clock catches up, or borrow from the sequence bits. Twitt…
3. ULID — Modern Snowflake Replacement
Snowflake's downside: it's a bare int64. No URL safety, no sortable-as-string, requires a dedicated generator service with coordinated machine IDs. ULID (Universally Unique Lexicographically Sortable ID) is Snowflake's spirit with better…
4. UUIDv7 — The Standardised Version
Published as RFC 9562 (2024). Same idea as ULID but conforms to the UUID format every language/DB already understands: - Drop-in replacement for UUIDv4 in any system that already uses UUIDs. - Postgres has genrandomuuid() (v4) and third-…
5. Embedding Shard IDs — The Instagram Trick
Instagram added a twist to Snowflake: the shard ID is a slice of the ID itself. The application reads an incoming postid, extracts the 13-bit shard ID, and routes directly to Physical Postgres #437 — no routing-table lookup, no central c…
6. What Salesforce Does — Not Time-Sortable
Salesforce IDs are 15 (case-sensitive) or 18 (case-insensitive) alphanumeric characters: - The object prefix tells you the entity type at a glance. - The pod ID identifies which database server minted it. - The sequence is roughly chrono…
7. When NOT to Use Time-Sortable IDs
- ID guessability is a security concern. Sequential IDs let a competitor estimate your sign-up rate or probe adjacent records. Mitigate: time-sortable + enough random bits (ULID/UUIDv7 both have this baked in; raw Snowflake does not). -…
8. Decision Matrix
| Need | Pick | |------------------------------------------------|------------------| | Twitter-style timeline, int64 columns | Snowflake | | Greenfield, needs text / URL safety | UUIDv7 | | Snowflake-shaped but UUID-compatible | UUIDv7…
What to Say in an Interview
1. "I'd use time-sortable IDs — Snowflake or UUIDv7." Explain the 41-bit timestamp + machine + sequence layout. 2. "Primary key becomes (userid, postid) — no secondary index needed for ORDER BY createdat." 3. "Timestamp-range queries wor…