What's New in PostgreSQL 18: Asynchronous I/O, Skip Scan, uuidv7, Virtual Generated Columns, RETURNING OLD/NEW and Temporal Constraints

Version note: The examples in this post were run on PostgreSQL 18.7 (the official postgres:18 Docker image), in the Bookstore lab we set up in post 02, right after reloading the schema; the "how on 17" comparisons ran on PostgreSQL 17.12 (postgres:17-alpine) with the same schema loaded.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, wants to show the amount including VAT on the invoice screen, and Emre adds it as a generated column to the one-million-row orders table. We run the same statement on 17 and 18, then add an index on the column in 18:

ALTER TABLE orders ADD COLUMN total_with_vat numeric(12,2)
    GENERATED ALWAYS AS (round(total_amount * 1.20, 2));
CREATE INDEX ix_orders_total_with_vat ON orders (total_with_vat);

Does the statement rewrite the table on 17, and how many seconds does it take? What does the same line do on 18, and is the index created?

/* 17.12 */
ERROR:  syntax error at or near ";"
/* 18.7 */
ALTER TABLE
Time: 3.946 ms
ERROR:  indexes on virtual generated columns are not supported

On 17 a generated column cannot be defined without STORED; a syntax error. On 18 the same line finished in 4 milliseconds, because virtual is now the default: the value is not written to disk but computed when read. The price is in the third line: a virtual column cannot be indexed. In this post we look at the new features of PostgreSQL 18, the base version of this series, all together. We used some of them in earlier posts; here each one gets a "how did we do this in 17" comparison and a small experiment on Bookstore.

Asynchronous I/O: io_method

Up to 18, each backend sent a request to the operating system to read a page and waited for the answer; read-ahead was left to the operating system's guesswork. The AIO infrastructure in 18 lets operations such as sequential scans, bitmap heap scans and VACUUM issue several read requests ahead of time. The method is chosen with io_method, and a restart applies a change:

           name           | setting |        enumvals
--------------------------+---------+------------------------
 effective_io_concurrency | 16      |
 io_combine_limit         | 16      |
 io_max_concurrency       | 64      |
 io_method                | worker  | {sync,worker,io_uring}
 io_workers               | 3       |

The default is worker: three background processes do the reads. sync is the behavior of 17. io_uring uses the Linux kernel's asynchronous I/O interface, but it did not start in a Docker container with the default security settings: FATAL: could not setup io_uring queue: Operation not permitted. We read the 124 MB order_items table with parallel query off, emptying shared buffers each time (pg_buffercache_evict_relation, new in 18):

17.12                    shared read=15927  67-69 ms
18.7 io_method=sync      shared read=15927  71-75 ms
18.7 io_method=worker    shared read=15927  61-62 ms

Since 17 does not have that function, we emptied shared buffers there by restarting the container. On 18, worker gains about fifteen percent over sync; the files sit in the operating system's page cache, so every read is already very fast. AIO makes its real difference where every read goes to disk and waits milliseconds, for example on cloud disks; we could not measure that in the lab. In practice, keep the worker default; on a system with heavy I/O you can raise io_workers and watch in-flight requests in the pg_aios view.

Skip Scan and Changes in EXPLAIN

The skip scan we saw in post 06 lets a multicolumn B-tree index be used even when the leading column is missing from the query: on the (status, created_at) index a search by created_at alone read 26 buffers on 18, versus a Seq Scan with 10,328 on 17.11. On 17 you needed either a second index (created_at on its own) or to add every value to the query with status = ANY(...). It helps when the leading column has few distinct values; the Index Searches line in EXPLAIN shows how many times the index was entered.

EXPLAIN itself changed too. The same query on both versions:

/* 17.12: EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) */
 Aggregate (actual rows=1 loops=1)
   ->  Seq Scan on books (actual rows=2000 loops=1)
/* 18.7: the same command */
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared read=444 dirtied=2
   ->  Seq Scan on books (actual rows=2000.00 loops=1)
         Buffers: shared read=444 dirtied=2

