UUIDv7 (A Postgres Case Study)

February 2026

A Universally Unique Identifier(UUID) is a 128-bit number used to identify information in computer systems. One of the good things about UUIDs is that their uniqueness does not depend on a central registration authority, or coordination between the parties generating them.


For most applications, the probability of finding a duplicate is practically zero. Values are normally represented as a 36-character string with hexadecimal digits hyphens, for example ca421b8d-e58d-4917-9cdb-941972f44477. The canonical layout is 8‑4‑4‑4‑12 characters. The first character in the third block and the first character in the fourth block have special meaning: xxxxxxxx-xxxx-Vxxx-Wxxx-xxxxxxxxxxxx – V marks UUID version (4 for UUIDv4, 7 for UUIDv7, etc.), W encodes the variant in its upper 2 or 3 bits (the layout family of the UUID).


The Problem

Until Postgres 18, the most common way to generate UUIDs was uuidv4 through the gen_random_uuid() inbuilt function, and that while was good, uuidv4 came with some performance penalties to inserts and lookups. This is because every time you insert a record, it lands in a random position in your B-tree1 index causing page splits and fragmentation, which degrades performance over time. With non-sequential identifiers, the most recent data will be randomly dispersed within an index and lack clustering. As a result, retrieving the most recent data from a large dataset will require traversing a large number of database index pages, leading to a poor cache hit ratio.


UUIDv72 on the other hand contains values that are time-ordered since the first 48 bits contain a big-endian Unix epoch timestamp with millisecond granularity, followed by additional sub-millisecond bits and randomness. This comes with two advantages:

  1. Better insert performance and locality since new values are clustered together for better performance
  2. Since the head of the key string contains a timestamp, this means that uuidv7 provides natural sortability based on creation time, and we dont need separate columns for time based sorting.

In Postgres 183, UUIDv7 can be generated using the inbuilt uuidv7() function


Test setup

This is a simple test setup – 2 different tables with column id containing generated UUID value (either v4 or v7), used as primary key, column ord with sequentially generated bigint, preserving the row creation order.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

-- UUIDv4 (completely random keys)
CREATE TABLE uuidv4_demo (
    id uuid PRIMARY KEY DEFAULT uuidv4(), -- alias of gen_random_uuid()
    ord bigint GENERATED ALWAYS AS IDENTITY
);

-- UUIDv7 (time-ordered keys)
CREATE TABLE uuidv7_demo (
    id uuid PRIMARY KEY DEFAULT uuidv7(),
    ord bigint GENERATED ALWAYS AS IDENTITY
);

-- 1M rows with UUIDv4
INSERT INTO uuidv4_demo (id) SELECT uuidv4() FROM generate_series(1, 1000000);

-- 1M rows with UUIDv7
INSERT INTO uuidv7_demo (id) SELECT uuidv7() FROM generate_series(1, 1000000);

VACUUM ANALYZE uuidv4_demo;
VACUUM ANALYZE uuidv7_demo;

As the first step, let’s compare the costs of ordering by UUID for the two tables:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- UUIDv4
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM uuidv4_demo ORDER BY id;

Index Scan using uuidv4_demo_pkey on uuidv4_demo  (cost=0.42..60051.29 rows=1000000 width=24) (actual time=0.018..731.171 rows=1000000.00 loops=1)
  Index Searches: 1
  Buffers: shared hit=1004700
Planning Time: 0.054 ms
Execution Time: 779.181 ms

-- UUIDv7
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM uuidv7_demo ORDER BY id;

Index Scan using uuidv7_demo_pkey on uuidv7_demo  (cost=0.42..36785.43 rows=1000000 width=24) (actual time=0.018..184.005 rows=1000000.00 loops=1)
  Index Searches: 1
  Buffers: shared hit=10204
Planning Time: 0.032 ms
Execution Time: 231.240 ms

The exact buffer numbers depend on caching effects, but one thing is clear in this run: the index scan over UUIDv7 needs close to 100 times less buffer hits and is around three times faster (231 ms vs 779 ms) for the same million‑row ORDER BY id. This is the first sign that UUIDv7 is a very viable solution for a primary key when we need to replace a BIGINT column with something that has a much larger space and uniqueness, while still behaving like a sequential key from the point of view of the index.


Primary key indexes: size, leaf pages, density, fragmentation
Next I look at the primary key indexes – their size, number of leaf pages, density, and fragmentation – using pgstatindex:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

SELECT 'uuidv4_demo_pkey' AS index_name, (pgstatindex('uuidv4_demo_pkey')).*;
[
    {
        "index_name": "uuidv4_demo_pkey",
        "version": 4,
        "tree_level": 2,
        "index_size": 183484416,
        "root_block_no": 295,
        "internal_pages": 102,
        "leaf_pages": 22295,
        "empty_pages": 0,
        "deleted_pages": 0,
        "avg_leaf_density": 15.75,
        "leaf_fragmentation": 49.66
    }
]

SELECT 'uuidv7_demo_pkey' AS index_name, (pgstatindex('uuidv7_demo_pkey')).*;
[
    {
        "index_name": "uuidv4_demo_pkey",
        "version": 4,
        "tree_level": 2,
        "index_size": 31563776,
        "root_block_no": 295,
        "internal_pages": 20,
        "leaf_pages": 3832,
        "empty_pages": 0,
        "deleted_pages": 0,
        "avg_leaf_density": 89.98,
        "leaf_fragmentation": 0
    }
]

The benchmarks above are not the most robust but we can already see the following:

  • index_size is ~40 MB vs ~31.6 MB
  • leaf_pages are 4861 vs 3832 (again about 26–27% more)
  • leaf pages in the v4 index have lower average density (71 vs ~90)
  • leaf_fragmentation for v4 is about 50%, while for v7 it is 0

Summary

  • UUIDs provide an enormous identifier space (128 bits, ~3.4 × 10^38 values) where the probability of collision is negligible for real‑world workloads.
  • Traditional UUIDv4 keys are completely random. When used as primary keys in PostgreSQL, they tend to: fragment B‑tree indexes, lower leaf page density and cause highly random heap access patterns and more random I/O
  • UUIDv7, introduced natively in PostgreSQL 18 as uuidv7(), keeps the 128‑bit space but reorders the bits so that the most significant bits contain a Unix timestamp with millisecond precision (plus sub‑millisecond fraction) the remaining bits stay random
  • In practical tests with 1M rows per table: The UUIDv7 primary key index was about 26–27% smaller, with fewer leaf pages and much higher average leaf density Leaf pages in the UUIDv7 index were overwhelmingly physically contiguous, whereas the UUIDv4 leaf pages were completely fragmented

The trade‑off is that UUIDv7 embeds a timestamp, which might expose approximate creation times, but for most use cases this is acceptable or even useful. It is important though to understand your case so as to make the correct decisions.


  1. https://en.wikipedia.org/wiki/B-tree ↩︎

  2. RFC9562 - https://datatracker.ietf.org/doc/rfc9562/ ↩︎

  3. https://www.postgresql.org/docs/current/release-18.html ↩︎