PostgreSQL: UUIDv4 vs UUIDv7

About a year ago, PostgreSQL 18 delivered native support for UUIDv7. In the article below, I'm gonna deep dive into the impact of UUIDv4 in comparison with the new UUIDv7.

---

TL;DR

• While on a small amount of data, the amount of time needed to insert v4 and v7 is so close; on a larger data set, UUIDv7 surprisingly wins with almost 10x faster results than a common UUIDv4.
• Meanwhile, the resulting index size is growing linearly: v7 index size is always about a quarter less than v4.

LR;WI

First, let's define the main difference between UUIDv4 and UUIDv7. As RFC 9562 says:

UUIDv7 features a time-ordered value field derived from the widely implemented and well-known Unix Epoch timestamp source, the number of milliseconds since midnight 1 Jan 1970 UTC

What does it mean? Well, in general, this means that while UUIDv4 was implemented to generate truly (or pseudo) random values, UUIDv7 tries to solve the same problem but with a little difference: the first 48 bits are filled with a UTC timestamp.

I picked 2 main measurements to compare v4 and v7:

• Amount of time for insertion
• Resulting index size on disk

Measurements were performed on 1, 10, and 100 million rows. UUIDs were stored in a column marked as PRIMARY KEY, which means PostgreSQL will by default generate a unique B-tree index for them.

First, let's take a glance at the results, then check out some source code. The charts below represent a comparison of the measurements we defined above.

Important note: the exact values, of course, can differ on a different setup.

Important note 2: as described below, the tests were inserting all the data at once, without extra latency from application throughput and additional WALs per transaction.

While the first chat was kinda easy to understand, the second is more interesting: the amount of time needed to insert a large portion of UUIDv4 is much more than UUIDv7. The reason? The way how B-tree indexes work under the hood.
In general, to insert a new UUIDv7, all you need is to find the last leaf. At the same time, to insert a UUIDv4, PostgreSQL has to load completely unrelated pages into RAM to find a proper leaf for the new indexed item.

Funny fact: I wanted to add a billion rows to these charts, but it took too much time on my PC, like more than an hour for UUIDv4.

Code

This part is optional and describes how to reproduce these results locally. All source code is located on my GitHub, so feel free to ask any questions or report a problem!

First, let's create a database:

services:
  postgres:
    image: 'postgres:18.6'
    container_name: postgres
    ports:
      - '5432:5432'
    environment:
      POSTGRES_USER: 'user'
      POSTGRES_PASSWORD: 'pwd'
      POSTGRES_DB: 'db'
    volumes:
      - postgres_data:/var/lib/postgresql
      - ./init.sql:/docker-entrypoint-initdb.d/01-init.sql:ro

volumes:
  postgres_data:

The init.sql mentioned in volumes contains only table definitions:

CREATE TABLE test_v7(id UUID NOT NULL PRIMARY KEY);
CREATE TABLE test_v4(id UUID NOT NULL PRIMARY KEY);

Second, let's run our tests. While benchmark.py contains all the code to make it for you, if you want to make it manually:
Enable query timing by typing \timing on in the psql shell.

Then let's generate 1 million rows for v7 and v4:

insert into test_v7(id)
select uuidv7() from generate_series(1,1_000_000);

insert into test_v4(id)
select uuidv4() from generate_series(1,1_000_000);

Enabled timing will produce us the amount of time needed to generate this test data.

And last, let's check the index sizes:

SELECT pg_size_pretty(pg_relation_size('test_v7_pkey'));
SELECT pg_size_pretty(pg_relation_size('test_v4_pkey'));

Also, if you want to try on a different dataset size, do not forget to clear all tables first:

truncate table test_v7;
truncate table test_v4;

vacuum (index_cleanup on, full) test_v7;
VACUUM (index_cleanup on, full) test_v4;

---

Afterwords

It's also important to note that these are not the only differences between v4 and v7. For example, v7 is also better in case you want to fetch multiple UUIDs that were generated at almost the same time, since PostgreSQL will store them on the same page.

Thank you a lot for reading!