Skip to content
EngineerData
Menu
Design
English

Engineer / Data / Portfolio

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.

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.

Contact

Still working out what you need?

Tell us what is going wrong, or what you are trying to build. We will tell you what it would take, roughly what it would cost, and whether we are the right people for it.

From 1,400 DKK an hour, excluding VAT — and less per hour on a longer booking.

welcome@engineer.company +45 50 10 61 84 Copenhagen, Denmark