UUID v7 in PostgreSQL: a practical guide

PostgreSQL has supported the uuid data type since version 8.3, released in 2008. For most of that history the conversation around UUIDs as primary keys has been a debate about whether the operational trade-offs — index fragmentation, larger storage than bigint, opaque values in logs — were worth the distributed-generation benefits. UUID v7, standardised in RFC 9562 in May 2024, materially changes that conversation. The fragmentation problem largely goes away. The other trade-offs remain, but the equation looks different.

This article covers the practical questions a developer new to UUID v7 in PostgreSQL is likely to ask: how to generate them, where to generate them, what the storage and performance characteristics look like, and what to watch out for. If you haven't yet settled on UUIDs at all, the broader trade-off against integer keys is covered in UUID vs auto-increment primary keys, and the wider distributed-generation landscape — Snowflake, ULID, and how v7 fits among them — in generating IDs in distributed systems.

Why UUID v7 matters for PostgreSQL specifically

PostgreSQL's uuid type stores 16 bytes and sorts them lexicographically by binary value. This is exactly the sort behaviour UUID v7 is designed for: the high bits of a v7 UUID encode a millisecond timestamp, so two v7 UUIDs generated milliseconds apart will compare adjacent in any binary or canonical-string sort. Inserting v7 UUIDs into a B-tree index hits the same tail page on every insert — the same pattern as auto-incrementing integers.

This matters because PostgreSQL's default primary-key index is a B-tree, and B-trees with random insert patterns suffer real costs. Each insert hits a different page, those pages fill and split, and over time the index becomes sparse and fragmented. UUID v4 as a primary key in a write-heavy table produces measurably worse insert latency and significantly larger index size than bigserial. UUID v7 closes most of that gap.

The other PostgreSQL-specific consideration is that the uuid type uses the correct RFC byte order natively. PostgreSQL doesn't suffer from the byte-order quirk that affects .NET's Guid.ToByteArray() or SQL Server's uniqueidentifier. Bytes you write into a uuid column are the bytes that get compared during indexing and sorting. As long as your application produces RFC-correct v7 UUIDs, PostgreSQL will use them correctly — see the .NET byte-order pitfall for the most common way that goes wrong.

Generating UUID v7 — where and how

You have three places to generate v7 UUIDs for a PostgreSQL table: in the database itself, in your application code, or at the connection-pool layer.

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

CREATE EXTENSION pg_uuidv7;

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

CREATE OR REPLACE FUNCTION uuid_generate_v7() RETURNS uuid AS $$
DECLARE
    unix_ts_ms bytea;
    rand_bytes bytea;
BEGIN
    unix_ts_ms := substring(int8send((extract(epoch from clock_timestamp()) * 1000)::bigint) from 3);
    rand_bytes := gen_random_bytes(10);
    rand_bytes := set_byte(rand_bytes, 0, (get_byte(rand_bytes, 0) & 15) | 112);
    rand_bytes := set_byte(rand_bytes, 2, (get_byte(rand_bytes, 2) & 63) | 128);
    RETURN encode(unix_ts_ms || rand_bytes, 'hex')::uuid;
END;
$$ LANGUAGE plpgsql VOLATILE;

var id = Guid.CreateVersion7();
// pass to ADO.NET parameter as Guid

const { v7: uuidv7 } = require('uuid');
const id = uuidv7();

from uuid_extensions import uuid7
id = uuid7()

id := uuid.Must(uuid.NewV7())

Storage and index characteristics

A uuid column in PostgreSQL takes 16 bytes per row, the same regardless of UUID version. The visible difference between v4 and v7 is in index structure, not row storage.

For an empirical comparison on a fresh table inserting 1 million rows with a uuid primary key, you can observe the following with pgstattuple:

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('events_pkey');

A v4-keyed table typically shows index leaf density around 70%, with significant internal fragmentation due to page splits during inserts. A v7-keyed table on the same workload typically shows leaf density above 90% — close to the theoretical maximum for a B-tree under append-only writes. The on-disk index size difference can be 30–40% in favour of v7 for write-heavy workloads.

Range queries by ID range also benefit. Because v7 UUIDs cluster chronologically, a query like WHERE id BETWEEN x AND y for IDs generated in a known time window can use index range scans efficiently. With v4 the same query is theoretically valid but practically useless — the range covers random pages across the index.

Foreign keys and joins

UUID v7 has no impact on foreign key behaviour. PostgreSQL compares uuid columns by binary value regardless of version, and join performance is the same as for any other 16-byte fixed-width type. There is no need to introduce version-specific indexing or comparison logic.

