PostgreSQL 18 is the most consequential release for application developers since 12 introduced generated columns. Async I/O, native uuidv7(), virtual generated columns, OAuth auth, and EXPLAIN ANALYZE shipping buffer info by default. This is the working summary.
If you’re upgrading from 16/17, this post is what to read on the train ride home before the upgrade meeting.
Async I/O — the headline
The new async I/O subsystem is the biggest performance change in years. Pre-18, Postgres issued I/O serially: read a page, wait, read the next page. On modern NVMe and cloud-backed disks that wait left throughput on the table.
Postgres 18 issues many I/O requests in flight, then processes them as they complete:
-- New GUC controls
SET io_method = 'io_uring'; -- Linux 5.1+; the fastest path
-- or
SET io_method = 'worker'; -- worker threads (cross-platform)
Real-world impact:
- Up to 3× faster sequential reads from storage.
- 2× speedup on index-only scans when pages must be fetched.
- Bigger improvements on cloud disks (EBS, GCP PD) where round-trip latency dominated.
You don’t have to do anything in your app — it kicks in automatically. Verify with EXPLAIN (ANALYZE, BUFFERS) and watch the read time drop on cold-cache queries.
uuidv7() is now native
-- Postgres 18+
INSERT INTO orders (id) VALUES (uuidv7());
UUIDv7 is timestamp-prefixed: the first 48 bits are a unix-millis timestamp, then 74 bits of randomness. Why this matters:
- Sortable. New rows insert at the right end of the B-tree. No page splits, no random I/O.
- Index size stays small. UUIDv4’s randomness causes indexes to bloat 30–50% larger than necessary.
- Time-ordered.
MAX(id)gives you “the latest order” without a separatecreated_atindex.
Replace gen_random_uuid() with uuidv7() for any new table where rows are inserted in time order. Old uuid_generate_v4() from uuid-ossp still works; you just shouldn’t reach for it in 2026.
If you’re stuck on Postgres 16/17, generate UUIDv7s in the application — see Distributed Systems Fundamentals for the patterns.
Virtual generated columns
Postgres has had STORED generated columns since 12. They’re great but cost row width. Postgres 18 adds VIRTUAL generated columns — computed at query time, no storage:
CREATE TABLE invoices (
id UUID PRIMARY KEY DEFAULT uuidv7(),
subtotal NUMERIC(12, 2) NOT NULL,
tax_rate NUMERIC(4, 4) NOT NULL,
total NUMERIC(12, 2) GENERATED ALWAYS AS (subtotal * (1 + tax_rate)) VIRTUAL
);
SELECT total FROM invoices WHERE id = $1; -- computed on the fly
When to pick which:
| STORED | VIRTUAL | |
|---|---|---|
| Storage cost | Real | Zero |
| Read speed | Fast (already computed) | Recomputes per query |
| Indexable | Yes | No (you index the source columns instead) |
| Best for | Hot keys promoted from JSONB | Derived display values |
For JSONB hot-field promotion , STORED still wins. For “give me an effective total” without storing it, VIRTUAL is what you want.
EXPLAIN buffers by default
This one’s small but everywhere. EXPLAIN ANALYZE now includes buffer info without an extra BUFFERS flag:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
-- QUERY PLAN
-- Index Scan using orders_customer_id_idx on orders
-- Index Cond: (customer_id = 42)
-- Buffers: shared hit=12 read=3 ← here, by default in 18+
When debugging slow queries, Buffers tells you whether the cost is RAM (shared hit) or disk (read). For the past decade you had to remember to add BUFFERS. No more.
Pair with the deeper indexing patterns in PostgreSQL Indexing and EXPLAIN .
OAuth authentication
Pre-18, Postgres auth was: passwords, certs, GSSAPI, LDAP, or pg_hba shenanigans. Postgres 18 adds first-class OAuth for client connections:
# pg_hba.conf
hostssl all all 0.0.0.0/0 oauth issuer="https://idp.example.com/realms/main" \
scope="openid profile" map=oauth_users
Clients present an OAuth bearer token (validated against your IdP) instead of a password. Useful when:
- You already run Keycloak, Okta, Auth0, or Google Workspace and want one identity surface.
- You’re rotating short-lived tokens for service accounts.
- You want machine-to-machine auth without long-lived passwords in
.pgpass.
Most managed Postgres providers will roll this out through 2026. On self-hosted clusters, configure now. The improvement to your audit story is real.
For application-level auth that pairs with this, see Authentication in 2026 — Passkeys, OAuth, OIDC .
Logical replication improvements
Two changes that matter:
1. Replicate from a standby (came in 16, hardened in 18)
You can subscribe to a standby for logical replication. Splits the read/replication load off the primary.
2. pg_createsubscriber
A built-in tool that converts a physical replica into a logical subscriber. Major win for blue/green migrations and cross-version upgrades.
pg_createsubscriber \
--pgdata=/var/lib/postgres/16/data \
--publisher-server=primary.example \
--subscriber-server=standby.example
Combined with logical replication, this is the cleanest path for a major version upgrade with near-zero downtime: bring up an 18 replica, replicate from 16/17, switch over, retire the old.
Skip-scan on B-tree indexes
A composite index (a, b) used to require a filter on a to be useful. Postgres 18 supports skip-scan:
CREATE INDEX orders_status_created ON orders (status, created_at);
-- Pre-18: this would seq-scan
SELECT * FROM orders WHERE created_at > now() - interval '1 day';
-- 18+: planner can skip-scan distinct values of `status` and use the second column
The planner enumerates distinct leading-column values and scans within each. Not as good as a dedicated (created_at) index, but eliminates the need to add separate indexes for every secondary access pattern. Index bloat goes down. Maintenance cost goes down.
Other things worth knowing
Per-row MERGE RETURNING of OLD/NEW
MERGE INTO inventory t
USING incoming s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty)
RETURNING merge_action(), OLD.qty AS old_qty, NEW.qty AS new_qty;
Useful for outbox-style writes where you want to capture what just changed without a separate trigger.
\PARTIAL and partition-wise improvements
Joins between partitioned tables now plan more aggressively. Big quality-of-life for time-partitioned analytical workloads.
Better COPY observability
COPY my_table FROM 'data.csv' WITH (LOG_VERBOSITY 'verbose');
Per-row error reporting; the planner explains which rows failed and why. Massive win for ETL.
What to test before upgrading
- Extensions. pgvector, PostGIS, TimescaleDB, pg_repack — confirm 18 compatibility. By 2026 most are caught up; verify your specific versions.
- Async I/O on your storage. Some virtualized environments ship
io_uringdisabled.SHOW io_methodand benchmark withpg_bench. - Logical replication topology. If you use replicas, plan the cutover.
- Default vs. opt-in changes. A few default GUCs changed in 18 (notably
default_toast_compression,enable_async_io). Audit before deploying. - EXPLAIN output parsers. If your tooling parses EXPLAIN ANALYZE output, the new BUFFERS-by-default format may need a small change.
A quick upgrade recipe (zero-downtime)
# 1. Spin up an 18 standby replicating from your 16/17 primary (physical replication)
# 2. Convert it to a logical subscriber:
pg_createsubscriber --pgdata=$NEW_DATA --publisher-server=$PRIMARY ...
# 3. Application starts dual-writing or routes reads to the 18 instance
# 4. Verify replication is caught up (pg_stat_subscription)
# 5. Promote 18 to primary, retire old primary
A nightly maintenance window is the alternative. With logical replication, you stop needing one.
Where Postgres 18 doesn’t quite reach
- Distributed Postgres. Citus is the answer if you’ve outgrown a single primary. Postgres 18 doesn’t change that.
- Vector at extreme scale. pgvector + Postgres 18 is great up to ~50M vectors (see pgvector Deep Dive ). Beyond that, Qdrant/Weaviate still earn their keep.
- Real-time analytical workloads. Postgres 18 is much better, but ClickHouse/DuckDB still rule columnar OLAP.
Read this next
- PostgreSQL Indexing and EXPLAIN
- PostgreSQL JSONB Advanced Patterns
- pgvector Deep Dive
- Postgres MVCC, Isolation, and Locking — A Backend Developer’s Guide
If you want a Postgres 18 upgrade checklist, including extension compat, GUC diffs, and a logical replication runbook, it’s at rajpoot.dev .
Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .