PostgreSQL 18 Cheatsheet: Async I/O, uuidv7, Virtual Generated Columns

PostgreSQL 18 came out on September 25, 2025, and the first minor release, 18.1, followed on November 13. Enough time has passed for the early adopters to report back, and for the rest of us to plan the upgrade. That makes this a good moment for a compact reference.

The release has one headline feature you will not see in your SQL at all: a new asynchronous I/O subsystem. Around it sit several features you will use every day: uuidv7(), virtual generated columns, OLD and NEW in RETURNING, and constraints over time ranges. There are also a few default changes that matter more during an upgrade than any new feature.

This post covers what changed, what each setting means, the SQL you will actually write, and an upgrade checklist. It assumes you already know PostgreSQL; it is not an introduction.

1. What is new, at a glance

2. Asynchronous I/O

Before 18, a backend reading a table issued a read, waited, and issued the next one (with some prefetch hints via posix_fadvise on platforms that support it). With AIO, it can have many reads in flight at once. The gain is largest when storage latency is high: cloud block storage, network disks, cold caches.

The three io_method values:

io_method can only be set at server start, so it belongs in postgresql.conf:

# postgresql.conf
io_method = worker            # or io_uring on a liburing build
io_workers = 3                # raise on large machines with fast storage
effective_io_concurrency = 16
maintenance_io_concurrency = 16

Observe it rather than guess:

-- What the server is running with
SHOW io_method;
SHOW io_workers;

-- Async I/O handles in use right now (new view)
SELECT * FROM pg_aios;

-- I/O volume per backend type and context; byte columns are new in 18
SELECT backend_type, object, context, reads, read_bytes, writes, write_bytes
FROM pg_stat_io
WHERE reads > 0 OR writes > 0
ORDER BY read_bytes DESC NULLS LAST;

Practical notes:

• Writes are not async yet. AIO in 18 targets reads (sequential scans, bitmap heap scans, vacuum). Do not expect it to fix write-heavy workloads.
• "Up to 3x" is a ceiling, not a forecast. A dataset that fits in shared buffers or the OS page cache will see little difference. Measure on your own storage.
• io_uring is not automatically faster. It removes the worker hop, but container runtimes and some kernels restrict io_uring. The worker default exists for good reasons.
• More workers is not always better. If pg_aios regularly shows a queue and your storage has spare IOPS, raise io_workers. Otherwise leave it.

3. uuidv7(): time-ordered UUIDs

UUIDv4 values are random. As a primary key, every insert lands on a random page of the B-tree index, which hurts cache hit rates and causes page splits and more WAL. UUIDv7 puts a 48-bit Unix timestamp in milliseconds at the front, followed by more timestamp precision and random bits, so new values sort after old ones and inserts go to the right edge of the index, like a bigint sequence.

