Postgres 14 to 18: The Best Features by Version

2026-09-12

The version you're on is a performance decision

PostgreSQL 13 went end of life on 13 November 2025. PostgreSQL 14 follows on 12 November 2026. If you are reading this on a 13 cluster, you are already running unpatched software; if you are on 14, you have roughly fourteen months and one budget cycle to do something about it.

▶ Watch on YouTube: The Best Features of the Last 5 Postgres Versions

Postgres 14 to 18: The Best Features by Version

The project ships a major every autumn and supports each one for five years, which is why the EOL dates cluster in November. PG12 died 14 November 2024, PG13 on 13 November 2025, PG14 on 12 November 2026, and so on down the line. It is the most predictable deadline in your infrastructure and somehow the one that surprises people most.

This is a working reference for which PostgreSQL features actually matter, version by version — the exact GUC names, copy-paste SQL to check your own cluster, the misattributions that keep showing up in interviews and code reviews, and an honest opinion about which features justify an outage window.

Five majors are currently supported: 14, 15, 16, 17 and 18. We'll cover all of them, plus the 12 and 13 features you probably skipped past.

First: what are you actually running?

Before any of this matters, pin yourself on the matrix.

SELECT version();
SHOW server_version_num;

server_version_num is the one to script against. It gives you an integer like 160004, which compares cleanly. Parsing the string from version() has bitten more than one deploy script.

Extensions drift independently of the server, and they're usually what blocks an upgrade — a lot of the features below depend on extension versions tracking core, not just core version, and an old extension binary on a new server is a common source of "why doesn't this GUC exist" tickets.

SELECT e.extname,
       e.extversion            AS installed,
       ae.default_version      AS available
FROM pg_extension e
LEFT JOIN pg_available_extensions ae ON ae.name = e.extname
ORDER BY e.extname;

If installed and available differ, you have an ALTER EXTENSION ... UPDATE pending. If an extension you depend on has no row in pg_available_extensions on your target version's packages, you have an upgrade blocker, and you want to know that now rather than during the maintenance window.

The cheat sheet: feature → minimum version

Bookmark this. It's the table that should exist before anyone starts an upgrade project.

| Feature | Introduced in | Notes | |---|---|---| | CTE inlining (MATERIALIZED / NOT MATERIALIZED) | 12 | Non-recursive, single-reference CTEs inline by default; MATERIALIZED forces the old fence | | Generated columns (STORED only) | 12 | VIRTUAL didn't exist yet | | REINDEX CONCURRENTLY | 12 | No more exclusive lock for reindex | | B-tree index deduplication | 13 | Not 12 — see correction below | | Parallel vacuuming of indexes | 13 | Heap scan stays single-process | | Incremental sort (enable_incremental_sort) | 13 | Reuses partially sorted input | | pg_stat_statements planning time (track_planning) | 13 | Splits planning time from execution time | | Trusted extensions | 13 | Non-superuser installs of marked extensions | | Bottom-up index deletion | 14 | Fights UPDATE-driven index bloat | | VACUUM skips insignificant index vacuuming | 14 | Fewer wasted full-index passes | | compute_query_id | 14 | Query ID in pg_stat_activity, EXPLAIN VERBOSE, logs | | LZ4 TOAST compression (default_toast_compression) | 14 | Per-column, only applies to newly written values | | Logical replication streaming of in-progress transactions | 14 | streaming = on on subscription | | idle_session_timeout | 14 | Distinct from idle_in_transaction_session_timeout | | MERGE | 15 | SQL-standard upsert-plus-delete | | wal_compression = lz4 \| zstd | 15 | pglz was the only option before | | Logical replication row filters and column lists | 15 | Finer-grained publications | | Cumulative statistics in shared memory | 15 | Stats collector process is gone | | UNIQUE NULLS NOT DISTINCT | 15 | NULLs now collide if you ask them to | | security_invoker views | 15 | View runs with caller's privileges | | CREATE on public revoked from PUBLIC | 15 | Breaking change — see below | | pg_stat_io | 16 | First real per-backend I/O breakdown | | Parallel FULL and internal RIGHT hash joins | 16 | More join shapes go parallel | | Logical decoding on standby | 16 | Offload CDC reads from primary | | vacuum_cost_delay changes take effect mid-run | 16 | Throttle a running vacuum without restarting it | | TidStore vacuum memory (no 1 GB dead-item cap) | 17 | No more multi-pass index vacuuming on big tables | | Incremental backup (--incremental, pg_combinebackup, summarize_wal) | 17 | Native, no third-party tool required | | pg_createsubscriber | 17 | Standby to logical subscriber, no full resync | | Failover slots (sync_replication_slots) | 17 | Logical slots survive failover | | pg_stat_checkpointer | 17 | Checkpoint stats split out of pg_stat_bgwriter | | MERGE ... RETURNING, WHEN NOT MATCHED BY SOURCE | 17 | Closes the gap with hand-rolled upsert logic | | EXPLAIN (SERIALIZE, MEMORY) | 17 | Real client-side serialization cost | | Asynchronous I/O (io_method) | 18 | worker default, io_uring opt-in on Linux | | B-tree skip scan | 18 | Multicolumn index usable without leading column | | Virtual generated columns (default) | 18 | VIRTUAL if you omit the keyword | | BUFFERS on by default in EXPLAIN ANALYZE | 18 | You've been adding this manually for years | | pg_upgrade carries planner statistics | 18 | No mandatory post-upgrade ANALYZE | | uuidv7() | 18 | Alongside uuidv4(), time-ordered inserts |

