Best Postgres Features by Version: 14 Through 18

Most "what's new in Postgres" posts are release notes with paragraph breaks. This is not that. This is the shortlist of things that changed how I operate databases between PG 14 and PG 18, with the version each landed in, the GUC or DDL you have to flip because almost none of them are on by default, and SQL you can paste into psql right now to see what your install supports.

📖 Read the full guide: Postgres 14 to 18: The Best Features by Version

Run SHOW server_version, find your row, read down.

TL;DR: the five features worth an upgrade

• PG 14 → LZ4 TOAST compression. ALTER SYSTEM SET default_toast_compression = 'lz4'; Off by default, still pglz in 18.
• PG 15 → row filters on publications. CREATE PUBLICATION p FOR TABLE orders WHERE (region = 'eu');
• PG 16 → pg_stat_io. No GUC, just SELECT * FROM pg_stat_io; and your first real I/O breakdown in core.
• PG 17 → incremental backup. ALTER SYSTEM SET summarize_wal = on; then pg_basebackup --incremental=/backups/full/backup_manifest. Off by default.
• PG 18 → asynchronous I/O. ALTER SYSTEM SET io_method = 'io_uring'; on Linux, or leave it on the new worker default.

There's a 6-minute video version if you want the fast pass. This is the version with the commands, going version by version with the exact GUCs, the syntax, and the caveats that bite.

First: what are you actually running?

SELECT version();
SHOW server_version;
SELECT current_setting('server_version_num')::int;

The third one is the only one you should use in scripts. server_version_num returns an integer like 170004 for 17.4, so gating is arithmetic instead of regex:

SELECT current_setting('server_version_num')::int >= 170000 AS has_pg17_features;

I have debugged a deploy script that parsed version() with a substring and broke when a distro appended its own build string. Don't.

Lifecycle check, because it changes your priorities. The project supports each major for five years. PG 13 got its final minor release in November 2025 and is done. PG 14's final minor is scheduled for November 2026, which means if you're on 14 you have an upgrade on your roadmap whether you've written it down or not. Everything below is ordered by how old your install is.

Postgres 14: the quiet operational release

Released 30 September 2021. Nothing flashy, a lot of things that stop pages at 3am.

LZ4 TOAST compression. Off by default and nobody tells you. The default is still pglz in PG 18.

ALTER SYSTEM SET default_toast_compression = 'lz4';
SELECT pg_reload_conf();

ALTER TABLE events ALTER COLUMN payload SET COMPRESSION lz4;

The gotcha: changing the compression method does not rewrite existing TOASTed values. Only newly stored values use lz4. Old rows keep whatever they were compressed with until something updates them. Check what you've actually got:

SELECT pg_column_compression(payload) AS method, count(*)
FROM events
GROUP BY 1;

If that returns a pile of pglz after you flipped the GUC, that's expected, not a bug. Rewrite the table if you care.

The vacuum failsafe. vacuum_failsafe_age and multixact_failsafe_age make an in-progress vacuum drop index cleanup and cost delays once XID wraparound gets dangerously close. This is automatic — there's nothing to flip — but know it exists, because when it kicks in your vacuum will suddenly get faster and uglier, and you should recognize why instead of thinking something broke. This is the feature that quietly saved a lot of people who were about to learn what single-user mode feels like.

Logical replication streaming of in-progress transactions, per subscription:

ALTER SUBSCRIPTION sub_orders SET (streaming = on);

Before this, a big transaction on the publisher spooled entirely to disk on the decoder before anything moved. Also in 14: snapshot scalability work that genuinely helps boxes carrying thousands of connections.

Postgres 15: MERGE, and the one that broke your deploy

Released 13 October 2022.

MERGE. Real upsert-with-delete in one statement:

MERGE INTO inventory AS t
USING staging_inventory AS s
   ON t.sku = s.sku
WHEN MATCHED AND s.qty = 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET qty = s.qty, updated_at = now()
WHEN NOT MATCHED THEN
  INSERT (sku, qty, updated_at) VALUES (s.sku, s.qty, now());

Honest caveat, and it's the one people miss: MERGE does not give you ON CONFLICT-style protection against concurrent inserts. Two sessions merging the same key can still produce a unique violation. It is not a drop-in replacement for INSERT ... ON CONFLICT DO UPDATE in a hot write path. I use MERGE for batch reconciliation jobs and ON CONFLICT for anything the application does concurrently.

Publication row filters and column lists. This is the one that made selective logical replication practical:

CREATE PUBLICATION pub_eu_orders
  FOR TABLE orders (id, customer_id, total, region)
  WHERE (region = 'eu' AND status <> 'draft');

That's the difference between shipping a full customer table to a reporting replica and shipping only what that replica actually needs.

wal_compression accepts lz4 and zstd as of 15, not just on/off/pglz. Off by default:

ALTER SYSTEM SET wal_compression = 'zstd';

Worth it if you're WAL-bandwidth constrained between primary and archive or standby, less so if CPU is your bottleneck.

