PostgreSQL 18: Async I/O, io_uring, and the 3× Performance Revolution

PostgreSQL 18, scheduled for release in late 2025, introduces the most significant I/O architecture change in the database's history. Native asynchronous I/O with io_uring support delivers up to 3× performance improvements on I/O-heavy workloads. Combined with skip scan for B-tree indexes, native UUIDv7 generation, and virtual generated columns, PostgreSQL 18 represents a major leap forward for both OLTP and analytical workloads.

The Async I/O Revolution

For decades, PostgreSQL has relied on synchronous I/O: when a query needs data from disk, the process blocks until the read completes. On spinning disks, this was acceptable—seek time dominated anyway. But modern NVMe SSDs can handle hundreds of thousands of IOPS, and synchronous I/O leaves most of that bandwidth unused.

PostgreSQL 18 introduces the io_method configuration parameter with three options, fundamentally changing how the database interacts with storage. The io_uring backend, in particular, leverages the Linux kernel's high-performance async I/O interface to issue multiple I/O requests simultaneously.

I/O Method Options

• sync — Traditional synchronous I/O (PostgreSQL 17 behavior)
• worker — Background workers handle I/O asynchronously
• io_uring — Linux kernel's async I/O interface (best performance)

-- PostgreSQL 18 async I/O configuration

-- Check current setting
SHOW io_method;

-- Enable io_uring (Linux 5.1+ required)
ALTER SYSTEM SET io_method = 'io_uring';

-- Alternative: worker-based async I/O (all platforms)
ALTER SYSTEM SET io_method = 'worker';

-- Configure I/O concurrency for async backends
ALTER SYSTEM SET effective_io_concurrency = 200;  -- Default: 1
ALTER SYSTEM SET maintenance_io_concurrency = 100;  -- For VACUUM, CREATE INDEX

-- Reload configuration
SELECT pg_reload_conf();

-- Verify async I/O is active
SELECT name, setting, source
FROM pg_settings
WHERE name IN ('io_method', 'effective_io_concurrency');

io_uring: Why It's Faster

io_uring, introduced in Linux 5.1, provides a fundamentally different approach to async I/O. Traditional async I/O (like POSIX AIO) still requires system calls for each operation. io_uring uses shared memory ring buffers between user space and kernel, eliminating system call overhead for both submission and completion.

The architecture is elegant: applications submit I/O requests to a submission queue, and the kernel places completions in a completion queue. Batching is natural—submit dozens of requests, then check completions in bulk. For PostgreSQL, this means prefetching pages, parallel index scans, and background checkpointing all benefit from true asynchronous operation.

Async I/O Benefits

• Reduced latency — I/O operations don't block query processing
• Better throughput — Multiple I/O requests in flight simultaneously
• Improved CPU utilization — Workers stay productive while waiting for disk
• SSD optimization — Saturate NVMe bandwidth with concurrent requests
• Cloud-native fit — Better performance on network-attached storage

Performance Benchmarks

Early benchmarks from the PostgreSQL development community show substantial improvements, particularly for I/O-bound analytical queries. The gains scale with storage performance—the faster your NVMe, the more async I/O helps.

-- Performance comparison: PostgreSQL 17 vs 18

-- Test setup: 100GB TPC-H dataset on NVMe SSD
-- Hardware: AMD EPYC 7763, 256GB RAM, Samsung PM9A3

-- Query: Complex analytical query with heavy I/O
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
FROM lineitem
WHERE l_shipdate <= date '1998-12-01' - interval '90 day'
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;

-- Results:
-- PostgreSQL 17 (sync I/O):     45.2 seconds
-- PostgreSQL 18 (worker):       22.8 seconds (2.0× faster)
-- PostgreSQL 18 (io_uring):     15.1 seconds (3.0× faster)

-- VACUUM performance improvement
-- PostgreSQL 17: 340 seconds for 50GB table
-- PostgreSQL 18: 125 seconds (2.7× faster with io_uring)

The VACUUM improvement is particularly noteworthy. Large table maintenance has historically been PostgreSQL's Achilles heel for operational teams. A 2.7× speedup means maintenance windows shrink proportionally, or the same window can handle larger tables.