On 18 BUFFERS comes with ANALYZE automatically (we used this in post 07), and row counts are fractional: when the average over loops is something like 0.5, it is no longer rounded to 0. When reading old plans from 17, remember that you had to ask for BUFFERS yourself.

uuidv7(): Time-Ordered UUIDs

In post 03 we compared the index density of v4 and v7 (71–72 percent versus 90 percent). 18 generates them in core:

SELECT uuidv7() AS id, uuid_extract_timestamp(uuidv7()) AS ts, uuid_extract_version(uuidv7()) AS v;

                  id                  |             ts             | v
--------------------------------------+----------------------------+---
 01a1d815-4d12-7614-9798-5df9bc9322fc | 2026-11-14 03:23:57.074+00 | 7

On 17 you get function uuidv7() does not exist. On that version the application generated v7: .NET has had Guid.CreateVersion7() since .NET 9, and as we saw in post 12 the Npgsql EF Core provider already generates v7 on the client for primary keys. Generating it on the server has the advantage that the same rule applies whichever client writes the row: DEFAULT uuidv7(), or HasDefaultValueSql("uuidv7()") in EF Core. One warning: the first 48 bits of a v7 are its creation time, so anyone who sees the ID also sees when the record was created.

Virtual Generated Columns

The plan for the column from the guess shows that the value is computed on read:

 Index Scan using orders_pkey on public.orders
   Output: round((total_amount * 1.20), 2)
   Index Cond: (orders.order_id = 1)

On 17 the only option was STORED. Adding the same column with STORED rewrote the table on both versions: 739 ms on 17 and 803 ms on 18; on 17 the table grew from 81 MB to 89 MB, and it was under an ACCESS EXCLUSIVE lock the whole time. A virtual column only changes the catalog. Which one to choose depends on how the column is used: STORED for a value that is read often, expensive to compute or needs an index (that is why search_vector in post 10 is stored), virtual for a simple derivation that is read rarely. In hand-written migration scripts, write STORED explicitly; without it, 18 creates a virtual column.

Virtual columns have one more restriction: the expression may only use built-in functions and types; a function you wrote, an extension function (such as unaccent) or a user-defined type such as an enum cannot be used. The search_vector from post 10, which uses the turkish_unaccent configuration, could not have been virtual for this reason as well. Stored columns have no such restriction.

RETURNING OLD/NEW

Getting both the old and the new value in a single statement to log a price change:

UPDATE books SET price = round(price * 1.05, 2)
WHERE book_id IN (1, 2, 3)
RETURNING book_id, old.price AS old_price, new.price AS new_price;

 book_id | old_price | new_price
---------+-----------+-----------
       1 |     32.77 |     34.41
       2 |     39.00 |     40.95
       3 |     35.69 |     37.47

On 17 this line gives missing FROM-clause entry for table "old". The equivalent there is a CTE that locks and reads the old values with FOR UPDATE:

WITH before AS (
    SELECT book_id, price FROM books WHERE book_id IN (1, 2, 3) FOR UPDATE
)
UPDATE books b SET price = round(b.price * 1.05, 2)
FROM before
WHERE b.book_id = before.book_id
RETURNING b.book_id, before.price AS old_price, b.price AS new_price;

The result is the same, but it reads the rows twice, and if you forget FOR UPDATE, a concurrent update makes it return the wrong old value. OLD/NEW also works with INSERT ... ON CONFLICT DO UPDATE: in the upsert from post 04 you can tell whether the row was inserted or updated with old.book_id IS NULL.

Temporal Constraints: WITHOUT OVERLAPS and PERIOD

