PostgreSQL 17 shipped on 26 September 2024 and, as usual, the serious content of the release sits in quiet planner and execution improvements. Six months into production, it is clear which optimisations have changed real queries on mixed OLTP and reporting databases. This post focuses on query-plan improvements, not admin or backup.

Key takeaways

  • SAOP scans combine IN (list) values into a single index pass, yielding 30–50 % p99 latency gains for lists of 20–100 IDs.

  • Streaming I/O cuts sequential-scan and ANALYZE time on cold large tables by 15–40 %.

  • NOT IN and NOT EXISTS are automatically transformed to hash or merge anti-joins when statistics justify it.

  • MERGE gains a RETURNING clause and WHEN NOT MATCHED BY SOURCE, completing bidirectional-sync patterns.

  • TidStore reduces VACUUM time 20–35 % with the same memory configuration.

  • Logical replication supports failover without full resync.

SAOP scans: what other engines call skip scan

The most useful improvement I have seen in real queries is the change to scalar array operator scans (SAOP). SAOPs appear in queries with WHERE column IN (list) or WHERE column = ANY (array).

In PostgreSQL 16 and earlier, the planner ran a separate scan per list value. With WHERE user_id IN (1,2,3,...,10), that was ten index accesses even if the data was contiguous.

PostgreSQL 17 combines those values into a single index scan when distribution allows it. The planner recognises the pattern and emits one pass over the index that jumps between value ranges without restarting. In our user queries with 20 to 100 IDs, p99 latency improves 30 to 50 %, and CPU on the read node drops noticeably under load. As pganalyze’s write-up on Postgres 17’s B-tree scans[1] documents, the mechanism is the same: avoiding duplicate leaf-page access.

This change requires nothing in the application. Checking EXPLAIN ANALYZE confirms that the number of index accesses has dropped. It pairs well with the database-observability practices covered in FinOps for AI infrastructure.

Streaming I/O: sequential scans and ANALYZE

The other deep change is the streaming I/O layer. In earlier versions, sequential scans read blocks one by one. PostgreSQL 17 introduces an internal API that lets the engine announce it will read a contiguous range of blocks, which the kernel exploits for more aggressive prefetch.

Real impact depends on load:

  • Reporting with large sequential scans over cold tables: 15–25 % wall-time reduction.

  • ANALYZE on large tables: 20–40 % drop in statistics-recompute time.

  • Hot OLTP queries (data in cache): more modest improvement.

The ANALYZE improvement is especially valuable because it is a heavy task that tends to run in overnight windows. The PostgreSQL 17 release notes[2] list this read-ahead API among the internal execution-engine improvements.

NOT IN and NOT EXISTS as anti-joins

Another planner change that has moved the needle is the improved handling of correlated subqueries with NOT IN or NOT EXISTS. Earlier versions transformed only part of those cases and ended up in nested loops with O(n × m) cost. In PostgreSQL 17, more cases are automatically transformed into hash or merge anti-joins when statistics justify it.

On a daily reconciliation query, a SELECT ... WHERE id NOT IN (SELECT id FROM other_table WHERE condition) that took 18 seconds dropped to 2.5 seconds after migration. EXPLAIN shows an anti-join where there used to be a nested loop. The SQL was not changed.

This improvement touches the typical reporting or set-difference queries. It is worth reviewing plans after migrating. The development thread itself, «Converting NOT IN to anti-joins during planning»[3], explains why the transform was not previously safe when the subquery could return NULL.

MERGE with RETURNING and wider coverage

MERGE arrived in PostgreSQL 15 but had annoying limitations. Version 17 adds two useful things:

  • RETURNING clause. A MERGE can now return affected rows with information about which action applied (INSERT, UPDATE or DELETE) via merge_action(). It simplifies upsert cases where the application needs to know whether a row pre-existed.

  • WHEN NOT MATCHED BY SOURCE. Lets you define what to do with target rows that have no source equivalent: delete them or mark them. This extends MERGE to cover the full bidirectional-sync pattern that previously required chaining separate steps.

In practice, we rewrote an hourly catalogue sync, going from three statements (INSERT, UPDATE, DELETE) to a single MERGE. The logic is clearer and maintainability has improved noticeably. depesz’s blog keeps a collection of real merge_action() cases[4] that follow the same pattern.

VACUUM memory management

PostgreSQL 17 introduces a new internal structure (TidStore) for storing dead tuples during VACUUM. maintenance_work_mem stops being the bottleneck it used to be: with TidStore, index passes are reduced and memory use is more efficient.

On large tables, VACUUM time drops 20–35 % with the same memory configuration. This matters more in update-heavy systems where bloat accumulates. pganalyze’s breakdown of the new radix-tree VACUUM structure[5] confirms the drop in memory and time versus the previous structure. Related to Kubernetes 1.32 resource management, where resource control similarly impacts system reliability.

Logical replication with failover

In PostgreSQL 17, a subscriber can continue from a new publisher when the original fails, without a full resync. For teams using logical replication for live migrations or data pipelines, this is a structural change that makes practical what used to be painful. Decodable documents in detail[6] how the slot sync worker keeps primary and standby in sync without losing pending events.

How to think about the upgrade

PostgreSQL 17 is a worthwhile upgrade for mixed workloads with:

  • Large index scans (SAOP)

  • Historical reporting or set-difference queries (NOT IN / anti-joins)

  • Sync operations (MERGE)

  • Maintenance on large tables (VACUUM / ANALYZE / logical replication)

If your load is pure OLTP with simple queries, improvement is more modest, but there are no notable regressions that justify staying.

The most useful free change was the SAOP and NOT IN improvement. Both change plans for queries written the same way for years. Review EXPLAIN for your ten slowest queries after migrating: there is a decent chance one moved to a better plan and you can close a performance ticket without refactoring anything.

This article is also available in Spanish.

Frequently asked questions

Do I need to rewrite my queries to benefit from PostgreSQL 17?

No. The two most useful improvements, SAOP scans for WHERE column IN (list) and the transformation of NOT IN / NOT EXISTS into hash or merge anti-joins, are applied by the planner with no application changes. What is worth doing is reviewing EXPLAIN ANALYZE plans for your ten slowest queries after migrating: there is a decent chance one moved to a better plan and you can close a performance ticket without refactoring.

How much faster do queries with lists of IDs get in PostgreSQL 17?

For lists of 20 to 100 IDs, p99 latency improves 30 to 50 %, and CPU on the read node drops noticeably under load. Up to PostgreSQL 16 the planner ran a separate index scan per list value. Version 17 combines those values into a single pass that jumps between value ranges without restarting, avoiding duplicate access to index leaf pages.

Is upgrading to PostgreSQL 17 worth it for a pure OLTP workload?

The improvement will be more modest, but there are no notable regressions that justify staying. The gains are largest on mixed workloads: large index scans (SAOP), historical reporting with NOT IN, sync operations with MERGE (now with RETURNING and WHEN NOT MATCHED BY SOURCE) and maintenance on large tables. There, TidStore cuts VACUUM time 20–35 % with the same memory and streaming I/O speeds up ANALYZE.

Sources

  1. pganalyze’s write-up on Postgres 17’s B-tree scans
  2. PostgreSQL 17 release notes
  3. «Converting NOT IN to anti-joins during planning»
  4. collection of real merge_action() cases
  5. pganalyze’s breakdown of the new radix-tree VACUUM structure
  6. Decodable documents in detail