The compatibility landmine. In 15, CREATE on the public schema was revoked from PUBLIC, and public is owned by pg_database_owner. Your app user that always created tables suddenly gets:

ERROR:  permission denied for schema public
LINE 1: CREATE TABLE widgets (id int);

The fix, assuming you actually want that behaviour:

GRANT CREATE ON SCHEMA public TO app_user;

Better: give the app its own schema and stop using public as a dumping ground. But at 2am, that GRANT is the fix. This one bites people on nearly every 14→15 migration I've done — test your deploy scripts against a 15 instance before you cut over, not after.

Postgres 16: parallelism and observability

Released 14 September 2023. The least flashy release in this range, and that's fine.

Parallel FULL and internal RIGHT hash joins. Before 16, a plan with Hash Full Join collapsed to a single worker. On 16 the same query shape gets Gather → Parallel Hash Full Join → Parallel Hash, and a report that took minutes takes tens of seconds. Re-check your EXPLAIN output after upgrading; some plans just get better with no work from you.

pg_stat_io. First real I/O breakdown in core, split by backend type, target object and context (normal, vacuum, bulkread, bulkwrite):

SELECT backend_type, object, context, reads, read_time, hits, evictions
FROM pg_stat_io
WHERE reads > 0
ORDER BY reads DESC
LIMIT 15;

This is how you find out that your "slow queries" are actually autovacuum doing bulkread and evicting everything warm — a conversation worth having before you blame the SAN.

Logical decoding on physical standbys. You can point CDC at a replica instead of stacking decoding load on the primary. If you have Debezium or similar chewing on your write leader, this alone justifies 16.

Postgres 17: the backup and replication release

Released 26 September 2024. If you own backups, this is the release you want.

Incremental backup. summarize_wal is off by default:

ALTER SYSTEM SET summarize_wal = on;
-- requires restart

# full, once
pg_basebackup -D /backups/2026-09-07-full -c fast

# incremental, against the prior manifest
pg_basebackup -D /backups/2026-09-12-incr \
  --incremental=/backups/2026-09-07-full/backup_manifest -c fast

# reconstruct before you ever start it
pg_combinebackup /backups/2026-09-07-full /backups/2026-09-12-incr \
  -o /restore/pgdata

Hard rules, learned the boring way: an incremental is useless without every backup in its chain, so you retain the full plus all intermediates or you have nothing. You must run pg_combinebackup before starting the directory as a data dir. And don't mix major versions in a chain. This isn't a toy feature, but treat your retention policy as part of the feature, not an afterthought.

Rewritten dead-TID storage in VACUUM. The old dead-tuple array was replaced with a compact radix tree (TidStore), which cuts memory use sharply and removes the effective 1 GB cap. Practical effect on a 2 TB table: fewer index-vacuum passes, because vacuum no longer runs out of room mid-scan and has to start over on the indexes. This is the strongest quiet argument for 17.

Failover slots. Logical subscribers used to die at every failover. Now:

-- on the subscriber
ALTER SUBSCRIPTION sub_orders SET (failover = true);

-- on the standby (off by default)
ALTER SYSTEM SET sync_replication_slots = on;
-- on the primary
ALTER SYSTEM SET synchronized_standby_slots = 'standby_1';

Plus pg_createsubscriber, which converts a physical standby into a logical subscriber instead of making you re-copy terabytes. This is the piece that finally makes "promote a standby without losing your CDC pipeline" a real workflow instead of a manual scramble.

Also worth knowing: MERGE ... RETURNING and WHEN NOT MATCHED BY SOURCE, the new transaction_timeout GUC (which finally covers the gap between statement_timeout and idle_in_transaction_session_timeout), and COPY ... (ON_ERROR ignore) so one bad row doesn't kill a 40-million-row load.

Postgres 18: async I/O, skip scan, and an upgrade that doesn't blind the planner

Released 25 September 2025. Young, but the headline items are real.

Asynchronous I/O via io_method. Default is worker, so you get some benefit with no action. On Linux builds compiled with liburing:

ALTER SYSTEM SET io_method = 'io_uring';
-- requires restart; 'sync' restores pre-18 behaviour

What actually benefits: sequential scans, bitmap heap scans, and vacuum — the read-heavy, prefetch-friendly workloads. Don't expect miracles on an OLTP workload dominated by index point lookups. io_uring needs a recent kernel and a build with liburing support, and 18 is new enough that I'd stay on worker in production for a while and test io_uring on a replica first.

B-tree skip scan. A multicolumn index can now be used when the query doesn't constrain the leading column. It works best when the leading column has few distinct values. Concretely: CREATE INDEX ON events (tenant_status, created_at) where tenant_status has four values. Queries filtering only on created_at used to seq-scan. Now they can skip.

pg_upgrade carries optimizer statistics. You no longer wake up in a freshly upgraded cluster where every plan is a guess. vacuumdb --analyze-in-stages used to be mandatory post-upgrade homework. Now it's optional cleanup.

