PostgreSQL 9.6 parallel query reaches Amazon RDS

A reporting query can spend most of its time reading a large table and calculating a small result. Giving the server more processors does not help that query unless the database can divide its execution between processes. PostgreSQL 9.6 introduces that capability, and Amazon RDS now offers version 9.6.1. For applications on RDS, the release makes the new execution model available through a managed database upgrade.

The announcement also includes phrase search and support for the bloom and pg_visibility extensions. These are upstream capabilities becoming available in the service. The distinction matters when planning adoption. An application can depend on PostgreSQL behavior while still waiting for its hosting service to expose the corresponding version. I default to PostgreSQL for relational work, so availability in a managed service matters to me when it expands the deployment choices for the same database.

How a query divides its work

The parallel query documentation shipped with PostgreSQL 9.6.1 describes a partial plan. Running an ordinary scan independently in several processes would duplicate the result. A partial plan divides the required output so that each row comes from exactly one participating process. In this release, the scan that drives a parallel plan is a parallel sequential scan. Cooperating processes receive table blocks individually and inspect the tuples on their assigned pages.

A Gather node joins the parallel portion to the rest of the plan. The session process requests background workers when execution reaches that node. Each worker executes the child plan, while the session process reads their output. That session process, called the leader, can also execute the parallel work. Its contribution depends on how much time it must spend collecting rows and processing operations above Gather.

Aggregation explains why the small-result reporting query is a useful candidate. Each process can calculate a partial count over its assigned rows. The leader then combines those counts into the final result. Grouped aggregation follows the same general arrangement, with partial results for each group. The database still has a final aggregation step, but fewer rows need to pass through it when the earlier work reduces a large input.

The distinction between reading rows and returning rows limits the benefit. A query that returns most of a table can occupy the leader with collecting worker output. A client that consumes those rows slowly introduces another limit. Parallel execution helps most when substantial work happens below Gather and produces relatively little data for the leader to collect. It does not make every query use the available processors efficiently.

Eligibility is part of the execution model

The same versioned documentation requires a positive max_parallel_workers_per_gather setting and dynamic shared memory. The total background worker limit can prevent a query from obtaining all its planned workers. A parallel plan can therefore execute with fewer workers than EXPLAIN anticipated, including none. The plan describes an execution strategy, while worker availability determines how much parallel execution actually occurs.

PostgreSQL 9.6 also excludes queries that write data or lock rows. Serializable transactions cannot use parallel query in this implementation. A cursor that can suspend execution prevents a parallel plan, and a function marked PARALLEL UNSAFE disables parallel execution for the query containing it. User-defined functions receive that classification by default because the planner cannot infer every operation their implementations might perform.

These restrictions give a concrete explanation for differences between superficially similar queries. A read-only aggregate and a statement that modifies data inside a common table expression do not have the same eligibility. An application function can also prevent parallel planning even when its surrounding SQL looks suitable. I value the explicit plan representation here because it makes the execution boundary inspectable. Gather identifies where worker output enters the session process, and the function classifications explain some otherwise surprising absences.

The documentation provides EXPLAIN with ANALYZE and VERBOSE as a way to see per-worker execution statistics. That information helps distinguish uneven work distribution from a plan that simply obtains fewer workers. It also keeps the discussion tied to the query actually executed, including the operations the leader must finish after collecting results.

Phrase search preserves word positions

The text search documentation in the 9.6.1 source tree describes another change with application consequences. A query that requires two lexemes anywhere in a document expresses a different condition from a query that requires them next to each other. The FOLLOWED BY operator supplies ordering and adjacency. Phrase search can therefore distinguish a phrase from the same vocabulary appearing elsewhere in a document.

The versioned documentation demonstrates the distinction with a pair of queries. The first matches, while the second does not, because the requested order and adjacency are absent.

SELECT to_tsvector('fatal error')
       @@ to_tsquery('fatal <-> error');

SELECT to_tsvector('error is not fatal')
       @@ to_tsquery('fatal <-> error');

The more general distance operator requires a specified difference between lexeme positions. That matters because text search configurations remove stop words. The phraseto_tsquery function accounts for those removed words when constructing its query, preserving the distance required by the original phrase. Normalization still applies, so this remains text search over lexemes rather than a literal substring comparison.

This is a useful extension for applications whose relational data already includes searchable text. The matching condition becomes more precise without requiring the application to interpret a bag of matching words as a phrase. My preference is to keep that distinction explicit in the database query whenever the product requires ordered terms. A conjunction cannot express the same condition.

The managed upgrade has its own sequence

The RDS announcement allows an existing PostgreSQL 9.5 instance to upgrade to 9.6.1 through the console. Instances on 9.3 or 9.4 must proceed through the intervening major versions until they reach 9.5. Each upgrade operation includes a period of database unavailability. That sequence affects the maintenance plan independently of the SQL capabilities available afterward.

The upstream 9.6.1 release notes, dated October 27, also explain why the patch version matters. They fix free space and visibility map logging problems during recovery, along with visibility map conversion errors in pg_upgrade. The RDS announcement makes the corrected upstream version available alongside the major release capabilities. For an RDS user, adopting parallel query therefore involves a specific engine version and an explicit service upgrade path, with separate constraints on which queries can use workers once the upgrade finishes.