PostgreSQL 12 gives the planner more freedom around WITH queries

A named intermediate query can make SQL easier to follow while preventing the planner from using a restriction where it would do the most good. PostgreSQL 12 changes that relationship for eligible common table expressions. A WITH clause no longer necessarily requires a separately evaluated intermediate result.

The October 3 release announcement also introduces SQL/JSON paths and stored generated columns. These additions concern different workloads, but each gives the database a more explicit account of an expression. The planner can combine an eligible CTE with its parent query, the JSON path engine can traverse a document, and a generation expression can define a value maintained with its row.

I default to PostgreSQL for relational systems because it provides substantial control without requiring every rule to live in application code. The CTE change interests me most in this release. It reduces the chance that a choice made to organize a query also imposes an unnecessary execution boundary.

A single reference can become one optimized query

The query documentation shipped in 12.0 defines the eligibility conditions. A non-recursive, side-effect-free CTE can be folded into the parent query. Here, side-effect-free means a SELECT containing no volatile functions. The default is to fold it when the parent references it once.

Consider this form, adapted from the manual's example. Assume big_table has a column named key.

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;

PostgreSQL 12 can fold that query into the equivalent direct selection on big_table. The parent restriction can then participate in planning the table access. The manual notes that an index on key will probably be used to retrieve just the matching rows.

The benefit comes from joint optimization, not from removing the spelling WITH. Keeping the intermediate query separate prevents some restrictions from reaching the underlying table scan. Combining the query levels lets the planner consider the condition where it can reduce work earlier.

This default does not apply to every CTE. Recursive queries have different evaluation requirements, and volatile functions can make repeated or reordered evaluation change behavior. Data-modifying statements are also outside the side-effect-free selection described by this rule.

The conditions are therefore part of the feature's meaning. A claim that PostgreSQL 12 always inlines WITH queries would erase the distinction that makes the optimization safe. The database is changing execution freedom for a defined class of queries.

Repeated references preserve a different tradeoff

When the parent references a CTE more than once, the default remains separate evaluation. The manual explains why. An expensive computation can be performed once and reused, and a restriction useful to one reference might incorrectly remove rows needed by another reference.

PostgreSQL 12 adds explicit controls for this decision. MATERIALIZED forces separate calculation. NOT MATERIALIZED requests merging into the parent for an eligible CTE, accepting the risk of duplicate computation.

The manual's self-join example shows where merging can help. A multiply referenced CTE selects an entire table, then one reference has a restrictive condition. If materialized, the query creates an intermediate copy and joins that result to itself without the original table's index. Merging can let restrictions reach scans of the underlying table.

The opposite case contains an expensive function computed in the CTE and used by two references. Separate evaluation avoids calling it twice for each row. The useful choice depends on whether avoiding duplicate computation is more valuable than pushing restrictions into each use.

I prefer this explicit control to treating CTE syntax as an accidental tuning mechanism. The default handles a common case, and the two keywords document an intentional execution choice. Reading the query can then reveal whether intermediate-result retention is required for a specific reason.

The change also makes execution plans relevant when upgrading existing queries. A query can retain its result semantics while receiving a different plan. The release gives the planner a new legal transformation, so the presence of a familiar WITH clause no longer establishes the same optimization boundary.

Generated columns retain computed values in the row

Stored generated columns address another kind of intermediate result. The 12.0 data-definition documentation says PostgreSQL implements the stored form, computed when a row is inserted or updated. The computed value occupies storage like an ordinary column.

The following complete table definition uses the conversion shown in that documentation.

CREATE TABLE people (
    height_cm numeric,
    height_in numeric
        GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

A generated column differs from a default. A default supplies an initial value when an insert does not provide one. The generation expression follows changes to the row, and the caller cannot override it with an independently supplied value.

The expression is restricted to immutable functions and the current row. It cannot contain subqueries or refer to another generated column. These restrictions define a value that the database can maintain from the row's own contents, without consulting an independently changing dataset.

PostgreSQL 12 does not implement virtual generated columns, which would compute their values on reading. The STORED keyword is required. That means the feature makes a deliberate storage and write-time computation choice, rather than simply introducing another spelling for a query expression.

The documentation also explains trigger ordering. Generated values conceptually update after BEFORE triggers, so modifications those triggers make to base columns affect the generated value. Conversely, a BEFORE trigger cannot access the generated columns. That ordering matters when moving a derivation from application code or a trigger into the schema.

JSON paths describe traversal within a document

The SQL/JSON path documentation at 12.0 describes a path engine evaluating expressions against JSON data. PostgreSQL represents these expressions with the jsonpath type. Evaluation can produce a sequence of SQL/JSON items.

The dollar sign identifies the document being queried. Accessors move through object members and array elements, while filter expressions select items at a particular stage. The documentation's tracker example uses a path such as $.track.segments[*].HR to retrieve heart-rate values from all segments.

Filter placement changes the question. Filtering the heart-rate value selects particular values. Filtering the segment before retrieving its start time selects a different field from qualifying segments. That gives the path expression enough structure to describe document traversal without extracting every intermediate object into application code.

The path language uses three-valued logic. A filter can evaluate to true, false or unknown, and further evaluation retains only items for which the filter is true. This is a concrete semantic detail for documents with unexpected contents, rather than a promise that every missing field behaves like an ordinary false comparison.

There are also two modes for structural errors. Lax mode is the default and adapts some mismatches through wrapping or unwrapping, suppressing remaining structural errors into empty sequences. Strict mode raises an error when the path does not match the document structure.

Those modes express different expectations about input shape. Lax traversal is useful when documents vary, while strict traversal can expose a structural assumption that is false. The manual records limits, including the absence of the .datetime() method in this release, so SQL/JSON support should be understood through the implemented language.

Extensibility and maintenance change alongside query execution

PostgreSQL 12 also introduces a pluggable table storage interface. The release announcement describes it as allowing developers to create their own storage methods. That is an extension point, not evidence that this release has replaced the default storage of every existing table.

Operational additions include REINDEX CONCURRENTLY, which permits rebuilding indexes without blocking writes to the table. The pg_checksums command can enable or disable page checksums in an offline cluster. Previously, the announcement notes, enabling them was a cluster-initialization choice.

The release also extends CREATE STATISTICS with multi-column most-common-value statistics. The announcement describes these as helping queries involving columns with nonuniform distributions. This addresses the information available to the planner, separately from the transformations permitted around a CTE. A planner can have freedom to combine query levels while still needing representative statistics to estimate the resulting work. PostgreSQL 12 changes both parts of that process, through CTE folding and through a richer account of commonly occurring combinations of column values.

These changes address existing operational constraints while the query features add expressiveness. My preference remains to use database-maintained rules where their semantics match the application. In PostgreSQL 12, stored generated columns provide a row-local derivation with explicit restrictions, while CTE controls preserve a way to choose separate calculation when the cost of repeating it exceeds the value of joint optimization.