PostgreSQL 18 added uuidv7(), which generates time-ordered UUIDs inside the database. It is listed among the major features in the PostgreSQL 18 release notes.

UUIDs are convenient when IDs must be generated in distributed locations, but random UUIDv4 primary keys scatter writes across a B-tree. Does switching to UUIDv7 solve the primary-key problem?

A comparison published on August 21, 2026 found that, after inserting one million rows, the UUIDv7 index was smaller and more densely packed than its UUIDv4 counterpart. Following the mechanism through timestamp exposure and coexistence with existing IDs shows when UUIDv7 is actually the right choice.

UUIDv7 timestamp bits move B-tree inserts toward the right edge

UUIDv4 uses random values in every bit except those reserved for the version and variant. Its ordering is therefore random, so new primary-key entries can land on leaf pages throughout the B-tree.

UUIDv7 uses a different layout. RFC 9562 places a Unix timestamp with millisecond precision in the most significant 48 bits. The remaining 74 bits, excluding version and variant fields, can hold random data and optional constructs that improve monotonicity. Because newer values tend to have larger prefixes, their insertion positions cluster near the right edge of an ascending B-tree.

PostgreSQL 18 places a 12-bit sub-millisecond component after the millisecond timestamp and uses random data in the remainder. The official UUID function documentation describes the value as millisecond Unix time plus sub-millisecond time plus random data. The commit that added the implementation also documents monotonic behavior within a single backend.

For a new table, adoption is straightforward.

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

The uuid type and primary-key constraint stay the same; only the default becomes uuidv7(). PostgreSQL compares UUID values directly, so placing time in the leading bits changes how those keys are ordered in the index.

A one-million-row comparison reduced the UUID index from 38 MB to 30 MB

The published test inserted the same payload into two one-million-row tables, one using gen_random_uuid() and one using uuidv7(). Their primary-key indexes produced these results.

Nearby UUIDv7 values arrive consecutively, allowing PostgreSQL to fill the current leaf page before moving to the next. UUIDv4 inserts are scattered and are more likely to require a page split when a value belongs in the middle of an already occupied page. The PostgreSQL B-tree documentation confirms that B-trees impose their data type's sort order and that a split creates a new page and a parent downlink.

Average leaf density and leaf fragmentation come from the pgstatindex() function in the pgstattuple extension. As the official documentation notes, these values are accumulated page by page and are not an instantaneous view of the entire index. Like index size, they vary with workload and measurement timing. The 38 MB and 30 MB figures are therefore a useful demonstration of the mechanism, not a universal performance promise.

The cause-and-effect chain still matters: UUID randomness changes B-tree write locations, which affects page density and cache usage. Write-heavy tables that genuinely require UUIDs are good candidates for a comparison with their own data distribution.

Time-ordered UUIDs expose creation time without guaranteeing a global event order

Sortability comes from carrying information. The leading 48 bits of a UUIDv7 expose millisecond-level time, and PostgreSQL 18 provides uuid_extract_timestamp() to read it. The function documentation cautions that the extracted value is not necessarily the exact generation time; that depends on the generating implementation.

UUIDv7 is not an automatic replacement when a public API identifier must hide creation order or approximate creation time. The security considerations in RFC 9562 also say not to assume that UUIDs are hard to guess and not to use possession of a UUID as a security capability. When UUIDs are involved in a security operation, the RFC recommends UUIDv4.

The scope of ordering matters too. PostgreSQL explicitly documents monotonicity within the same backend. That is not a guarantee of a strict total order for business events generated concurrently across multiple backends or nodes.

Even when the primary key appears able to replace created_at, keep a separate column when the application needs business-event time, mutable time, or audit semantics. UUIDv7 time is best treated as information for key locality and approximate ordering.

PostgreSQL 18 can start the migration without rewriting existing UUIDs

Core uuidv7() is available in PostgreSQL 18 and later. gen_random_uuid() became available without an extension in PostgreSQL 13, so a PostgreSQL 17 or earlier deployment cannot simply swap the function name.

As of August 22, 2026, PostgreSQL 18 is the current supported major release and its latest minor release is 18.6. The official versioning policy says that major upgrades require dump/restore or pg_upgrade, and recommends running the current minor release for each major version. Adopting UUIDv7 should therefore fit into a normal major-upgrade plan rather than forcing a rushed production upgrade on its own.

After upgrading to 18, a table that already has a uuid primary key can switch only its default.

ALTER TABLE customer
  ALTER COLUMN id SET DEFAULT uuidv7();

Under the ALTER TABLE specification, the new default applies only to subsequent inserts and does not change existing rows. PostgreSQL's uuid data type can store UUIDs regardless of their version or origin, so UUIDv4 and UUIDv7 values can coexist in the same column.

That makes a gradual trial possible, but it does not reorder existing UUIDv4 rows or automatically remove existing fragmentation. Compare version counts, index size, pgstatindex() density, write latency, and buffer hit rate under equivalent conditions before and after the change.

SELECT uuid_extract_version(id) AS uuid_version, count(*)
FROM customer
GROUP BY uuid_extract_version(id)
ORDER BY uuid_version;

If the application also generates IDs, inventory every client rather than changing only the database default. A system that generates IDs in both the database and application should also make ownership of ID generation explicit.

Choose BIGINT, UUIDv4, or UUIDv7 by generation topology and visible metadata

UUIDv7 is a strong default candidate when UUIDs are required, but it is not the answer for every primary key. Start by asking whether multiple nodes or clients must generate IDs without coordinating with one database.

When UUIDs are unnecessary, an 8-byte BIGINT is more compact than a 16-byte UUID and sequential assignment is naturally friendly to a B-tree. When UUIDs are required but public IDs should not expose timing, UUIDv4 still has a role. UUIDv7 fits when distributed generation and B-tree insert locality matter, and exposing time information is acceptable.

The answer to the opening question is therefore not that UUIDv7 solves every primary-key problem. For a table that genuinely needs UUIDs and would benefit from more localized writes, PostgreSQL 18's uuidv7() deserves an early comparison. On an existing table, changing only the default and measuring real index density and latency gives you a practical way to judge the benefit and the cost together.

Source

• Title: Shaun Thomas: The Time Traveler's Primary Key
• URL: https://postgr.es/p/9sX