Time‑ordered keys for a growing data model
Adopted UUID v7 time‑ordered identifiers (PostgreSQL 18) as entity
keys to reduce B‑tree index fragmentation and speed up queries.
2025

Situation. Every entity needs a unique id, and the reflex choice is
a random UUID. The trouble is that random ids land all over a B‑tree
index. Inserts scatter, the index fragments, and as tables grow both
writes and range scans pay for it. On a platform meant to keep
growing, that's a slow leak you'd rather not build in.

Task. Keep the global uniqueness of a UUID but drop the
fragmentation that comes with the randomness.

Action. The standard became UUID v7, which is time‑ordered — the
leading bits are a timestamp, so new rows sort into the index
instead of peppering it. PostgreSQL 18 has this natively as
uuidv7(), wrapped in a small uuid_generate_v7() function so the same
call behaves cleanly on Azure's Flexible Server, then made the
default for entity primary keys across the schema. Nothing exotic
about it; it's the kind of decision that's cheap if you make it
early and a pain to retrofit later.

Result. UUID v7 became the configured key convention, giving new
identifiers time ordering. Reduced fragmentation and faster queries
were design aims; no before‑and‑after workload benchmark is recorded
in this case.

Categories:
1Backend Engineering	/categories/backend/	engineer.company	70
1Databases	/categories/databases/	engineer.company	70
1PostgreSQL	/categories/postgresql/	engineer.company	70
1Data Engineering	/categories/data-engineering/	engineer.company	70
1Performance Tuning	/categories/performance/	engineer.company	70

Services:
1Database Design & Modeling	/services/database-design/	engineer.company	70
1Database Performance Tuning	/services/database-performance/	engineer.company	70

hThis page on the web	URL:https://engineer.company/portfolio/quicker-answers-from-a-better-choice-of-key/	engineer.company	70
