What Is New in PostgreSQL 9.6
PostgreSQL 9.6 delivers major performance gains for both read and write workloads, alongside significant enhancements to parallel query execution and full-text search capabilities.
| Category | Key Changes |
|---|---|
| Performance | Parallel Query, Multiple Synchronous Standbys, Faster Vacuum |
| Features | Phrase Search, pg_stat_activity wait events, Remote Apply sync |
| Improvements | PostgreSQL FDW pushdown, postgres_fdw enhancements |
| Monitoring | Better visibility into locking and query states |
How does parallel query work in PostgreSQL 9.6?
This release introduces the foundational framework for parallel query execution. It allows certain operations like sequential scans, aggregates, and joins to be distributed across multiple background worker processes. This is a game-changer for data warehousing and analytical queries on large datasets.
The planner can now create plans that use multiple CPUs to execute a single query. You control this behavior with new parameters like max_parallel_workers_per_gather and parallel_setup_cost. In practice, this means queries that once took minutes can now complete in seconds on a multi-core machine.
What are the benefits of multiple synchronous standbys?
PostgreSQL 9.6 lifts the long-standing limitation of having only one synchronous replica. You can now configure multiple standbys using the new synchronous_standby_names syntax with keywords like FIRST and ANY.
This matters for high availability configurations. The FIRST option lets you prioritize a specific standby order, while ANY allows a transaction to commit as soon as it's replicated to any number of nodes from a list. This provides much more flexibility in designing robust, fault-tolerant systems without sacrificing write performance.
How is full-text search improved?
Full-text search gets a major usability boost with phrase search support. You can now use the tsquery operators <-> and <N> to find words that are a specific distance apart.
For example, the query to_tsquery('hot <-> dog') will match "hot dog" but not "dog hot". This eliminates a lot of the manual work previously required to achieve precise phrase matching, making PostgreSQL's full-text capabilities more competitive with dedicated search engines for many use cases.
What monitoring enhancements were added?
Database administrators gain significantly better insight into what their database is doing. The pg_stat_activity view now includes a wait_event_type and wait_event column, showing exactly what a process is waiting on (e.g., a lock, I/O, latch).
This is invaluable for troubleshooting performance bottlenecks. Instead of guessing why a query is hanging, you can now directly see if it's blocked by a heavyweight lock, waiting for disk I/O, or contending for some other resource.
What's new for foreign data wrappers?
The postgres_fdw driver received substantial optimizations for working with remote tables. The most significant is join pushdown: the local PostgreSQL server can now send entire join operations to the remote server for execution.
This drastically reduces the amount of data that needs to be transferred over the network. Instead of pulling two entire tables locally to perform a join, only the final result set is sent back. For distributed database setups, this can lead to order-of-magnitude performance improvements on join-heavy queries.
FAQ
Does parallel query work automatically, or do I need to tune it?
It works automatically for eligible queries, but you must tune it. The planner decides to use parallelism based on cost settings like parallel_tuple_cost and min_parallel_relation_size. You'll get the best results by adjusting these parameters for your specific hardware and workload.
What's the practical difference between FIRST and ANY for synchronous replication?FIRST (priority-based) ensures transactions are replicated to standbys in a strict order you define. ANY (quorum-based) allows a commit once the write reaches any N standbys from your list, which can offer lower latency if your network has variable performance between nodes.
Can I use the new phrase search with existing tsvector columns?
Yes, the phrase search functionality works with existing tsvector data. You don't need to reindex your documents; you can start using the new <-> operators immediately on your current indexes.
How do I see what my queries are waiting for?
Query the upgraded pg_stat_activity view. The new wait_event columns will show details like 'Lock' and the specific lock type, or 'IO' and the data file, giving you a direct line of sight into query contention.
Is postgres_fdw join pushdown enabled by default?
Yes, but the foreign server must be PostgreSQL 9.6 or later. The local planner will automatically consider pushing down joins when it estimates a performance benefit. You can control this with the enable_join_pushdown configuration parameter.