PostgreSQL 17 in Production, Features Worth Using

TL;DR — Postgres 17 ships streaming I/O for sequential and bitmap scans, native incremental backups via pg_basebackup --incremental, and a genuinely useful MERGE ... RETURNING. The wins are real, but you only see them with the right io_combine_limit and updated extensions.

PostgreSQL 17 went GA on September 26, 2024, and I’ve had it running under a real workload for about six weeks. The release notes are long, but most of what’s in them is plumbing you’ll never touch directly. A smaller set of changes will pay for the upgrade by themselves, and a couple of them require you to think differently about how you’ve been doing things.

This post is the short version of what I’d hand a teammate before they upgrade a serious cluster. I’m assuming you’re already on 15 or 16, you use logical replication for at least some pipelines, and you care about p99 more than feature checkboxes. If you’re still on 13, the bigger question is your upgrade plan, not what 17 added.

I’ll cover the features that changed how I run Postgres, the things that sound exciting but aren’t yet, and the migration gotchas that bit me. There are working psql snippets you can paste into a 17 cluster today.

Streaming I/O actually speeds up sequential scans

The headline performance change is the new streaming I/O subsystem. Sequential scans and ANALYZE both use it in 17, with index scans and other paths coming in later releases. The practical effect is that a large SELECT * over a cold cache no longer waits for one 8 KB block at a time. The executor asks for a window of blocks, and the storage layer prefetches them as a batch.

You don’t have to do anything to opt in, but two GUCs decide how much you get out of it:

-- session-level inspection
SHOW io_combine_limit;       -- default 128kB in 17
SHOW effective_io_concurrency; -- bump to 200+ on NVMe

On a 240 GB table sitting on local NVMe, raising effective_io_concurrency from 1 (the historical default) to 256 took a cold sequential scan from 41 seconds to 18 on my box. ANALYZE on the same table dropped from 26 to 11. If you’ve been compensating with parallel workers, you can probably back those down a notch.

The official rundown lives in the PostgreSQL 17 release notes. Read the “I/O performance” section before tuning.

Incremental backups change my backup story

Before 17, “incremental backup” meant pgBackRest, Barman, or a homegrown script around pg_basebackup plus WAL archiving. Postgres 17 makes incremental base backups a first-class feature with pg_basebackup --incremental and a new pg_combinebackup tool to merge them.

# full backup on Sunday
pg_basebackup -D /backups/full-$(date +%F) -Ft -X stream -P

# incremental on Monday, referencing the manifest
pg_basebackup -D /backups/inc-$(date +%F) \
  --incremental=/backups/full-2024-11-03/backup_manifest \
  -Ft -X stream -P

# restore: combine full + chain of incrementals
pg_combinebackup /backups/full-2024-11-03 /backups/inc-2024-11-04 \
  -o /var/lib/postgresql/17/restore

This does not replace pgBackRest 2.53 for clusters where you need parallelism, encryption, and remote repos. But for a single-host or small cluster, you can now run nightly fulls with hourly incrementals using only what ships with Postgres. The size savings are similar to block-level diff tools, since incrementals only ship blocks changed since the reference manifest.

The catch is that you must keep the full backup and every intermediate incremental until you cut a new full. Lose the chain, lose the restore.

MERGE got RETURNING and conditional WHEN actions

I’ve been waiting for MERGE ... RETURNING since 15 shipped MERGE. In 17 it works:

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

That merge_action() is the new function that tells you whether each row was inserted, updated, or deleted. Before this, doing upserts with audit trails meant writing the same logic twice or pulling everything back with a CTE.

The other 17 addition is WHEN NOT MATCHED BY SOURCE, which lets you delete or update rows in the target that have no matching source row. It’s the missing third leg of a full sync.

Logical replication failover finally usable

Logical replication has had a decade of “it almost works for failover” stories. In 17, replication slots can be synchronized to standby servers, and pg_createsubscriber converts a physical standby into a logical subscriber in place.

-- on primary
ALTER SYSTEM SET sync_replication_slots = on;
ALTER SYSTEM SET standby_slot_names = 'standby_1';
SELECT pg_reload_conf();

The practical impact is that you can do a logical-replication-based major-version upgrade without losing the subscription state when the primary fails over mid-migration. I used this on the upgrade I just finished and it removed a class of “oh god the slot disappeared” panic.

For more on the surrounding pattern, see logical replication for blue green Postgres deploys.

What I’m not using yet

A few of the marquee items haven’t earned my trust in this release.

The new JSON_TABLE is standards-compliant SQL/JSON and lovely on paper. In practice, I find jsonb_to_recordset plus explicit casts is still less surprising. I’ll revisit in 18.

COPY ... ON_ERROR ignore looks great for ingest pipelines, but it silently skips bad rows. Use it with LOG_VERBOSITY verbose and a sink for the rejected rows, or you’ll lose data quietly.

The vacuum memory rework dropped the per-table maintenance_work_mem ceiling, which is a real win on wide indexes. The new dead_tuple_storage_type is automatic. Don’t fiddle with it.

Gotchas

A few things I tripped over during the upgrade. Each one cost me at least an hour.

• Extensions need rebuilds. pgvector users must be on 0.7.4 or newer compiled against 17 headers. The old binary loads but throws on HNSW operations.
• pg_stat_statements view changed. New columns for JIT and parallel workers mean dashboards built on SELECT * break. Pin the column list.
• PgBouncer compatibility. PgBouncer 1.23 supports 17, but if you’re still on 1.19 the scram-sha-256 channel binding negotiation fails silently. Upgrade the pooler first.
• pg_dump from 17 to 16 doesn’t roundtrip. New default --no-sync for parallel dumps is fine, but MERGE with RETURNING won’t parse on the older target.
• Planner reset. The new I/O streaming changes seq scan costs slightly. Run ANALYZE and re-check any plans where you set enable_seqscan = off as a hack. You probably don’t need the hack anymore.

The single biggest mistake I see is upgrading the server before the surrounding tooling. Get pgBackRest 2.53, PgBouncer 1.23, and your extensions sorted on a staging cluster first.

Wrapping Up

Postgres 17 isn’t a revolution, but it’s the most useful single-version jump since 14 added the lz4 toast compression and 16 added logical replication on standbys. The streaming I/O and incremental backup work in particular will quietly save you money on storage and time on incident response.

If you’re sitting on 16 with a healthy cluster, plan the upgrade for Q1 2025, not now. Let the 17.1 and 17.2 patch releases shake out the long tail. If you’re on 14 or earlier, the math is different — every quarter you skip costs you in extension support and security backports. Read the release notes, build a staging cluster, run your real workload against it for a week, then schedule the cutover.

Postgres rewards operators who treat upgrades as a recurring engineering activity, not an emergency.