Release dates, for the record: 14 on 30 September 2021, 15 on 13 October 2022, 16 on 14 September 2023, 17 on 26 September 2024, 18 on 25 September 2025.

What you already missed: 12 and 13

Both are EOL. Nothing here is something to plan around. These are arguments to hand your change board when they ask why the upgrade can't wait another quarter.

Two corrections first, because these get repeated constantly.

B-tree deduplication is PostgreSQL 13, not 12. It gets attributed to 12 in blog posts and conference talks often enough that it has become folklore. It landed in 13, it applies automatically wherever duplicate values exist in a B-tree, and on a low-cardinality index — status flags, booleans, foreign keys with few distinct parents — it can cut size substantially without you changing a line of DDL.

Parallel index vacuuming is 13, and only the index phase is parallel. The heap scan is still single-process. People read "parallel vacuum" and assume the whole operation scales with max_parallel_maintenance_workers. It doesn't. If your vacuum is slow because the table is enormous and the heap scan dominates, more workers buy you nothing.

The rest of 13's useful set: incremental sort, gated by enable_incremental_sort, which lets the planner reuse a partially sorted input instead of re-sorting everything. pg_stat_statements planning time via track_planning, which is the difference between knowing a query is slow and knowing the planner itself is the slow part. And trusted extensions, which let a non-superuser install marked extensions and removed one recurring ticket type entirely.

From 12: CTE inlining, which was a real planner change. Non-recursive, side-effect-free CTEs referenced exactly once can be inlined instead of forming an optimisation fence. If you wrote CTEs as deliberate fences before 12, your queries changed plan under you on upgrade, and MATERIALIZED is the keyword that puts them back. Also from 12: generated columns, STORED only at that point, and REINDEX CONCURRENTLY, which is the single most useful bloat tool in the list.

None of this is a reason to stay on 12 or 13. Both are dead or nearly so. It's an argument for why the jump forward is worth the time, not for keeping them around.

PostgreSQL 14 features: the bloat and observability release

14 is where the first real attack on index bloat shows up.

Bottom-up index deletion targets the specific pain of version churn: you UPDATE a row, the indexed column doesn't change, and yet every index picks up a new tuple pointer. On an UPDATE-heavy table with several indexes, that's how a 2 GB index becomes a 9 GB index over six months. Bottom-up deletion cleans those up before a page split rather than waiting for vacuum. It's automatic, needs no configuration, and on the right workload it's the difference between weekly reindexing and never thinking about it.

VACUUM can skip index vacuuming when the number of removable index entries is insignificant. Small change, large effect on tables with many indexes and few dead tuples, because you stop paying the full index scan cost for a handful of entries.