CREATE TABLE orders (
    id          uuid PRIMARY KEY DEFAULT uuidv7(),
    customer_id bigint NOT NULL,
    total_cents integer NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (customer_id, total_cents) VALUES (42, 1999) RETURNING id;

-- When was this id minted?
SELECT id, uuid_extract_timestamp(id) AS minted_at, uuid_extract_version(id) AS v
FROM orders
ORDER BY id DESC
LIMIT 5;

When to use what:

Two cautions:

• Creation time is visible. Anyone with the id can extract the timestamp. If ids appear in public URLs and creation time is sensitive (sign-up dates, internal volumes), use v4 for the public identifier.
• Do not use the id as your clock. Sort by created_at for business logic. The id is time-ordered, not a guaranteed source of truth for time, and values generated by application servers depend on their clocks.

You can also generate v7 values in the application: Python 3.14 (October 2025) added uuid.uuid7(), and most languages have a library. Generating in the database keeps a single clock.

4. Virtual generated columns

PostgreSQL 12 introduced stored generated columns. PostgreSQL 18 adds virtual ones and makes them the default.

CREATE TABLE line_items (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    unit_cents  integer NOT NULL,
    qty         integer NOT NULL,
    tax_rate    numeric(5,4) NOT NULL DEFAULT 0.21,
    -- virtual: computed when read, no storage, no write cost
    net_cents   integer GENERATED ALWAYS AS (unit_cents * qty),
    -- stored: computed on write, can be indexed
    gross_cents integer GENERATED ALWAYS AS (round(unit_cents * qty * (1 + tax_rate))) STORED
);

CREATE INDEX ON line_items (gross_cents);  -- fine, stored
-- CREATE INDEX ON line_items (net_cents); -- error, virtual

-- If you need an index on the virtual expression, index the expression itself
CREATE INDEX ON line_items ((unit_cents * qty));

Rules that apply to both kinds: the expression can only use immutable functions, cannot use subqueries, and cannot reference another generated column. Virtual columns add one more: the expression may only use built-in functions and types, not user-defined ones.

The upgrade trap: DDL that says GENERATED ALWAYS AS (...) without a keyword now creates a virtual column. Existing columns keep their kind across pg_upgrade or pg_dump, because dumps write STORED explicitly. But migration scripts, ORMs and schema tools that omit the keyword will create virtual columns in 18 and stored ones in 17. If a column must be indexed or replicated, write STORED explicitly. Write VIRTUAL explicitly too, so the intent is visible in code review.

5. OLD and NEW in RETURNING

Before 18, RETURNING gave new values for INSERT and UPDATE and old values for DELETE. Now you can ask for both, with the special aliases old and new.

-- What changed?
UPDATE products
SET price = price * 1.10
WHERE price <= 99.99
RETURNING name, old.price AS old_price, new.price AS new_price,
          new.price - old.price AS price_change;

-- Upsert: was this row inserted or updated?
INSERT INTO inventory (sku, qty) VALUES ('A-100', 5)
ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty
RETURNING sku, old.qty AS qty_before, new.qty AS qty_after,
          (old.sku IS NULL) AS was_inserted;

If old or new clashes with a table or column name, rename them with RETURNING WITH (OLD AS o, NEW AS n) ....

Typical uses: write audit rows from a CTE (WITH changed AS (UPDATE ... RETURNING old.*, new.*) INSERT INTO audit ...), emit change events from application code, and tell inserts from updates in an upsert without a second query.

6. Temporal constraints and skip scan

Temporal constraints enforce "no overlap" over a range column. The range column goes last in the key.

CREATE EXTENSION IF NOT EXISTS btree_gist;  -- needed for the scalar part of the key

CREATE TABLE room_bookings (
    room_id  integer NOT NULL,
    during   tstzrange NOT NULL,
    guest    text NOT NULL,
    PRIMARY KEY (room_id, during WITHOUT OVERLAPS)
);

INSERT INTO room_bookings VALUES (101, '[2026-02-01 14:00, 2026-02-03 11:00)', 'Ana');
INSERT INTO room_bookings VALUES (101, '[2026-02-02 14:00, 2026-02-04 11:00)', 'Ben');
-- fails: overlaps the existing booking for room 101

You could do this before with an EXCLUDE USING gist constraint. The difference is that it is now a real primary key or unique constraint, and foreign keys can reference it with PERIOD, checking that the referenced row covers the whole referencing range.

Skip scan lets the planner use a multicolumn B-tree index when the leading column has no equality condition, as long as later columns do. It works best when the leading column has few distinct values.

CREATE INDEX ON events (tenant_id, event_type, created_at);

-- Before 18: often a sequential scan, or a second index on (event_type, ...)
-- In 18: can skip through tenant_id values and use the index
EXPLAIN ANALYZE
SELECT count(*) FROM events WHERE event_type = 'refund' AND created_at > now() - interval '7 days';

Do not drop your other indexes on faith. Run EXPLAIN ANALYZE first; skip scan helps with a low-cardinality leading column and can lose to a dedicated index with a high-cardinality one. Since buffers are now in the output by default, compare shared hit and read counts, not only time.

7. Upgrade checklist: the changes that bite

The release notes list the incompatibilities. These are the ones most likely to matter:

pg_upgrade itself got better:

# Check first, in parallel, then upgrade by swapping data directories (new in 18)
pg_upgrade --old-bindir /usr/lib/postgresql/17/bin --new-bindir /usr/lib/postgresql/18/bin \
           --old-datadir /var/lib/postgresql/17/main --new-datadir /var/lib/postgresql/18/main \
           --jobs 4 --check
pg_upgrade ... --jobs 4 --swap

# Statistics are carried over by default; extended statistics are not, so still run:
vacuumdb --all --analyze-in-stages --missing-stats-only

--swap moves directories instead of copying or linking files, which is the fastest mode, but the old cluster is modified destructively and is no longer safe to start once the swap begins. Take a backup first.

Version status as of mid-January 2026: 18.1 is the current minor release, 17 is supported until November 2029, and 13 reached end of life in November 2025. If you are still on 13, you are already unsupported; 18 is a reasonable target.

Why this matters

• Performance without code changes. AIO speeds up scans and vacuum on slow storage with no query changes. Measure before and after with pg_stat_io.
• Better UUID keys are now built in. uuidv7() removes the main argument against UUID primary keys, without an extension.
• Defaults changed under your DDL. Virtual generated columns and default checksums are the two changes most likely to produce surprises in migrations and upgrades.
• Less application glue. OLD and NEW in RETURNING and temporal constraints move audit and booking logic into a single statement or constraint.
• Upgrades hurt less. Kept statistics, --swap and parallel checks shorten the downtime window, which removes one more reason to stay on an old major version.

Final Thoughts

PostgreSQL 18 is mostly an infrastructure release. The biggest change, AIO, is invisible in your SQL, and the most useful new functions are small. That is a good sign: the gains come from the engine, and the risks come from a handful of well-documented default changes.

Plan the upgrade around those defaults. Test with a copy of production, check generated columns and checksums, compare EXPLAIN ANALYZE output for your top queries, and keep io_method = sync in mind as a quick way to rule AIO in or out if something regresses.

Sources

• PostgreSQL Global Development Group, PostgreSQL 18 Released! (September 2025): release date, AIO "up to 3x" claim, feature overview, statistics carried over in upgrades, --swap, protocol 3.2.
• PostgreSQL, Release 18 notes (September 2025): incompatibilities, io_method, pg_aios, uuidv7(), virtual generated columns default, OLD/NEW in RETURNING, WITHOUT OVERLAPS and PERIOD, skip scan, BUFFERS in EXPLAIN ANALYZE, OAuth.
• PostgreSQL, Release 18.1 notes and announcement (November 2025): 18.1 release date, PostgreSQL 13 end of life.
• PostgreSQL, Resource Consumption settings: io_method, io_workers, effective_io_concurrency, io_combine_limit defaults.
• PostgreSQL, UUID Functions: uuidv7(), uuidv4(), uuid_extract_timestamp(), uuid_extract_version().
• PostgreSQL, Generated Columns: stored vs. virtual, restrictions, replication of generated columns.
• PostgreSQL, Returning Data from Modified Rows: old and new syntax and NULL behavior per command.
• PostgreSQL, Versioning Policy: support windows per major version.
• PostgreSQL source, indexcmds.c (REL_18_STABLE): indexes on virtual generated columns are rejected.
• IETF, RFC 9562: Universally Unique IDentifiers (UUIDs) (May 2024): UUID version 7 layout.
• Python, What's New in Python 3.14 (October 2025): uuid.uuid7().