In post 04 we defined book price periods with PRIMARY KEY (book_id, valid_during WITHOUT OVERLAPS) and tied campaigns to those periods with FOREIGN KEY ... PERIOD. On 17 the same table gives syntax error at or near "WITHOUT". The way to do it there was an EXCLUDE USING gist (book_id WITH =, valid_during WITH &&) constraint with the btree_gist extension (post 13); it prevents overlaps the same way, but it is not a primary key, so a foreign key cannot reference it. A temporal foreign key has no direct equivalent on 17; it was written with triggers. Even on 18 temporal keys need btree_gist, because the = comparison on book_id happens in a GiST index.

Operations: Checksums, Statistics, OAuth, md5 and Logical Replication

Data checksums: 18's initdb turns them on by default; in the lab SHOW data_checksums is on, on 17 from the same image family it is off. Corruption of a page on disk is detected when it is read. As we saw in post 14, coming from 17 with pg_upgrade requires creating the new cluster with --no-data-checksums. The other change from the same post: pg_upgrade carries planner statistics over, except extended statistics.

OAuth: 18 added the oauth method to pg_hba.conf; the user connects with a token obtained from an identity provider. The server does not validate the token itself; that needs a validator module specific to the identity provider (oauth_validator_libraries), and PostgreSQL does not ship one. On the client side libpq supports it; in Npgsql it is an open issue on GitHub as of the day this post was written. So it is not usable for a .NET application today; the token-based logins of managed services (such as Microsoft Entra ID) work through Npgsql's periodic password provider.

md5: as we saw in post 14, it is deprecated in 18, and assigning an md5 password raises a warning. On the text side we covered casefold() and the PG_UNICODE_FAST builtin collation in post 03; for case-insensitive comparison they give more correct results than lower(). We saw the autovacuum changes (autovacuum_vacuum_max_threshold, eager freezing) in post 08.

Logical replication has two changes: the values of stored generated columns can now be replicated (publish_generated_columns = stored), which matters especially for non-PostgreSQL targets that cannot compute the value themselves. The default streaming option of CREATE SUBSCRIPTION also changed from off to parallel: large transactions are streamed before commit and applied in parallel. The details are in the replication post of the Veritabanı Performansı ve Scaling series.

Production Notes

After upgrading to 18, check the GENERATED ALWAYS AS (...) lines in scripts that change the schema: a new column without STORED is now virtual, cannot be indexed and is computed on every read.

Do not change io_method without measuring. The worker default is good for most systems; io_uring needs both the kernel and the container security profile to allow it, and if it cannot start, neither does the server.

A short checklist after the upgrade: is SHOW data_checksums what you expect, have the extended statistics in pg_stats_ext been collected again, is any role left with an md5 password, and is the md5 method still used in pg_hba.conf? The queries from post 14 check these in a few minutes.

Try the new version's defaults with your monitoring tools too: fractional row counts and the new BUFFERS lines in EXPLAIN output can affect tools that parse plans.

Common Pitfalls

Thinking a virtual generated column is "free": as in the guess, it cannot be indexed and is computed on every read; if it is used often in WHERE, either make it STORED or define an expression index with the same expression.

Treating uuidv7 as an opaque identifier: it carries the creation time. For IDs exposed to the outside that must not leak timing, use v4.

Finding out about the checksum difference at pg_upgrade time when upgrading from 17: decide in advance how you will create the new cluster, so you do not hit a --check failure in the maintenance window.

A Small Experiment

★☆☆ Using the ISBN, try INSERT ... ON CONFLICT (isbn) DO UPDATE ... RETURNING old.price, new.price, (old.book_id IS NULL) AS inserted with both a new and an existing number. What did inserted return?

★★☆ Add total_with_vat as a virtual column and look at the plan of WHERE total_with_vat > 500. Create an expression index with the same expression and check whether the plan changes.

★★★ Compare the read rates in the VACUUM (VERBOSE) output on order_items with io_method set to sync and to worker. Interpret the result together with the effect of the page cache.

The next post is the last one in the series: REPACK (CONCURRENTLY), sequence replication, the WAIT command on a standby, ON CONFLICT DO SELECT and the behavior changes, on PostgreSQL 19's release candidate.