Smaller but nice: EXPLAIN ANALYZE includes BUFFERS by default, VIRTUAL generated columns arrived and VIRTUAL is now the default when you omit the storage kind, uuidv7() gives you time-ordered UUIDs that index better than v4 and don't shred your index, and RETURNING can reference both OLD and NEW.

The cheat sheet

Version	Feature	On by default?	How to enable/use
14	LZ4 TOAST compression	No (pglz)	SET default_toast_compression='lz4'; ALTER TABLE ... SET COMPRESSION lz4
14	Vacuum failsafe	Yes (automatic)	Tune vacuum_failsafe_age, multixact_failsafe_age
14	Streaming in-progress logical txns	No	ALTER SUBSCRIPTION ... SET (streaming = on)
15	MERGE	Yes (syntax)	MERGE INTO ... WHEN MATCHED ...
15	Publication row filters / column lists	Yes (syntax)	CREATE PUBLICATION ... FOR TABLE t (cols) WHERE (...)
15	wal_compression lz4/zstd	No (off)	ALTER SYSTEM SET wal_compression='zstd'
15	public schema CREATE revoked	Yes (behaviour change)	GRANT CREATE ON SCHEMA public TO app_user
16	Parallel FULL/RIGHT hash join	Yes	Nothing; re-check EXPLAIN
16	pg_stat_io	Yes	SELECT * FROM pg_stat_io
16	Logical decoding on standby	Yes	Create the slot on the standby
17	Incremental backup	No	summarize_wal=on, pg_basebackup --incremental=, pg_combinebackup
17	New VACUUM dead-TID storage	Yes	Nothing
17	Failover slots	No	failover=true, sync_replication_slots=on, synchronized_standby_slots
17	transaction_timeout	No (0)	ALTER SYSTEM SET transaction_timeout='60s'
17	COPY ON_ERROR ignore	No	COPY t FROM '...' (ON_ERROR ignore)
18	Asynchronous I/O	Partly (worker)	ALTER SYSTEM SET io_method='io_uring'
18	B-tree skip scan	Yes	Nothing; planner decides
18	pg_upgrade keeps statistics	Yes	Nothing
18	VIRTUAL generated columns	Yes (new default)	GENERATED ALWAYS AS (...) VIRTUAL
18	uuidv7()	Yes	SELECT uuidv7();

What I'd actually upgrade for

On 13 or older: you're unsupported. Stop reading and plan the upgrade. That's the whole recommendation.

On 14 or 15: the PG 17 backup and vacuum work is the strongest single argument I can make. Incremental backup plus the new dead-TID storage changes your nightly window and your wraparound risk at the same time, more than anything since parallel query. Don't skip straight past it chasing 18's shiny stuff. Remember 14's final minor lands November 2026.

On 16: you can reasonably wait for 18.2-ish unless you're I/O bound, in which case async I/O and skip scan are worth the early adoption tax — go test io_uring on 18 in staging now.

One war story, because features aren't operations. We got a disk-full alert on a primary and everyone immediately blamed WAL archiving, because it's always WAL archiving. It wasn't. pg_stat_activity showed about 150 overlapping runs of the same cron-driven report, each spilling temp files, because the job had no locking and work_mem was tuned for OLTP concurrency, not batch reporting, and each run was taking longer than its interval. pg_ls_tmpdir() told the story in ten seconds once someone looked. The fix was a lock file and a separate work_mem setting for the reporting role, not a Postgres version. None of the PG 18 goodies would have caught that, and no amount of io_method tuning fixes a job that runs 150 copies of itself. New Postgres features raise the ceiling; they don't replace watching your own workload. That's more or less the itch that got me building MyDBA — I got tired of manually correlating pg_stat_io against cron logs at 3am, but the discipline is still the point.

Corrections I owe you

Things I've seen misstated, including in my own faster pass on video:

• B-tree deduplication shipped in 13, not 12. It applies automatically to eligible indexes created or reindexed on 13+.
• TOAST compression is pglz and lz4 only, still true in 18. zstd is for WAL compression (15+) and for pg_basebackup/pg_dump output. There is no zstd TOAST.
• STORED generated columns came in 12. VIRTUAL only arrived in 18, where it also became the default if you don't specify the storage kind.
• Incremental sort and parallel VACUUM index cleanup were both 13. CTE inlining, with the MATERIALIZED / NOT MATERIALIZED keywords, was 12.

Text is where you get to be exact. Corrections welcome; I'd rather be right than consistent.

Tags: postgres database performance devops ## Closing thought: features are a ceiling, not a floor

None of this replaces knowing your own workload. Every version above gives you a sharper tool — better compression, better backups, better I/O — but the incidents that actually page you are usually mundane: a missing index, a runaway cron job, a work_mem setting tuned for the wrong workload. Upgrade for the real wins (17's backup and vacuum work, 18's async I/O if you're read-heavy), but don't mistake a new major version for a substitute for watching pg_stat_io, your replication lag, and your autovacuum logs on an ordinary Tuesday. That habit outlasts every release note.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=best-postgres-features-by-version

If you want the version checks and cheat-sheet queries above running against your own cluster instead of copy-pasted into psql one at a time, that's exactly what MyDBA is for.