> ## Content Index
> Fetch the complete content index at: https://stalin-pereira.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Time-Sortable IDs: The Hidden Power of ULIDs
- URL: https://stalin-pereira.com/time-sortable-ids-the-hidden-power-of-ulids/
- Published: 2025-12-08T10:37:00.000Z
- Updated: 2026-10-16T19:08:11.000Z
- Description: How combining a timestamp with randomness (ULID/CUID) gives you sorting for free without a `created_at` index
- Author: Stalin Pereira
- Tags: Databases

**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?