Sortable ID

Definition

A sortable ID is an identifier that embeds enough ordering information — typically a timestamp — that sorting the IDs produces the same sequence as sorting by creation time. The sort operation can be a simple lexicographic string sort or a numeric comparison, with no additional lookup or join required.

Sortable IDs are also called time-ordered IDs, sequential IDs (loosely), or monotonic IDs. UUID v7 is the IETF-standardised sortable ID format, defined in RFC 9562 §5.7.

Why sorting by ID matters

In a relational database, sorting records by creation time typically requires either:

1. A sequential primary key (auto-increment integer) — sort by id ASC
2. A separate created_at timestamp column — sort by created_at ASC
3. A sortable ID — sort by id ASC directly

Option 3 eliminates the need for a separate created_at index when the only ordering requirement is “newest first” or “oldest first”. UUID v7 primary keys mean ORDER BY id and ORDER BY created_at are equivalent — one index serves both purposes.

Comparison of sortable ID formats

UUID v7 and ULID are functionally equivalent in precision and total bits. UUID v7 has the advantage of being an IETF standard with native database type support (PostgreSQL’s uuid column). ULID has a more compact base32 representation. The ULID specification on GitHub documents the format.

How UUID v7 achieves sortability

UUID v7 places the timestamp in the most significant bits:

Bits 0–47:   Unix millisecond timestamp (48 bits)
Bits 48–51:  Version field = 0111 (7)
Bits 52–63:  rand_a (12 bits, monotonic counter or random)
Bits 64–65:  Variant field = 10
Bits 66–127: rand_b (62 bits random)

Lexicographic string comparison of UUID v7 values starts with the timestamp bits. Two UUIDs with different millisecond timestamps always sort correctly regardless of their random bits. Within the same millisecond, the rand_a field (when used as a monotonic counter) provides further ordering.

Sortability in practice

PostgreSQL:

-- Returns records in creation order
SELECT * FROM events ORDER BY id ASC;

-- Time-range query without a separate timestamp column
SELECT * FROM events
WHERE id > '019236a7-0000-7000-8000-000000000000'  -- after a known time
  AND id < '019236a8-0000-7000-8000-000000000000'  -- before a known time
ORDER BY id ASC;

Generate boundary UUIDs from a target timestamp:

function uuidV7Boundary(targetMs) {
  const hex = targetMs.toString(16).padStart(12, '0');
  return `${hex.slice(0,8)}-${hex.slice(8,12)}-7000-8000-000000000000`;
}

B-tree index performance: Because UUID v7 values always increase, new inserts always land at the end of the index rather than at a random position. This eliminates the page-split fragmentation that makes UUID v4 primary keys slow on large tables. The PostgreSQL B-tree implementation documentation explains the fill factor and page split mechanics.

When sortable IDs create risk

Sortable IDs expose creation timestamps to anyone who has access to the ID value. For resources identified in URLs (user profiles, public API responses), UUID v7 discloses when the resource was created — potentially revealing account age, product launch dates, or activity volume. Use UUID v4 for externally visible identifiers where this information is sensitive.

Related terms

• Lexicographical Order — why string sort works on UUID v7
• Monotonic Counter — within-millisecond ordering
• Entropy — the random bits in a sortable ID
• Time-Based UUID — UUID v1 and v6, the older time-based formats

External references

• RFC 9562 §5.7 — UUID v7 bit layout
• Wikipedia — UUID versions
• ULID specification
• PostgreSQL B-tree implementation
• OWASP Top 10