PostgreSQL UUIDv7: Time-Ordered IDs for Modern Applications

How UUIDv7 solves the performance problems of UUIDv4 in PostgreSQL. Covers index locality, generation strategies, and migration from serial or UUIDv4 columns.

April 1, 2026·Databases Data & Databases

postgresqluuiduuidv7primary-keysindexing

UUIDv4 has been the default choice for distributed primary keys for over a decade, but its randomness creates a well-known problem: B-tree index fragmentation and write amplification. UUIDv7, standardised in RFC 9562, solves this by embedding a millisecond-precision timestamp in the most significant bits. PostgreSQL 18 adds native support, making it practical to adopt for new tables and worth considering for migrations.

This article explains the mechanics, covers implementation in PostgreSQL 18, walks through migration from UUIDv4, and addresses the trade-offs. It is written for backend engineers and DBAs who manage PostgreSQL in production. The databases hub provides broader reading resources, and the data and databases path covers foundational database engineering skills.

For context on the broader PostgreSQL 18 release, see the PostgreSQL 18 upgrade guide.

Why UUIDv4 causes write hot spots

A B-tree index stores entries in sorted order. When you insert a new row with a UUIDv4 primary key, the random value lands at an unpredictable position in the index. This means:

Random I/O: the database must read the leaf page where the new entry belongs, which is rarely the same page as the last insert
Poor cache utilisation: hot pages are scattered across the index, reducing the effectiveness of the buffer cache
Write amplification: random insertions cause more page splits and WAL traffic

For tables with high insert rates (logging, events, IoT telemetry), these effects compound. Insert throughput degrades as the table grows, and storage I/O costs increase.

How UUIDv7 fixes this

UUIDv7 places a Unix timestamp (millisecond precision) in the first 48 bits of the UUID. The remaining bits contain random data for uniqueness within the same millisecond.

The result:

Time-ordered: UUIDv7 values generated at approximately the same time are adjacent in B-tree sort order
Sequential inserts: new rows land at the right edge of the index, just like auto-incrementing integers
Cache-friendly: the hot pages are always the most recently written ones, maximising buffer cache hits

UUIDv7 retains all the benefits of UUIDs: globally unique, no central coordinator, safe for distributed systems, and 128 bits wide.

Implementing UUIDv7 in PostgreSQL 18

PostgreSQL 18 includes a built-in uuidv7() function. Use it as a column default:

CREATE TABLE events (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  event_type text NOT NULL,
  payload jsonb,
  created_at timestamptz DEFAULT now()
);

The created_at column is still useful for human-readable timestamps and queries with explicit time ranges. The UUID embeds a timestamp, but extracting it requires parsing—using a dedicated timestamp column is cleaner for application queries.

Index behaviour comparison

Insert throughput

On a table with 100 million rows, UUIDv7 primary keys typically sustain 2–5× higher insert throughput than UUIDv4, depending on the ratio of table size to available buffer cache. The improvement is most dramatic when the index no longer fits in memory.

Index size

UUIDv7 indexes are the same physical size as UUIDv4 indexes (both are 128-bit values), but UUIDv7 indexes have fewer page splits over time, resulting in better fill factors and slightly smaller on-disk size for the same row count.

Read performance

For point lookups by primary key, performance is identical. For range scans (e.g., "all events in the last hour"), UUIDv7 is significantly faster because the target rows are physically adjacent in the index and table (assuming no aggressive HOT updates have scattered them).

Migrating from UUIDv4

New tables

Use UUIDv7 for all new tables. There is no downside compared to UUIDv4 and meaningful upside for insert-heavy workloads.

Existing tables with low insert rates

If the table has fewer than a few thousand inserts per day, the UUIDv4 fragmentation cost is negligible. Migration is optional.

Existing tables with high insert rates

For tables with high insert throughput where UUIDv4 fragmentation is a measured problem:

Add a new UUIDv7 column alongside the existing UUIDv4 primary key
Backfill the UUIDv7 column (you can derive approximate values from the created_at timestamp)
Update application code to use the new column
Swap the primary key constraint
Drop the old column

This is a non-trivial migration. Plan it as a multi-sprint project with thorough testing at each step.

Sorting and time extraction

Because UUIDv7 values are time-ordered, you can sort by the UUID column to get chronological order. However, the precision is milliseconds, so rows inserted within the same millisecond may appear in any order.

If you need to extract the embedded timestamp for debugging or auditing, you can write a SQL function that parses the first 48 bits. For application logic, rely on an explicit created_at column instead—it is clearer and avoids coupling your application to the UUID internal format.

Trade-offs

UUIDv7 leaks creation time. The embedded timestamp is not encrypted. If your primary keys are exposed to users and creation time is sensitive information, consider this before adopting UUIDv7.
Clock skew in distributed systems can cause UUIDv7 values to be slightly out of order. This rarely matters for B-tree performance but can affect strict ordering guarantees.
Millisecond collisions: if you generate thousands of UUIDs per millisecond on a single node, the random bits provide collision resistance. For extremely high throughput (millions per second), consider a counter-based approach within the sub-millisecond portion.
UUIDv7 does not replace sequences for cases where you need a compact, human-readable identifier. UUIDs are 36 characters in their text representation.