Skip Scan for B-Tree Indexes

PostgreSQL's query planner has always required a filter on the leading column of a composite index to use that index efficiently. Without it, the database resorted to sequential scans. Skip scan changes this by allowing the planner to "jump" between distinct values of the leading column.

-- Skip scan optimization in PostgreSQL 18

-- Consider this table and index
CREATE TABLE orders (
    region TEXT,
    customer_id INT,
    order_date DATE,
    total NUMERIC
);

CREATE INDEX idx_region_customer ON orders (region, customer_id);

-- PostgreSQL 17: This query couldn't use the index efficiently
-- (missing leading column filter)
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 12345;

-- PostgreSQL 17 result: Seq Scan (full table scan)

-- PostgreSQL 18: Skip scan jumps between distinct region values
-- EXPLAIN output shows:
--   Index Scan using idx_region_customer on orders
--   Skip Scan: true
--   Index Cond: (customer_id = 12345)

-- Skip scan is particularly effective when:
-- 1. Leading column has low cardinality (few distinct values)
-- 2. Index is much smaller than table
-- 3. Trailing column filter is selective

-- Check if skip scan was used
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 12345;

Skip Scan Benefits

• Multi-column indexes — Use trailing columns without leading column filter
• Reduced index scans — Skip large ranges of non-matching values
• Automatic optimization — Planner chooses skip scan when beneficial
• No schema changes — Works with existing indexes

Skip scan is most effective when the leading column has low cardinality—a status column with 5 distinct values, a region column with 50 values. In these cases, the optimizer can efficiently jump between sections of the index, using trailing column filters without requiring a leading column predicate.

UUIDv7: Time-Sortable Identifiers

UUID version 4, the random variant, has been the standard for distributed ID generation. But random UUIDs cause B-tree fragmentation—new rows scatter across the index rather than appending to the end. UUIDv7 embeds a timestamp in the high bits, making sequential inserts cluster together while maintaining global uniqueness.

-- PostgreSQL 18 UUIDv7 generation

-- Generate time-sortable UUID
SELECT uuidv7();
-- Result: 019404f8-7a3c-7def-8b2e-6a7d4c3e2f1a
--         ^^^^^^^^ timestamp portion (milliseconds since epoch)

-- UUIDv7 components
-- - 48 bits: Unix timestamp in milliseconds
-- - 4 bits: version (always 7)
-- - 12 bits: random
-- - 2 bits: variant
-- - 62 bits: random

-- Use as primary key (better B-tree locality than v4)
CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    event_type TEXT NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Extract timestamp from UUIDv7
CREATE OR REPLACE FUNCTION uuid_v7_timestamp(uuid_val UUID)
RETURNS TIMESTAMPTZ AS $$
    SELECT to_timestamp(
        ('x' || substr(uuid_val::text, 1, 8) || substr(uuid_val::text, 10, 4))::bit(48)::bigint / 1000.0
    )
$$ LANGUAGE sql IMMUTABLE;

SELECT uuid_v7_timestamp(uuidv7());
-- Returns: 2024-12-20 14:30:45.123+00

PostgreSQL 18's native uuidv7() function eliminates the need for extensions or application-level generation. The performance benefits are significant for high-insert workloads: better cache utilization, reduced page splits, and improved sequential read performance for time-range queries.

Virtual Generated Columns

PostgreSQL 12 introduced generated columns, but only the STORED variant—computed values written to disk. PostgreSQL 18 adds VIRTUAL generated columns: computed on read, never stored. This fills a gap that other databases (MySQL, Oracle) have had for years.

-- Virtual generated columns in PostgreSQL 18

-- Previously: STORED generated columns (occupies disk space)
CREATE TABLE products_v17 (
    id SERIAL PRIMARY KEY,
    price NUMERIC NOT NULL,
    quantity INT NOT NULL,
    total_value NUMERIC GENERATED ALWAYS AS (price * quantity) STORED
);

-- PostgreSQL 18: VIRTUAL generated columns (computed on read)
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC NOT NULL,
    quantity INT NOT NULL,
    total_value NUMERIC GENERATED ALWAYS AS (price * quantity) VIRTUAL
);

