PostgreSQL 18: native uuidv7() function speeds up inserts by up to 23x

PostgreSQL 18 introduced the native uuidv7() function, and a production report published by Andrew Atkinson on Planet PostgreSQL shows what that means in practice: reductions in average insert execution time of 6x to 23x in extremely high-volume tables. The case is relevant to a growing share of Brazilian applications that have adopted UUID as a primary key, often without measuring the cost of that choice on the index.

Why UUID v4 penalizes inserts

The whole discussion revolves around how PostgreSQL keeps the primary key ordered in a b-tree index. Index entries, like table rows, live in fixed-size 8 KB pages. When inserting a new row, the database needs to decide which page to place the new entry in, and it does so by comparing the first bytes of the value.

This is where the UUID format changes everything. UUID v4 is essentially random: it is not monotonically increasing. This means new values can fall anywhere in the ordering, rarely in the same page accessed recently. The result is terrible for caching. When the needed index page is not in PostgreSQL's buffer cache nor in the operating system's cache, the database falls back to a disk read, much slower, and insert latency spikes.

There is a second, less obvious cost. Since v4 values spread across many pages, the frequency of page splits increases: when a new entry needs to go into a page that is already full, PostgreSQL splits the page in two. Each split generates more WAL and more I/O. Time-based v1 suffers less than v4 because it tends to grow, but it still lags behind v7.

What UUID v7 solves

UUID v7 embeds a timestamp in the value's first bits. In practice, the generated values are approximately increasing over time, meaning they have monotonicity. This keeps the last index page "hot" in the buffer cache: consecutive inserts land in the same recently accessed region, avoiding disk reads and drastically reducing page splits. The positive side effect is a smaller index, with less CPU and I/O consumption.

Atkinson is careful about the general recommendation. He states that, for new projects, he still prefers bigint with sequences over UUID v4 as a primary key. But when UUID is already an established architectural decision in the system, v7 is the best available variant for write performance, and it has become the standard adopted by his team.

The production numbers

The environments described ran PostgreSQL 18.4, with keys mostly in v1 and some in v4. After switching the default of the qualifying columns, the author audited insert queries table by table. For many tables there was no noticeable change, an honesty worth noting. But in a handful the gain was significant. The highlighted cases:

• Table A: from 0.7 ms to 0.03 ms, a 23x reduction, in a multi-row insert query called 12,000 times per minute on a table with billions of records.
• Table B: from 0.6 ms to 0.07 ms, a 9x reduction, with 2,000 calls per minute.
• Table C: from 0.50 ms to 0.08 ms, a 6x reduction, with 9,500 calls per minute.

The pattern is clear: the benefit shows up precisely in large tables with high insertion rates, exactly where cache misses and page splits exact the highest price.

The catch: the ALTER TABLE lock

The origin of the UUIDs was heterogeneous: uuid_generate_v1() from the uuid-ossp module, gen_random_uuid() (which natively generates v4 since Postgres 13), and even v4 values sent by the client application, in which case the column default was not even used. The migration consisted of switching each table's default to uuidv7() with a single command:

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

The command executes quickly, but it requires an ACCESS EXCLUSIVE LOCK, which conflicts with every read and write operation, including SELECT. On tables that are queried constantly, there is practically no window to acquire that lock without causing blocking, and the team did not want downtime.

For rarely queried tables, the solution was to run it via the migration framework (Active Record, in Rails) with safeguards: an explicit transaction and short timeouts via SET LOCAL, to give up quickly if the lock did not come.

BEGIN;
SET LOCAL lock_timeout = '50ms';
SET LOCAL statement_timeout = '100ms';
ALTER TABLE my_table ALTER COLUMN id SET DEFAULT uuidv7();
СOMMIT;

For the busiest tables, a single attempt was not enough. The approach was to repeat the ALTER TABLE with a short lock_timeout, accepting the error when the lock was not obtained within 50 ms, and trying again. For the most stubborn cases, Atkinson built (with help from Claude, according to him) a PL/pgSQL DO block that tries up to 50 times with jittered backoff between 50 and 250 ms:

DO $$
DECLARE
  attempt INT := 0;
  max_attempts INT := 50;
BEGIN
  LOOP
    attempt := attempt + 1;
    BEGIN
      EXECUTE 'ALTER TABLE my_table ALTER COLUMN id SET DEFAULT uuidv7()';
      EXIT;
    EXCEPTION WHEN lock_not_available THEN
      IF attempt >= max_attempts THEN
        RAISE EXCEPTION 'Failed to acquire lock after % attempts', attempt;
      END IF;
      PERFORM pg_sleep(0.05 + random() * 0.2);
    END;
  END LOOP;
END $$;

After a few dozen quick attempts, the strategy found a small window and applied the change. The author also mentions a plan B that he did not need to execute: actively monitoring the queries holding the lock via pg_stat_activity and pg_locks, and canceling them with pg_cancel_backend() to open up the window, an idea credited to Ants Aasma, from the PostgreSQL community on Slack. It is an aggressive measure, with direct impact on user experience, that requires case-by-case evaluation.

When v7 is not worth it

The central trade-off of v7 is about security, not performance. Since the creation timestamp is embedded in the first bits, it can be easily decoded. This exposes the creation time of each record, something purely random v4 does not do. For tables where that metadata leak is sensitive, v4 remains the correct choice, even at the cost of write performance. That is why the team kept v4 where randomness was necessary.

It's also worth tempering expectations: the gain is not universal. As the report itself shows, a good portion of the tables saw no relevant change. V7 pays off where there is volume and a high rate of inserts on large indexes that do not comfortably fit in memory. On small or low-write tables, the migration effort may not be justified.

An important operational detail for the Brazilian audience running in the cloud: because it is part of PostgreSQL 18's core, and not an extension, uuidv7() works in managed environments like AWS RDS, which restrict which extensions can be installed. This removes a common adoption barrier. For those already carrying UUID as a primary key in production, Atkinson's conclusion is direct: a high return for relatively low effort, as long as the ALTER TABLE lock is handled with short timeouts and retries.