A release-notes page is two documents wearing one URL. The Overview at the top is a sales document. It is there to make you want the upgrade. Further down, past the feature entries, sits a section called Migration to Version 18, and that one is an incident report written ahead of time. It lists the things that will behave differently on your cluster after the upgrade finishes, whether or not you read about them first.
The engineer’s move is to read the second document before the first. Postgres 18 was released on 2025-09-25, and moving data from any earlier release requires a dump and restore through pg_dumpall, or pg_upgrade, or logical replication. That one sentence tells you a migration is happening no matter what, so the only real question is which behavior changes ride along with it. What follows reads the PG18 notes in that order, risk before headlines. Every SQL snippet below was run on PostgreSQL 18.4, so the output is real.
The headline features
The Overview leads with a tidy list of eight headline items, and most of them split cleanly into two buckets.
Some change how you write SQL. Skip scan is the one to read first. Before 18, a
multi-column B-tree index needed an equality match on its leading column to earn
its keep. Now the planner reaches for that index even when the leading column is
unrestricted, provided a later column has a selective restriction. A whole category
of “add another single-column index” tickets goes away, though you should still
confirm the planner picks it in your own plans. uuidv7()
generates a time-sortable UUID, and a uuidv4() alias was added so you can be
explicit about which version you mean. If you have ever watched random UUIDs shred
your index locality, this is the entry to read twice. On 18.4, consecutive
uuidv7() calls come back in generation order, which is the property that keeps
recent rows near each other in the index. Virtual generated columns now compute on
read instead of on write, and virtual is the default when you omit STORED.
RETURNING can finally hand you both the old and the new row through aliases you
can rename. On 18.4 the change is concrete:
-- PostgreSQL server 18.4, psql 18
CREATE TABLE account (id int primary key, balance numeric);
INSERT INTO account VALUES (1, 200);
UPDATE account SET balance = balance - 50 WHERE id = 1
RETURNING old.balance AS before, new.balance AS after;
-- before | after
-- --------+-------
-- 200 | 150One round trip shows what a row was and what it became, with no second SELECT.
Temporal constraints round out the SQL side: a primary key or unique constraint
can carry WITHOUT OVERLAPS, with PERIOD doing the same job for foreign keys.
Others change how the engine runs. OAuth arrives as a real pg_hba.conf
authentication method backed by pluggable token-validation libraries. And the
asynchronous I/O subsystem, the first bullet in the Overview, is the one worth
stopping on.
Asynchronous I/O is on by default
The release-notes entry for asynchronous I/O says it lets backends queue multiple
read requests for more efficient sequential scans, bitmap heap scans, vacuums, and
similar bulk-read work, controlled by a server variable named io_method. Read
only that entry and you might file AIO under “opt-in tuning for later.”
Now click through to the runtime configuration page. The default for io_method
is worker, and it can only be set at server start. Worker mode runs a small pool
of dedicated I/O processes, io_workers, defaulting to 3. So AIO is not a flag you
flip. It is on the moment you boot 18, and your process list has three new backends
in it that were not there on 17.
This is the whole point of reading like an engineer. The same feature shows up in
both documents. In the Overview it reads as a performance headline. In practice it
is an operational change on day one: new processes to account for, plus a new
pg_aios system view that shows the file handles currently in flight for
asynchronous I/O. Per-backend I/O accounting arrives with it, reachable through
pg_stat_get_backend_io() and resettable with pg_stat_reset_backend_stats().
Good news for your dashboards, if your dashboards know to look. You can confirm the
default yourself on a fresh 18 server:
-- PostgreSQL server 18.4, psql 18
SHOW io_method; -- worker
SHOW io_workers; -- 3Migration incompatibilities
Here is the Migration section, reordered by how likely each item is to page you.
Data checksums are now on by default. initdb enables them unless you pass
--no-data-checksums, and pg_upgrade refuses to run unless the old and new
clusters match on this setting. If your existing cluster was built without
checksums, which was the default for its whole life, pg_upgrade will stop until you
tell the new cluster to also go without. This is a five-second fix once you know it
and a confusing wall if you do not.
VACUUM and ANALYZE now recurse into inheritance children. By default both
commands process the inheritance children of a parent table, and the old behavior
is only available through a new ONLY keyword. If you run targeted maintenance
against parent tables in cron, those jobs now do more work than they used to, with
no error to tip you off. Add ONLY where you meant the old scope.
COPY FROM stops treating \. as end of file in CSV. A CSV file that used \.
as ordinary data used to break; now it is read as data, \. must appear alone on a
line to act as a marker, and older psql clients talking to an 18 server can hit
\copy problems. Ingestion pipelines that were silently truncating at a stray \.
will change shape here.
Full text search changed its collation provider. FTS now reads its
configuration files and dictionaries using the cluster’s default collation provider
instead of always using libc. On clusters whose default is ICU or the builtin
provider, some FTS functions and the pg_trgm extension can behave differently, and
the notes recommend reindexing every FTS and pg_trgm index after a pg_upgrade. A
recommended REINDEX buried in a release-notes paragraph is exactly the kind of thing
that turns into a corruption ticket three weeks later.
Some collation mismatches now fail the restore itself. Postgres 18 now requires that a primary and foreign key relationship use one deterministic collation, or the same nondeterministic collation on both sides. A schema that breaks the rule causes the pg_dump restore under pg_upgrade to fail before the upgrade finishes. Catch it in a staging rehearsal, well ahead of the real maintenance window.
Your monitoring will go quiet before it errors. pg_stat_wal lost its
wal_write, wal_sync, wal_write_time, and wal_sync_time columns. pg_stat_io
replaced the fixed op_bytes column with byte-level counters: read_bytes and
write_bytes for reads and writes, plus extend_bytes for relation extension. Any
dashboard or alert querying the old columns breaks, and a broken metric usually
shows up as a flat line that looks calm rather than as a loud failure. A quieter
observability change points the other way: EXPLAIN ANALYZE now includes BUFFERS
output automatically. On 18.4, EXPLAIN ANALYZE SELECT * FROM account; prints
Buffers: shared hit=... lines with no BUFFERS keyword supplied. Saved plans
from older versions will now carry buffer numbers, which helps anyone chasing I/O
and can surprise tooling that parses plan text.
A few more belong on the checklist without needing a paragraph each. MD5 passwords
are deprecated and will be removed in a future major release, so CREATE ROLE and
ALTER ROLE now emit warnings you can silence with md5_password_warnings = off.
Unlogged partitioned tables are no longer allowed at all. AFTER triggers now run as
the role that was active when the trigger event was queued rather than the role
active at execution time, which can shift what a trigger is allowed to do at COMMIT.
The default configuration also drifted: effective_io_concurrency and
maintenance_io_concurrency both rose to 16 to match modern hardware, and the
default CREATE SUBSCRIPTION streaming mode moved from off to parallel.
pg_upgrade: preserved statistics and --swap
Buried in the good news is real operational relief. pg_upgrade now preserves
optimizer statistics, so you no longer face a cluster that plans like it has amnesia
until ANALYZE finishes; extended statistics are the one exception, and
--no-statistics opts out. A new --swap mode swaps data directories instead of
copying or hard-linking files, which the notes call potentially the fastest option.
For a large cluster with a tight window, those two together can be the difference
between a rehearsed cutover and a nervous one.
A runbook checklist
Here is a checklist to paste into a runbook, each line tied to the release notes it came from.
- Match checksum settings before pg_upgrade, or pass
--no-data-checksums. - Add
ONLYto targeted VACUUM and ANALYZE jobs that meant the parent only. - Audit CSV ingestion for
\.and for old psql clients. - On ICU or builtin clusters, plan a post-upgrade REINDEX of FTS and pg_trgm indexes.
- Rehearse the restore to catch PK/FK collation failures before the window.
- Update dashboards for the pg_stat_wal and pg_stat_io column changes.
None of that comes from the Overview. All of it comes from reading the Migration
section as though it were an outage you get to prevent instead of explain. The
headline features deserve your attention too, and uuidv7() and skip scan will
both earn their place in real code. But the version you actually run is defined by
the changes nobody put in the announcement.
Written by Context Systems. This piece was researched from the project’s public docs, release notes, and source. The way we write everything.