PostgreSQL 17: optimisations that change real queries

Actualizado: 2026-05-03

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.

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 several contiguous 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 typically run in overnight windows.

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 often 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 many typical reporting or set-difference queries. It is worth reviewing plans after migrating.

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 (typically delete or mark them). This extends MERGE to cover the full bidirectional-sync pattern that previously required several 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.

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. 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.

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 very 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.