PostgreSQL 18: uuidv7() instead of UUID v4, and async I/O set up right

PostgreSQL 18 shipped on 25 September 2025 with two things ordinary apps benefit from almost without code changes: a uuidv7() function that stops UUID primary keys from hurting the index, and an asynchronous I/O (AIO) subsystem that lets reads queue several requests at once instead of waiting for each in turn. This article explains how each works, what to configure, and works through the numbers.

Facts checked against the PostgreSQL 18 documentation (UUID functions, runtime config, release notes) and RFC 9562 as of October 2026. Index sizes below are arithmetic with the working shown, not a benchmark.

What UUID v4 does to a B-tree

gen_random_uuid() (aliased as uuidv4() in 18) randomises all 122 bits, so each new value can land anywhere in the primary key index. The consequences:

1. Every insert touches a random leaf page. Once the index is bigger than shared_buffers, that page is often not in memory and must be read before it can be written
2. Full pages split in half, so most pages are never full and the index is bigger than it needs to be
3. WAL grows. With the default full_page_writes = on, the first change to any page after a checkpoint writes the whole 8 KB page to WAL. The more distinct pages you touch, the more full-page images you write

RFC 9562 says it plainly: non-time-ordered UUIDs such as v4 have poor database-index locality.

What uuidv7() changes

The UUIDv7 layout from RFC 9562:

Bits	Content
0-47	Unix timestamp in milliseconds (48 bits)
48-51	version = 7
52-63	12 bits (PostgreSQL uses them for sub-millisecond time)
64-65	variant
66-127	62 random bits

With time at the front, later values sort after earlier ones. New keys append at the rightmost leaf page: one hot page that stays in memory.

-- PostgreSQL 18
CREATE TABLE orders (
  id           uuid PRIMARY KEY DEFAULT uuidv7(),
  customer     text NOT NULL,
  total_cents  bigint NOT NULL
);

INSERT INTO orders (customer, total_cents) VALUES ('A', 125000) RETURNING id;

-- Read back the embedded time and the UUID version
SELECT id, uuid_extract_timestamp(id), uuid_extract_version(id) FROM orders;

uuidv7() also takes a shift interval, for example uuidv7(interval '-1 hour'), which shifts the embedded timestamp. It is handy when backfilling, so ids sort by the data’s real time. (Money is stored as integer cents in bigint, for the reasons in money should never be a float.)

Worked example: a 10-million-row primary key

Count only the leaf pages of an index on uuid (8,192-byte pages).

Size per entry

• Index tuple header 8 bytes + uuid value 16 bytes = 24 bytes (already 8-byte aligned)
• Line pointer 4 bytes → 28 bytes per entry

Usable space per page

• 8,192 − 24 (page header) − 16 (B-tree special space) = 8,152 bytes
• At 100% full: 8,152 ÷ 28 ≈ 291 entries

v7 (always appending). The docs say B-tree leaf pages are filled to fillfactor when the index is extended at the right with new largest values; the default is 90.

• 291 × 0.90 ≈ 262 entries per page
• 10,000,000 ÷ 262 ≈ 38,168 pages × 8 KB ≈ 298 MB

v4 (random). Classic B-tree analysis gives about 69% average fill (ln 2) for random inserts with half-and-half splits.

• 291 × 0.69 ≈ 201 entries per page
• 10,000,000 ÷ 201 ≈ 49,751 pages × 8 KB ≈ 389 MB

That is about 91 MB, or ~30% more for v4. Size is not the main cost, though; which pages must be in memory is. With the default shared_buffers of 128 MB, v7 only needs the hot rightmost page, while each v4 insert picks a random page out of 389 MB. The chance it is in shared_buffers is at most 128 ÷ 389 ≈ 33%; the rest come from the OS page cache or disk.

Measure it on your own machine with pgstattuple:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
CREATE TABLE t4 (id uuid PRIMARY KEY DEFAULT uuidv4(), n int);
CREATE TABLE t7 (id uuid PRIMARY KEY DEFAULT uuidv7(), n int);
INSERT INTO t4 (n) SELECT g FROM generate_series(1, 1000000) g;
INSERT INTO t7 (n) SELECT g FROM generate_series(1, 1000000) g;

SELECT 'v4' AS kind, leaf_pages, avg_leaf_density FROM pgstatindex('t4_pkey')
UNION ALL
SELECT 'v7', leaf_pages, avg_leaf_density FROM pgstatindex('t7_pkey');

By the arithmetic above, a million rows should give roughly 3,800 leaf pages for v7 and roughly 5,000 for v4. If yours differ a lot, check for other indexes or a custom fillfactor.

Moving from v4 to v7

No rewrite needed. Every UUID version lives in the same uuid type; change only the default.

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

This is a metadata-only change. Old rows stay v4 and scattered; new rows are v7 and append. If your app generates ids in code, change the library there too. For bigger schema changes on a live system, follow zero-downtime PostgreSQL migrations.

v7 helps the index, not secrecy. Ids shown to outsiders whose creation time is sensitive should stay v4.

Async I/O: io_method

Before 18, PostgreSQL issued a read and waited for it, one at a time, relying on OS readahead. 18 adds an AIO subsystem so a backend can queue several reads at once. In 18 it covers sequential scans, bitmap heap scans and vacuum. The release announcement reports benchmarks showing up to 3x in certain scenarios when reading from storage.

io_method	How it works	Use when
worker (default)	hands I/O to io_workers processes (default 3)	everywhere; start here
io_uring	uses the Linux kernel’s io_uring directly	Linux, PostgreSQL built with --with-liburing / -Dliburing
sync	the old synchronous behaviour	comparisons, or if something goes wrong

io_method can only be set at server start, so changing it means a restart.

SHOW io_method;
SHOW io_workers;
SHOW effective_io_concurrency;   -- 18 raised the default to 16

# Was the installed package built with liburing? (packages built with configure)
pg_config --configure | grep -o with-liburing

While queries run, the new pg_aios view shows in-flight asynchronous I/O.

When it helps: AIO pays off when data really comes from storage and each request has latency, such as cloud network disks. If everything is already in shared_buffers there is nothing to speed up. And 18 does not cover every kind of read; plain index scans are not on the announced list.

Common mistakes

• Using uuidv7 as a secret token, for example in a password-reset link. Only 62 bits are random and the time is guessable. Use a long, fully random value for that
• Storing UUIDs as text. 36 characters is more than twice the 16 bytes of uuid, and comparisons are slower
• Setting io_method=io_uring on a build without liburing. Check first, and change it in a maintenance window since it needs a restart
• Expecting AIO to speed up every query. Compare EXPLAIN (ANALYZE, BUFFERS) before and after; queries served from memory will not change
• Forgetting the app also makes ids. The DB default is v7 but the ORM still generates v4 in the app, so the index stays random

uuidv7() is the single most valuable one-line change in 18 for tables keyed by UUID.

Sources

• PostgreSQL 18 Released! — postgresql.org
• UUID Functions (PostgreSQL 18) — postgresql.org
• Resource Consumption: io_method, io_workers — postgresql.org
• Release 18 notes — postgresql.org
• CREATE INDEX: fillfactor — postgresql.org
• Write Ahead Log: full_page_writes — postgresql.org
• RFC 9562: Universally Unique IDentifiers (UUIDs) — rfc-editor.org