PostgreSQL 18 vs 17: what changed and whether to upgrade

Short answer: PostgreSQL 18 is a solid upgrade, not a rewrite. The changes most people will notice are a new asynchronous I/O subsystem that speeds up some read-heavy workloads, native uuidv7(), skip scan on multicolumn B-tree indexes, virtual generated columns, faster upgrades that keep planner statistics, and OAuth authentication. Upgrade on your normal schedule after testing, not in a rush.

This summarises the official release notes. Performance gains depend on your workload. Benchmark your own queries before and after instead of trusting a headline figure.

What is the asynchronous I/O change?

Earlier versions read data pages synchronously: ask the operating system, wait, continue. PostgreSQL 18 adds an asynchronous I/O subsystem so the server can issue multiple reads at once, which helps sequential scans, bitmap heap scans and vacuum, especially on network storage with high latency. It is controlled by the io_method setting (worker, io_uring on Linux, or sync for the old behaviour). Gains show up on I/O-bound workloads. A query already served from memory will not change.

What about UUIDs?

uuidv7() generates time-ordered UUIDs. Random v4 UUIDs as primary keys scatter inserts across the whole index, which hurts cache locality and bloats indexes on large tables. A v7 value begins with a timestamp, so new keys land near the end of the index, like a sequence, while still being globally unique and safe to generate in application code.

CREATE TABLE events (
  id      uuid PRIMARY KEY DEFAULT uuidv7(),
  payload jsonb NOT NULL
);

What is skip scan?

Given an index on (region, created_at), a query that filters only on created_at could not use it efficiently before. With skip scan, PostgreSQL 18 can jump through the distinct values of the leading column and use the index anyway, when the leading column has few distinct values. It does not remove the need to design indexes thoughtfully, but it rescues some queries that had no matching index.

What are virtual generated columns?

Generated columns existed already, but they were stored on disk. Now you can declare a virtual one that is computed when read and takes no storage. Virtual is the default in 18.

CREATE TABLE line_items (
  qty    int     NOT NULL,
  price  numeric NOT NULL,
  total  numeric GENERATED ALWAYS AS (qty * price) VIRTUAL
);

Use stored when you need to index the column or the computation is expensive.

What else is useful?

• `OAuth` authentication: the server can validate OAuth bearer tokens for login, useful with single sign-on setups.
• Upgrades keep statistics: pg_upgrade now carries over planner statistics, so performance does not collapse until ANALYZE finishes after an upgrade.
• `RETURNING` can reference `OLD` and `NEW` in INSERT, UPDATE, DELETE and MERGE.
• Temporal constraints such as WITHOUT OVERLAPS for primary and unique keys, and PERIOD for foreign keys.
• Better `EXPLAIN` output with more buffer and index information by default.
• Data checksums on by default for new clusters created with initdb.

Should you upgrade?

• If you are on 17 and stable, there is no emergency. Each major version is supported for five years from release.
• If you are on 13 or older, check the support end date. Plan the move.
• If you are I/O bound or use random UUID keys heavily, 18 may give a real improvement worth testing.

How do you upgrade safely?

• Read the incompatibilities section of the release notes. Check extensions you depend on have 18-compatible versions.
• Restore last night's backup to a staging server and run pg_upgrade --check against it.
• Run your test suite and your slowest queries on 18 with realistic data, comparing EXPLAIN (ANALYZE, BUFFERS) output with 17.
• Schedule the production upgrade with a rollback plan: keep the old cluster intact until you have verified the new one. pg_upgrade --link is fast and makes rollback harder once the new server has started, so take a backup first.
• After the upgrade, run ANALYZE if you did not carry statistics, and watch slow query logs for a few days.

The upgrade is only as safe as your backup. If you have not tried a restore recently, do the restore drill first.