Time-Sortable IDs: The Hidden Power of ULIDs

Why Your Database Hates UUIDs

They look cool, but they might be killing your speed.

The 'Messy Library' Analogy

• The Random Way (UUID) — Imagine a librarian throwing new books onto random shelves all over the building.
• The Result — To find the newest books, you have to run through every single aisle.
• The Better Way — Placing new books exactly at the end of the last shelf. Fast and organized.

Primary Keys Are Physical Maps

• Clustered Index — In most databases (Postgres, MySQL), the Primary Key dictates where data lives on the disk.
• The Friction — If your ID is random, the database has to constantly rearrange data on the disk to fit it in.

The 3 Contenders

• Auto-Increment (1, 2, 3...) — Fast and sorted, but leaks business size and hard to shard.
• UUID v4 (Random) — Unique and secure, but destroys database performance due to fragmentation.
• ULID — The Hybrid. Time-sorted like a number, unique like a UUID.

Deep Dive: Anatomy of a ULID

Universally Unique Lexicographically Sortable Identifier

• Part 1: Timestamp — The first 48 bits are the current time in milliseconds.
• Part 2: Randomness — The remaining 80 bits are cryptographic randomness.
• The Magic — Because the time comes first, they sort automatically.

The 'Free' Index Trick

• Standard Query — Usually, you need an index on created_at to sort posts by date.
• With ULID — Since the ID is the time, sorting by ID automatically sorts by time.
• The Savings — You save the storage space and write-penalty of an extra index.

Why Databases Love Sequential IDs

It's all about the B-Tree.

• Sequential Write — New data is appended to the end of the file. Very fast.
• Random Write — Database must split pages and move data around to insert a random ID in the middle.
• Fragmentation — Random IDs leave empty spaces on the disk, wasting storage.

Privacy & Security

• The ID Leaks Nothing — Unlike user_id = 500, a ULID doesn't reveal you only have 500 users.
• URL Safe — ULIDs use Crockford's Base32. No special characters, easy to select and copy.
• Collision Resistant — Even if two IDs are generated at the exact same millisecond, the 80 random bits protect you.

When to Use What?

• Use ULID (or UUID v7) — For high-scale distributed systems where sorting matters.
• Use Auto-Increment — For small internal tools where no one sees the IDs.
• Use Standard UUID — Rarely. If you do, ensure it's not the Primary Key.

The Business ROI

• Cheaper Storage — Less fragmentation = smaller database backups.
• Faster Apps — Quicker inserts mean better user experience during high traffic.
• Simpler Code — No need to manage separate created_at sorting logic.

The Future: UUID v7

The standard is catching up.

• Standardization — UUID v7 is basically the standardized version of ULID.
• Compatibility — It fits perfectly into existing UUID database columns.

Summary: Order from Chaos

A simple ID change can double your write throughput.

Stop fighting your database. Let time do the sorting.

Have you migrated away from standard UUIDs yet?