compute_query_id exposes a query identifier in pg_stat_activity, EXPLAIN VERBOSE and the log line, aligned with the id pg_stat_statements uses. Turn it on. It's the join key between "what's running right now," "what's in my logs," and "what's expensive cumulatively." Before 14 you stitched those together by hand with regex and hope.

LZ4 TOAST via default_toast_compression has two caveats that matter. First, it applies per column, and only to newly written values. Setting default_toast_compression = lz4 doesn't recompress your existing bloated JSONB column — you get the benefit as rows are rewritten, or immediately if you force a rewrite. Plan for that lag. Second: TOAST supports pglz and lz4 only. There is no zstd for TOAST. Zstd is a wal_compression option, and that's a PG15 feature. The two get merged in people's heads because they arrived a year apart and both say "compression."

Also in 14: logical replication can stream in-progress transactions, set per subscription with streaming = on, which stops a single large transaction from stalling apply until commit. And idle_session_timeout, distinct from the older idle_in_transaction_session_timeout, which reaps idle non-transaction sessions — useful when an application pool leaks connections, not a substitute for fixing the pool.

PostgreSQL 15 features: MERGE, sorting, and the upgrade that breaks your deploys

Start with the gotcha, because it's the one that generates the 2am call.

PostgreSQL 15 removed the default CREATE permission on the public schema for ordinary users. Your migration tooling, which has happily created tables in public as an unprivileged app user since forever, will fail immediately after the upgrade with a permission denied error — not on upgrade of an existing database, which keeps its existing grants, but on any fresh cluster or restored dump into a clean instance. The fix is trivial (GRANT CREATE ON SCHEMA public TO app_user, or better, give the app its own schema), but discovering it during the deploy that follows the upgrade is a bad time. Put it in the pre-upgrade checklist.

The features: MERGE finally arrived, SQL-standard syntax for the upsert-plus-delete pattern people were emulating with CTEs and ON CONFLICT gymnastics. It was limited in 15; 17 fills the gaps.

wal_compression gained lz4 and zstd alongside the original pglz. On a write-heavy cluster this is free money for your WAL volume and archive storage. Zstd generally compresses better at the cost of more CPU; lz4 is faster to compress and decompress. Default to lz4 when the CPU cost matters; zstd when archive bandwidth or retention cost is the binding constraint.

Logical replication got row filters and column lists on publications, turning "replicate this whole table to the analytics box" into something you can scope — useful for compliance-sensitive columns you don't want landing on a downstream replica. Cumulative statistics moved into shared memory and the separate stats collector process went away, removing a genuine source of stats loss and temp-file churn. UNIQUE NULLS NOT DISTINCT lets a unique index treat NULLs as equal, which is what most people assumed it did already. And security_invoker views run with the caller's permissions instead of the owner's, which is how a lot of row-level security designs should have been written from the start.

PostgreSQL 16 features: pg_stat_io and more parallel shapes

pg_stat_io is the reason to care about 16. It breaks I/O down by backend type, object and context, which means you can finally answer "who is doing the reads" without guessing from pg_statio_* aggregates.

SELECT backend_type,
       object,
       context,
       reads,
       read_bytes,
       hits,
       evictions,
       ROUND(100.0 * hits / NULLIF(hits + reads, 0), 2) AS hit_pct
FROM pg_stat_io
WHERE reads > 0 OR hits > 0
ORDER BY reads DESC
LIMIT 20;

What to look for: a client backend row with a bulkread context and huge reads means someone is sequential-scanning past your shared buffers. High reads against vacuum context tells you autovacuum is fighting cold cache and your autovacuum_work_mem or scheduling needs attention — this is how you catch an autovacuum worker doing almost entirely physical reads on a bloated table, hit rate in single digits, while the app's own backends sit above 95%. That's a targeted VACUUM (VERBOSE) and a maintenance_work_mem bump, not a "buy more disk" ticket. Low hit_pct on normal context for client backends is the classic undersized shared_buffers signal, and now you have a number instead of a hunch.

