PostgreSQL 18: What Developers Need to Know Before Upgrading

PostgreSQL 18 is not a routine .0 release. The async I/O rewrite—five years in development, led by Andres Freund—fundamentally changes how Postgres reads and writes data. Add native incremental backup, virtual generated columns, and a fully completed SQL/JSON implementation, and you have the most significant Postgres upgrade in at least a decade. Here is what matters, what breaks, and when to actually pull the trigger.

Async I/O: The Architectural Shift You Actually Care About

PostgreSQL has always used synchronous, blocking I/O. A backend process requests a page from disk and waits. Everything stalls. For transactional OLTP workloads on modern NVMe drives, this was leaving enormous throughput on the table.

PG18 fixes this with a new io_method configuration parameter. The options are:

• sync — the old behavior, kept for compatibility
• worker — the new default on Linux; background workers handle I/O, letting the backend continue processing
• io_uring — opt-in, requires Linux kernel 5.1+, uses the kernel’s io_uring interface for maximum throughput

Real-world numbers from early benchmarks: sequential table scans that took 4 seconds now complete in roughly 1.2 seconds on NVMe storage. That is a 3x improvement without changing a single line of application code. Read-heavy analytics workloads and large reporting queries benefit most immediately. For teams using pgvector for approximate nearest neighbor searches on large embedding tables, the improvement compounds significantly.

One warning: async I/O may expose I/O bottlenecks previously hidden by the synchronous model. If your system has an undersized storage controller, you will find out quickly. Treat this as useful information, not a regression.

Incremental Backup: Finally

If you run a database larger than 50GB, you already know the backup problem. Full backups take hours, consume storage, and create anxiety. PG18 solves this with native incremental backup support in pg_basebackup.

The setup requires one new component: pg_wal_summarizer, a background process that tracks which data blocks have changed since the last backup. Once running, the official pg_basebackup documentation outlines the workflow:

# Take an initial full backup
pg_basebackup -D /backups/full-20260601 --checkpoint=fast

# Take incremental backups thereafter
pg_basebackup -D /backups/incr-20260608 \
  --incremental=/backups/full-20260601/backup_manifest

# Restore by combining full + incrementals
pg_combinebackup /backups/full-20260601 /backups/incr-20260608 \
  -o /backups/restored

For databases in the 500GB–5TB range, this can reduce backup storage by 70–90% depending on your write volume. Backup windows shrink proportionally. The only prerequisite is wal_level = replica, which is already the default in any modern Postgres deployment.

Virtual Generated Columns

PostgreSQL 17 introduced stored generated columns—computed values persisted to disk. PG18 adds the virtual variant: computed at query time, zero storage cost.

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  unit_price  numeric(10, 2),
  quantity    integer,
  total       numeric(10, 2) GENERATED ALWAYS AS (unit_price * quantity) VIRTUAL
);

SELECT id, total FROM orders WHERE total > 500;

Virtual generated columns work in SELECT, WHERE, and ORDER BY clauses. They cannot be directly indexed—use expression indexes if you need that. The best use case is computed display fields and transformations you want enforced at the schema level without paying the storage overhead of a stored column.

JSON_TABLE and the End of json_array_elements() Hell

PG18 completes the ISO SQL:2016 JSON standard with JSON_TABLE(), which converts a JSON array into relational rows in a single function call. If you have ever written three nested json_array_elements() calls to shred API responses into rows, this is your relief.

SELECT jt.*
FROM api_responses,
     JSON_TABLE(
       payload,
       '$.items[*]'
       COLUMNS (
         item_id   int     PATH '$.id',
         item_name text    PATH '$.name',
         price     numeric PATH '$.price'
       )
     ) AS jt;

Also new: the IS JSON predicate for inline validation in WHERE clauses, and improved error handling via ON ERROR clauses in JSON functions. If you store semi-structured data in JSONB columns, PG18 makes querying it significantly cleaner.

MERGE Gets RETURNING

MERGE arrived in PG15 and was immediately useful but frustratingly incomplete—you could not get the affected rows back. PG18 fixes this with RETURNING support and a new WHEN NOT MATCHED BY SOURCE clause:

MERGE INTO inventory AS target
USING incoming_stock AS source
ON target.sku = source.sku
WHEN MATCHED THEN
  UPDATE SET quantity = target.quantity + source.quantity
WHEN NOT MATCHED THEN
  INSERT (sku, quantity) VALUES (source.sku, source.quantity)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
RETURNING target.sku, target.quantity, merge_action();

The WHEN NOT MATCHED BY SOURCE clause handles target rows with no corresponding source row, enabling full outer join semantics in a single statement. This finally closes the gap between Postgres MERGE and what SQL Server and Oracle have supported for years.

What Breaks: The Upgrade Checklist

PG18 has real breaking changes. Do not skip this section.

• md5 password authentication is removed. If any users or connection strings rely on the md5 method in pg_hba.conf, migrate to scram-sha-256 before upgrading. This is the most common upgrade surprise.
• Extension compatibility. PostGIS requires 3.5+. pgvector requires 0.7+. Run pg_upgrade --check before committing.
• io_method=worker is the new default. Most workloads improve; a small number see unexpected I/O pressure. Test on a read replica or staging environment first.
• Some GUC parameter names changed. Review the PG18 release notes for renamed parameters if you manage postgresql.conf directly.

When to Upgrade

If you self-manage Postgres on bare metal or VMs: test on a read replica now (PG18 beta is stable for testing), and plan the production migration for Q4 2026 after the first minor release (18.1) ships.

If you use RDS, Aurora, Cloud SQL, or Supabase: wait for your provider to certify PG18 support. AWS RDS typically lags 3–6 months behind the GA release. Do not attempt a major version upgrade on a managed platform before the provider explicitly supports it.

The async I/O improvement alone justifies the upgrade for any read-heavy workload. The incremental backup feature alone justifies it for any team managing databases over 100GB. If either description fits you, PostgreSQL 18 belongs on your 2026 roadmap.