One thing worth noting: if you have an existing v4-keyed table and you're considering changing the primary key type, don't. Adding a v7 ID to new rows alongside the existing v4 IDs is fine; changing the primary key of an existing table is a substantial operation that ripples through every foreign key reference and is rarely worth the index benefit unless the table is extremely write-heavy. The pragmatic migration path is "use v7 for new tables; leave existing v4-keyed tables alone."

Sortability in practice

A useful property of v7 in PostgreSQL is that ORDER BY id produces chronological order without needing a separate created_at column for sort purposes. This isn't free — you still want created_at for human readability and for queries that need timestamp comparisons — but you can drop sort indexes on created_at if the table's natural ID ordering is sufficient.

-- These two queries produce equivalent ordering for v7 IDs:
SELECT * FROM events ORDER BY id LIMIT 100;
SELECT * FROM events ORDER BY created_at LIMIT 100;

The first uses the primary key index directly. The second requires a separate index on created_at (or a sort) to be efficient. For tables where chronological listing is a common access pattern, v7 lets you serve that pattern from the primary key.

Replication considerations

PostgreSQL's logical replication transmits row data including UUIDs verbatim. There is no version-specific replication behaviour. v7 UUIDs replicate identically to v4 UUIDs.

One subtle point: if you're using pglogical or similar tools that perform conflict resolution based on timestamps, v7's embedded timestamp can be used as a tiebreaker for conflicts. This isn't built in to any tool I'm aware of, but it's a viable pattern for systems that need application-level conflict resolution — the timestamp is already in the ID, no need for a separate column.

Gotchas

A few practical issues worth knowing about:

Timestamp precision and ordering. v7's timestamp is millisecond-precision. In the plain RFC layout, values generated within a single millisecond sort by their random component, which is unrelated to insert order. PostgreSQL 18's native uuidv7() is better than this: it fills 12 bits after the timestamp with a sub-millisecond fraction, which keeps values monotonic within the same session even inside one millisecond. The caveat applies to the generic format and to generators (including some application-side libraries) that don't add that fraction — there, large batches generated in the same millisecond may not sort in insert order.

Clock skew. If you're generating v7 UUIDs in application code on multiple servers, clock differences between servers will show up as out-of-order IDs in the database. The IDs are still globally unique and still mostly sorted, but the sort property degrades to "sorted with some jitter." For applications where strict total order matters, generate at the database level.

Time-traveling IDs. If a server's clock goes backwards (NTP correction, deliberate reset, virtual machine snapshot restore), subsequently-generated v7 UUIDs will appear "earlier" than IDs already in the database. This isn't a uniqueness problem — the random component still distinguishes them — but it's a sort-order anomaly. Some implementations include a clock-rollback detection mechanism that uses a counter to maintain monotonicity; the RFC discusses this in detail. PostgreSQL 18's uuidv7() guarantees monotonicity within a single session through its sub-millisecond fraction, but does not document any protection against the system clock itself moving backwards.

Privacy. Anyone with a v7 UUID can extract the millisecond timestamp it was generated at. If your IDs are exposed to users — in URLs, in API responses, in webhook payloads — those users can determine when each record was created. For most applications this is fine and even useful. For applications where creation time is sensitive (account creation timing, internal sequencing of events), use v4 instead.

When to use v7 in PostgreSQL

A short decision guide:

• New tables, especially write-heavy ones: v7 is the right default. The index benefit is real, the operational complexity is low, and PostgreSQL handles v7 natively.
• Tables where creation time should not be inferable from the ID: stick with v4.
• Tables that are small or read-mostly: v4 is fine. The fragmentation cost only matters at scale.
• Existing v4 tables: leave them alone. Migrating is rarely worth the effort.
• Multi-region or distributed systems with synchronisation requirements: v7 helps with rough ordering. For strict ordering you need something more sophisticated (a centralised generator, hybrid logical clocks, or a coordinated approach like Spanner's TrueTime).

Summary

UUID v7 is the right default for new PostgreSQL primary keys in most contexts. The format is natively supported (or trivially added via extension), sortability and index efficiency are excellent, and the operational characteristics are predictable. The cases where v4 remains the better choice are well-defined: privacy-sensitive identifiers, small read-mostly tables, and existing systems where migration cost outweighs the benefit.

If you're starting a new project on PostgreSQL today, default to id uuid PRIMARY KEY DEFAULT uuidv7() and only deviate when you have a specific reason.

For more on UUID v7 internals and a deeper look at the byte-order trap that affects .NET specifically, see UUID v7 in .NET: the byte-order pitfall and the UUID versions explained reference article. Generate v7 UUIDs in your browser with the UUID v7 generator.