Also in 16: parallel execution of FULL and internal RIGHT hash joins, which quietly speeds up a class of analytical queries that previously fell back to serial. Logical decoding on standby, which lets you move CDC load off the primary. And vacuum_cost_delay changes now take effect during a running vacuum, so you can throttle a vacuum that's hurting production without cancelling it and starting over.

PostgreSQL 17 features: the DBA release

If you're only approving one upgrade this year, aim for 17 or later — it has the highest ratio of operational payoff to application risk of any release in this set.

TidStore. VACUUM's dead-tuple array was replaced with a new memory structure, cutting vacuum memory use and removing the old 1 GB cap on dead-item storage. That cap was the reason vacuuming a large, heavily-updated table required multiple index passes: fill 1 GB of TIDs, scan every index, come back, do it again. On a 500 GB table with six indexes, that was hours of avoidable work. 17 does it in one pass. This is the single best vacuum change since parallel index vacuuming landed in 13.

Incremental backup. pg_basebackup --incremental plus pg_combinebackup to reconstruct, with summarize_wal enabled on the source server so it can track which blocks changed. Native, no third-party tool required. The storage and network cost of a nightly full backup on a multi-terabyte instance is real money, and incrementals can cut both by an order of magnitude depending on your churn rate.

pg_createsubscriber converts a physical standby into a logical replication subscriber, without building the logical replica from scratch and resyncing all the data. That turns major-version upgrades with logical replication from a multi-day initial sync into something you can schedule.

Failover slots. Logical slots can now be synchronised to standbys using the failover option on the slot together with sync_replication_slots. Before this, a failover meant your logical subscribers lost their position silently and you re-seeded, discovering it only when someone noticed downstream data had stopped moving.

pg_stat_checkpointer splits checkpointer stats out of pg_stat_bgwriter into their own view, making checkpoint tuning legible.

On the SQL side: MERGE ... RETURNING and WHEN NOT MATCHED BY SOURCE complete what 15 started. And EXPLAIN (SERIALIZE, MEMORY) tells you how much time went into serialising the result to the client and how much memory the planner used — useful when a query looks cheap in the cost estimate but the client-side result transfer is where the time actually goes.

PostgreSQL 18 features: async I/O, skip scan, and a cheaper upgrade

18 shipped 25 September 2025, so production mileage is still building, but three things are worth planning around now.

Asynchronous I/O, controlled by io_method. Default is worker, which uses a pool of I/O worker processes. io_uring is available on Linux builds compiled with liburing support, and it's not the default even where available — you opt in explicitly. Check your packages before you plan around it; if your distro build lacks liburing, io_method = io_uring won't start. This mostly helps sequential scans and vacuum on storage where the previous synchronous read pattern left queue depth on the table. Treat it as a "test on staging under load" item, not a "flip on day one" item.

B-tree skip scan lets a multicolumn index serve queries that don't constrain the leading column. Historically the answer to "my index on (tenant_id, created_at) is useless for my created_at query" was "add another index." Now the planner can skip through distinct leading values. It helps most when the leading column has low cardinality — it's not a licence to stop thinking about index order.

Virtual generated columns, and note the default changed: if you write GENERATED ALWAYS AS (...) with neither STORED nor VIRTUAL, you get VIRTUAL in 18. Generated columns arrived in 12 as STORED only. If you have DDL that relied on the old implicit behaviour, read it again.

BUFFERS is on by default in EXPLAIN ANALYZE. Small change, big quality-of-life win, given how many plans get shared without buffer counts.

pg_upgrade transfers planner statistics to the new cluster. Previously, the cluster came up with no stats and planned badly until you ran a full ANALYZE, which on a large database meant an hour of terrible query plans in production while you frantically ran vacuumdb --analyze-in-stages. That window is gone. On big estates that ANALYZE was often the longest part of the upgrade; this alone makes 18 a materially cheaper upgrade than any prior major, purely on maintenance-window arithmetic.

Also: uuidv7() alongside uuidv4(), giving you time-ordered UUIDs and primary keys that don't shred your B-tree insertion locality.

The three threads that actually matter

Read the releases as three continuous stories rather than five feature lists.

