PostgreSQL 18: Async I/O, uuidv7 and Skip Scan Explained

I have spent years tuning PostgreSQL for read-heavy ERP workloads, and the one wall I kept hitting was the same: on a big sequential scan or a vacuum, the backend would issue one disk read, block until it returned, then issue the next. On fast NVMe or cloud block storage that leaves most of the device idle, because a single process waiting on a single request cannot keep the queue full. PostgreSQL 18, which reached general availability on 25 September 2025, is the first release that changes that at the storage layer rather than around it.

This is not a cosmetic release. The headline is a genuine asynchronous I/O subsystem, but PostgreSQL 18 also ships several features I have wanted for years as an application developer: a native uuidv7() so I can stop reaching for extensions, generated columns that no longer waste disk, smarter multicolumn index use, and OAuth as a first-class authentication method. Let me walk through each one the way I evaluate it before putting it into production.

What actually changed in PostgreSQL 18

PostgreSQL major versions arrive yearly, but most bring incremental planner and syntax gains. PostgreSQL 18 is different because its flagship change is architectural: how the server talks to disk. For most of Postgres history, disk reads were synchronous — the executor asked the operating system for a block and waited. Version 18 introduces an asynchronous I/O layer that lets a backend queue several read requests at once, so the storage device stays busy instead of idle between requests. Everything else in this post rides alongside that, but the I/O change is the one that reshapes performance expectations.

Asynchronous I/O — the headline feature

The new AIO subsystem lets PostgreSQL issue multiple I/O requests concurrently instead of waiting for each one to finish in sequence. In practice that means sequential scans, bitmap heap scans, and vacuum can read ahead: while one block is in flight, the next requests are already queued. The official benchmarking reports performance gains of up to 3x in certain scenarios — and crucially, it also enables effective_io_concurrency and maintenance_io_concurrency above zero on systems that previously lacked fadvise support.

You control it with the io_method server variable. The default is worker, where dedicated worker processes execute the async I/O. On Linux you can switch to io_uring for kernel-native asynchronous I/O, provided the server was built with liburing support. There is also sync, which runs eligible I/O synchronously and effectively opts out. Two companion settings, io_combine_limit and io_max_combine_limit, control how adjacent block reads get merged into a single larger request, and the new pg_aios system view lets you watch in-flight operations.

# postgresql.conf — enable asynchronous I/O (PostgreSQL 18)
io_method = worker          # default; also: io_uring (Linux, --with-liburing) or sync
io_combine_limit = 128kB    # batch adjacent reads into one larger I/O
effective_io_concurrency = 16

-- Inspect in-flight asynchronous I/O
SELECT * FROM pg_aios;

Native uuidv7() for time-ordered keys

Random UUIDv4 primary keys are convenient but hostile to a B-tree: each insert lands at a random point in the index, scattering writes and wrecking cache locality on large tables. UUIDv7 fixes this by prefixing the value with a timestamp, so freshly generated keys sort in roughly insertion order — you get the uniqueness of a UUID with the index-friendliness of a sequence. Before version 18 you needed an extension or client-side code to produce one. PostgreSQL 18 makes uuidv7() a built-in function, and adds a uuidv4() alias so you can be explicit about which version you want.

-- Time-ordered primary keys, no extension required
CREATE TABLE orders (
  id          uuid PRIMARY KEY DEFAULT uuidv7(),
  customer_id bigint NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

SELECT uuidv7();   -- timestamp-ordered, temporally sortable
SELECT uuidv4();   -- explicit random UUID (new alias in PG18)

The value uuidv7() returns is temporally sortable, which is exactly what you want for a default primary key on high-insert tables: index pages fill left to right, page splits drop, and range scans over recently created rows stay tight. For distributed systems it also keeps the merge-friendliness of UUIDs without the write amplification that plagued UUIDv4 as a clustering key.

Virtual generated columns are now the default

PostgreSQL has had STORED generated columns since version 12 — computed on write and persisted to disk. Version 18 adds virtual generated columns that compute their value on read instead, storing nothing, and makes VIRTUAL the default when you write GENERATED ALWAYS AS without a keyword. For a derived value you rarely filter or index on, that is free: no extra bytes on disk, no write-time cost, and the expression is evaluated only when the column is actually selected.

CREATE TABLE invoices (
  id        uuid PRIMARY KEY DEFAULT uuidv7(),
  net       numeric(12,2) NOT NULL,
  tax_rate  numeric(4,3)  NOT NULL,
  -- VIRTUAL is now the default: computed on read, stored nowhere
  gross     numeric(12,2) GENERATED ALWAYS AS (net * (1 + tax_rate)) VIRTUAL,
  -- opt back into on-disk materialization when you need to index it
  gross_kept numeric(12,2) GENERATED ALWAYS AS (net * (1 + tax_rate)) STORED
);

B-tree skip scan

A classic multicolumn B-tree index on columns like (tenant_id, created_at) was useless to a query that filtered only on created_at, because Postgres could not seek without a value for the leading column. PostgreSQL 18 adds skip scan, which lets the planner use a multicolumn B-tree index even when there is no equality restriction on the first or an early column, as long as there is a useful restriction on a later one. It effectively iterates the distinct leading-column values and skips through the index for each — turning indexes that were previously ignored into usable ones and cutting down on full-table scans.

OAuth authentication support

PostgreSQL 18 adds an oauth authentication method to pg_hba.conf, letting clients authenticate with OAuth 2.0 flows validated through extension libraries. It brings a server variable oauth_validator_libraries to load token validation modules, new libpq OAuth options on the client, and a --with-libcurl build flag for the required libraries. For teams already standardized on an identity provider, this means database access can finally hang off the same token infrastructure as the rest of the stack instead of a separate password realm.

PostgreSQL 17 versus PostgreSQL 18 at a glance

My upgrade plan is deliberately boring: test on a replica first, leave io_method on the worker default and measure real query timings before touching it, and audit every generated column so nothing silently switches from stored to virtual. Then I reach for uuidv7() on new high-insert tables and let skip scan quietly rescue queries the planner used to ignore. PostgreSQL 18 is the rare release where the biggest win — asynchronous I/O — costs nothing to adopt beyond the upgrade itself.