PostgreSQL 11 changes where database work is performed

An expensive database query can spend time on several different kinds of work. It may read data that a predicate could have excluded, repeatedly interpret an expression, or coordinate work that several processes could perform together. PostgreSQL 11 addresses these costs through different mechanisms. Its release makes more sense when those mechanisms are kept distinct than when they are combined into one promise of faster queries.

The October 18 announcement introduces LLVM-based just-in-time compilation, stored procedures with transaction management, and substantial partitioning improvements. It also expands parallel execution. These changes concern different workloads and require different conditions. A short transactional lookup has little in common with a long calculation over many partitions, even when both use the same SQL connection.

I default to PostgreSQL for relational systems because its behavior remains open to inspection. This release extends that property into new decisions about execution. JIT has cost thresholds, procedures have rules for transaction control, and partitioning has defined routing and indexing behavior. Those details determine which application designs the new capabilities support.

Expression compilation has a specific scope

The PostgreSQL 11 JIT explanation describes generation of native code for expression evaluation and tuple deforming. Expressions occur in filters, projections and aggregates. Tuple deforming extracts values from the stored row representation. Code specialized for the expression or row layout can remove work that general-purpose evaluation performs repeatedly.

This is a narrower claim than compiling the entire database system or every operation in a query plan. A query still needs its selected access method and its data. Generating machine code for a predicate does not change how many pages a poorly chosen scan reads. Understanding the scope helps relate the feature to a particular source of CPU work.

Inlining can reduce calls to small functions and operators, which is relevant to a database whose extensibility uses function calls throughout execution. LLVM can also optimize generated code. Both activities take time before the optimized work produces a benefit. The compilation decision therefore needs to consider how much execution is available to repay that initial cost.

The JIT decision documentation identifies long-running CPU-bound queries as the main beneficiaries. PostgreSQL compares estimated query cost with jit_above_cost. Separate thresholds govern inlining and more expensive optimization. These decisions occur during planning, and generic prepared plans retain the relevant planning-time choices.

That last point matters when investigating an apparent configuration mismatch. A setting changed at execution time does not necessarily reconstruct an already selected generic plan. The question is which settings controlled the plan being executed. The documented behavior makes the relationship between preparation and execution part of JIT diagnosis.

The feature also requires a suitable build, including LLVM support, and enabling jit. EXPLAIN reports whether it is used. For a session on a supported build, the enabling command is simply:

SET jit = on;

Enabling it does not require compilation for every statement. The cost decision still applies. That separation lets an installation make the implementation available while preserving the planner's threshold for choosing it. It also means that an absence of JIT in a small query is consistent with the feature working as designed.

Procedures introduce transaction boundaries inside server code

SQL functions have long provided server-side computation, but PostgreSQL 11 introduces procedures invoked with CALL. The PL/pgSQL transaction documentation permits transaction control in qualifying procedure and DO invocations. After a commit or rollback, a new transaction starts automatically. This gives server-side batch logic a way to retain progress in separate transactions.

For example, this small demonstration commits its first insertion before performing its second. It requires a top-level CALL outside an explicit transaction block, in an environment where the caller has the required creation permissions.

CREATE TABLE procedure_demo (value integer);

CREATE PROCEDURE insert_demo()
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO procedure_demo VALUES (1);
  COMMIT;
  INSERT INTO procedure_demo VALUES (2);
END;
$$;

CALL insert_demo();

The application consequence is a changed unit of rollback. Once the procedure commits its first transaction, a later failure cannot make the entire invocation behave like one atomic transaction. A caller needs to understand that partial progress is intentional. Putting the work inside one server-side procedure does not restore a single all-or-nothing boundary.

That also changes the meaning of retrying the invocation. If a batch has already committed some work, repeating it needs a rule for identifying that completed work. Otherwise, the procedure's transaction control can preserve progress while the caller duplicates it. The appropriate rule depends on the operation, such as whether an input record has a durable processing identity. Procedures supply the ability to commit a batch. They do not define an application's retry policy or determine which repeated requests represent the same intended work. Those semantics still need to be present in the operation's data model.

The documentation also restricts which call chains can perform transaction control. A chain of qualifying CALL invocations can do so, but an intervening SELECT function call prevents transaction control further down the chain. The distinction follows the invocation context, not merely the presence of COMMIT text in a procedure definition.

I find procedures useful when the desired transaction boundaries belong to the batch operation itself. Their value is not that they make every unit of work atomic. They let the operation express multiple units deliberately. That can make a bulk workflow easier to specify, provided its caller understands which progress survives a later error.

Partitioning acquires more relational behavior

The release notes add hash partitioning and a default partition. They also describe moving a row when an update changes its partition key. These changes address different parts of the table's lifecycle. Hash partitioning supplies another division rule, while a default partition handles values outside the other declared bounds.

Automatic row movement removes a reason for application code to implement partition relocation explicitly. The logical update can remain an update even when its physical destination changes. That does not make the placement policy irrelevant. It makes the database responsible for carrying out the policy represented by the partition definitions.

The partitioning documentation distinguishes an index declared on the partitioned parent from its physical indexes on individual partitions. A parent index describes the associated partition indexes. Global uniqueness therefore needs a partition key that is included in the relevant unique constraint, so rows that could conflict cannot evade comparison by belonging to different partitions.

That constraint is a useful reminder that a logical table and its physical organization remain connected. An application may want an identifier to be unique across every time period, while its partitioning rule separates records by time. The database cannot infer that a set of independent per-partition checks establishes an arbitrary global rule. The schema needs to express a rule that the implementation can actually enforce.

Partition elimination also extends into execution, as the release notes explain. Values unavailable during planning can become available later, allowing partitions to be excluded at that stage. This is particularly relevant to prepared queries and joins. The timing of available information determines when the executor can establish that a partition cannot contribute rows.

Different mechanisms remove different work

The release also permits parallel B-tree index construction and improves parallel hash joins using a shared hash table. Those changes address work distributed among processes. JIT specializes repeated evaluation within execution. Partition elimination can avoid work entirely when a partition is irrelevant. Keeping those explanations separate makes the release's performance changes more intelligible.

A workload can benefit from more than one mechanism, but that does not mean their benefits can be added mechanically. Avoiding rows reduces the expression evaluations remaining to compile. Parallel execution changes where the remaining work occurs. The useful explanation begins with what the plan must do for the application's query.

My interest in PostgreSQL 11 is that it expands the database's responsibility for work applications previously had to arrange themselves. Procedures can own batch transaction boundaries, and partitioned updates can own row relocation. At the execution level, optional compilation adds another strategy for repetitive CPU work. Each capability has a documented scope that can be related to the operation being designed.

Moving an existing installation to 11 still requires a major-version migration method, as the release notes state. That migration needs to preserve the schema rules on which the application depends, including the uniqueness rules of partitioned tables and the transaction expectations of callers adopting procedures. The new execution choices become useful within those explicit application semantics.