The vacuum thread. 13 parallelised index vacuuming. 14 let VACUUM skip insignificant index work and added bottom-up index deletion to reduce the bloat in the first place. 17 removed the 1 GB dead-item cap with TidStore and killed multi-pass index vacuuming on big tables. 18 gave the whole thing async I/O. If you're on 14 and vacuum is your operational pain, 17 is the version with your name on it.

The observability thread. 13 added planning time to pg_stat_statements. 14 added compute_query_id and joined the views together. 15 moved stats into shared memory. 16 added pg_stat_io. 17 split out pg_stat_checkpointer and added EXPLAIN (SERIALIZE, MEMORY). 18 turned BUFFERS on by default. Every release added a number you previously had to infer.

The disk-footprint thread. 13 deduplicated B-tree entries. 14 added LZ4 TOAST. 15 added LZ4 and zstd WAL compression. 17 added incremental backup. None of these alone is dramatic, but they compound — dedup shrinks index bloat, LZ4 shrinks large column values, WAL compression shrinks replication and archive traffic, incremental backup shrinks backup storage and time. Stacked across four majors, that's a meaningful reduction in bytes on disk, bytes in the archive, and bytes over the wire, none of which required an application change.

What upgrading does not fix

A client called about a disk-full alert on a production primary. The runbook said check WAL archiving, because the last time this happened archive_command had failed and pg_wal grew until the volume filled. Reasonable runbook. Wrong this time.

WAL was fine. The space was in pgsql_tmp. A per-minute cron job ran a report query that spilled to disk, and the query had crept from occasionally slow to reliably slow over a few weeks, until it started taking more than a minute. So the next invocation started before the previous one finished. Then the next. By the time anyone looked, roughly 150 overlapping copies of the same query were each writing their own temp files, and the volume filled in under two hours.

No release note fixes that. There's no GUC for "my cron job has no lock file." log_temp_files would have caught it weeks earlier. Alerting on pg_stat_activity count for a matching query_id would have caught it in minutes. Watching temp bytes as a first-class metric instead of only watching total disk would have caught it before the page — every dashboard was pointed at WAL volume and table bloat, the usual suspects, and temp files just weren't on anyone's radar.

Upgrades are worth doing. They are not a substitute for knowing what your database is doing.

Which Postgres version should I upgrade to?

The default recommendation is n-1. Today that means 17. It has had a year of point releases, the extension ecosystem has caught up, and it happens to be the release with the most DBA value in a decade.

Jump straight to 18 if you're currently on 14 and facing the November 2026 EOL anyway, because the change-management cost of an upgrade is mostly fixed regardless of how many majors you cross, and pg_upgrade carrying planner statistics genuinely shortens the window. Also go to 18 if async I/O or skip scan addresses a problem you've measured, or if it's a greenfield build with a confirmed-compatible extension stack and testing bandwidth to spare.

The extension checklist, in order: confirm every extension in pg_extension has a build for the target major, with particular attention to anything doing binary-level integration — pg_stat_statements, pg_repack, pgaudit, any FDW; confirm your connection pooler version supports it; check whether you use any public schema creation as a non-superuser (the PG15 change applies cumulatively no matter which version you jump from); re-read any CTE you deliberately wrote as an optimisation fence if you're coming from 11 or earlier; and test MERGE and generated-column DDL if you're landing on 18. Extension incompatibility is the single most common reason an upgrade that tested clean in staging fails in production, because staging rarely has the same extension footprint as prod after two years of ad hoc installs.

The real argument against skipping majors isn't technical. It's that four versions of behaviour changes arrive in one deploy — the public schema permission change, generated column defaults, the stats collector removal, parallel query shape changes, all at once — and when something regresses you have four release notes to bisect instead of one. The technical work of pg_upgrade --link is usually the easy part; the organizational cost of explaining four majors of breaking changes in one go is not.

Checking your own estate

If you don't know what versions you're running across the fleet, that's the first thing to fix, and it's cheaper than you think.

MyDBA runs a free health check that reports your version and EOL runway, vacuum and bloat state, and your worst queries ranked by measured mean and max time rather than by whoever complained loudest. It also tracks plan changes over time, which is how you catch the regression that an upgrade introduced before your users do.

Start with SHOW server_version_num. Then work out how many Novembers you have left.