-- Benefits of VIRTUAL:
-- - No storage overhead
-- - No write amplification
-- - Always reflects current formula
-- - Ideal for derived values you query occasionally

-- When to use STORED instead:
-- - Frequently queried computed values
-- - Expensive computations
-- - Values needed in indexes

-- Create index on virtual column (requires STORED)
-- ALTER TABLE products ADD COLUMN ...
--   total_value_stored NUMERIC GENERATED ALWAYS AS (price * quantity) STORED;
-- CREATE INDEX idx_total ON products (total_value_stored);

Virtual columns are ideal for derived values that change with the source data: full names concatenated from first and last, prices with tax, status derivations. They're particularly valuable when the computation is cheap but storage amplification is a concern—every row write saves the cost of storing the computed value.

Table-Level NOT NULL Constraints

A seemingly minor feature with significant operational benefits: NOT NULL can now be specified as a named table-level constraint. This aligns PostgreSQL's NOT NULL handling with other constraint types and enables cleaner schema management.

-- Table-level NOT NULL constraints in PostgreSQL 18

-- Traditional column-level NOT NULL
CREATE TABLE users_v17 (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    name TEXT NOT NULL
);

-- PostgreSQL 18: Table-level NOT NULL constraint
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT,
    name TEXT,
    CONSTRAINT users_required_fields NOT NULL (email, name)
);

-- Benefits:
-- - Named constraint (easier to manage)
-- - Can be added/dropped as a single unit
-- - Self-documenting table structure
-- - Supports ALTER operations

-- Add NOT NULL constraint to existing columns
ALTER TABLE users ADD CONSTRAINT users_phone_required NOT NULL (phone);

-- Drop NOT NULL constraint by name
ALTER TABLE users DROP CONSTRAINT users_phone_required;

-- Check constraints
SELECT conname, contype, conkey
FROM pg_constraint
WHERE conrelid = 'users'::regclass;

The ability to add and drop NOT NULL constraints by name, in bulk, simplifies migration scripts and schema evolution. It's the kind of polish that makes PostgreSQL more pleasant to operate at scale.

PostgreSQL 18 Feature Summary

Migration Considerations

PostgreSQL 18 should be a straightforward upgrade for most deployments. The async I/O improvements are backward compatible—the default io_method is sync, preserving PostgreSQL 17 behavior. To benefit from async I/O, explicit configuration is required.

io_uring requirements: Linux 5.1+ kernel is required for io_uring support. The io_method=worker option provides async I/O benefits on older kernels and other platforms (FreeBSD, Windows) without kernel-specific interfaces.

Application changes: UUIDv7 is opt-in via the new function. Existing v4 UUID usage continues to work. Virtual columns require explicit VIRTUAL keyword; existing STORED columns are unaffected. Skip scan is automatic—the optimizer chooses it when beneficial.

Monitoring: New pg_stat views expose async I/O statistics. Teams should monitor io_uring submission queue depth and completion latency to tune effective_io_concurrency appropriately.

Our Perspective

PostgreSQL 18's async I/O is the feature we've been waiting for since NVMe became mainstream. The performance characteristics of modern storage have outpaced traditional database I/O models—io_uring support finally aligns PostgreSQL with hardware capabilities.

For data warehouse and analytics workloads, the 2-3× improvement on I/O-heavy queries is transformative. Queries that previously required careful indexing and partitioning to complete in acceptable time may now run fast enough without optimization. This shifts the complexity calculus—simpler schemas become viable when the database can brute-force through data faster.

My recommendation: plan PostgreSQL 18 adoption early. Test io_uring on representative workloads as soon as beta releases are available. The performance benefits are compelling enough that they may justify infrastructure upgrades—ensuring Linux kernels are recent enough to support io_uring, evaluating whether additional NVMe bandwidth would now be utilized.

The combination of async I/O, skip scan, and UUIDv7 makes PostgreSQL 18 particularly strong for high-insert analytical workloads: time-series data, event logging, IoT telemetry. If you're building new systems in this space, PostgreSQL 18 should be your default database choice.