PostgreSQL 14 adds multiranges and explicit query pipelines

A single time range cannot represent a schedule with a gap. Sending one database request and waiting for its result before sending the next also leaves a gap, this time in network use. PostgreSQL 14 addresses both problems with separate features. Multiranges add a representation for disconnected intervals, and libpq pipeline mode lets a client send work before reading the preceding result.

The September 30 release announcement presents both changes alongside JSON subscripting and operational improvements. Their useful connection is that each gives an existing application pattern an explicit database interface. The details of that interface determine what application code can stop doing and what it still has to manage.

I default to PostgreSQL for relational data partly because its type system can express more than scalar fields. Multiranges fit that preference. Pipeline mode interests me for a different reason, since it exposes the network scheduling decision without pretending to remove the client's responsibility for transaction outcomes.

A multirange preserves the gaps

The range documentation in the 14.0 release describes a multirange as an ordered list of noncontiguous, nonempty, non-null ranges. Every range type has a corresponding multirange type. The built-in set includes integer, numeric, date and timestamp variants.

A range represents the values between its bounds. Widening a range to encompass two separated intervals includes the values in the gap as well. A multirange can retain both intervals without asserting that the intervening values belong to the set.

The documented literal syntax uses braces containing comma-separated ranges. This small integer example comes directly from the shape supported by the release documentation. The lower bounds are inclusive and the upper bounds are exclusive.

SELECT '{[3,7), [8,9)}'::int4multirange;

The value includes the integers beginning at three and ending before seven, together with the interval beginning at eight and ending before nine. Seven remains outside the value. The same notation therefore communicates the discontinuity that one enclosing range would erase.

An empty multirange is represented by empty braces. That is a defined value of the multirange type. The documentation's separate prohibition on null constituent ranges prevents confusing an absent range with an interval that contributes no members.

The syntax resembles an array, but the reference explicitly distinguishes the simpler multirange form. It has one dimension, and its contents do not require array-style quoting. Individual range bounds can still require quoting when their text contains characters meaningful to range syntax.

Most range operators also work on multiranges. This preserves a connection with PostgreSQL's existing range model, including the importance of bound inclusivity. Applications working with availability windows or discontinuous numeric intervals can express the set directly instead of treating an enclosing interval as a complete description.

JSON access also gains a common notation

PostgreSQL 14 adds subscripting for jsonb. The announcement gives a nested access example that reads a release value from an object. Its syntax makes the sequence of object keys explicit.

SELECT ('{"postgres": {"release": 14}}'::jsonb)
       ['postgres']['release'];

The announcement describes a general subscripting framework that can extend to other nested structures. The same release applies it to hstore. This is a change in access notation, with a framework behind it, rather than a claim that PostgreSQL has only now acquired JSON storage.

Keeping those distinctions clear helps when reviewing an upgrade. A new notation can simplify application expressions without changing the representation used in existing columns. Multiranges, in contrast, add a type for a value whose disconnected structure an individual range cannot express.

Pipelining changes when the client waits

The libpq documentation at the 14.0 tag defines pipeline mode as sending a query without first reading the result of the previous query. Multiple requests and results can then share network activity. The release announcement identifies high-latency links and many small writes as workloads that can benefit.

The feature belongs to the client library. It works with servers that support the version-three extended query protocol, so it does not require a PostgreSQL 14 server. Using the new client API and upgrading the server are separate adoption decisions.

The server executes statements in the order the client sends them. It starts executing before the end of the pipeline arrives. Pipelining therefore does not mean simultaneous execution of all queued statements on that connection.

That ordering allows a later statement to depend on an earlier statement's database effects. The documentation gives creating a table before another statement uses it as an example. The client need not wait for the first result merely to preserve the server's execution order.

For example, a later request can already name a table created by an earlier request. A client that needs an earlier query's returned identifier to construct its next statement has a different dependency. Server-side ordering does not make that identifier available in client memory before the response arrives.

It still needs the earlier result when its own computation depends on returned data. Pipelining cannot supply an application with a value that has not arrived. The useful opportunity is to queue operations whose next request can already be constructed.

The client tracks the pending work

An application enters pipeline mode with PQenterPipelineMode. Only asynchronous operations are permitted there. Synchronous execution functions such as PQexec are errors, and COPY is disallowed.

The API adds work to both sides of the connection. The reference warns of greater memory consumption on client and server, with send and receive queue management providing a way to limit it. It recommends nonblocking mode because a blocking client can become stuck sending while the server is stuck sending results back.

This is a concrete reason to interleave dispatch and result processing. A client that keeps adding requests without consuming responses can fill buffers on both sides. Removing a wait after every statement does not remove the need for flow control.

The application must also associate results with the requests it sent. libpq does not identify the current query for that purpose. The reference suggests a state machine or FIFO queue. A null result from PQgetResult marks the transition between query-result sequences, after which the client continues collecting the next query's results.

I regard that bookkeeping as part of the feature's cost. The network benefit is useful when the workload has enough independent requests to queue. A driver that exposes the API must preserve the request-to-result association even when some queued statements never execute.

Synchronization bounds error recovery

PQpipelineSync establishes a synchronization point. If a statement fails, the server aborts the current transaction and skips subsequent queued commands until the next synchronization point. Those skipped commands produce PGRES_PIPELINE_ABORTED results.

The original failing operation produces PGRES_FATAL_ERROR. PGRES_PIPELINE_SYNC then marks the synchronization result and the end of that aborted pipeline segment. The client must continue processing results during recovery, since an error does not empty its result queue for it.

Transaction structure determines what remains committed. For an implicit transaction, the already-executed operations roll back. If the pipeline contains several explicit transactions, transactions committed before the error remain committed, while the current transaction aborts and subsequent queued work is skipped.

A synchronization point does not itself repair an explicit transaction left in an aborted state. The next command needs to return the transaction to normal operation with ROLLBACK. Otherwise, the next pipeline immediately becomes aborted too.

These rules make the synchronization point useful as an error boundary while keeping it distinct from an application-level unit of work. The client has to choose where boundaries belong and interpret the resulting transaction state. A batch of submitted requests is not enough information to decide which effects exist.

A sent commit still needs its result

The documentation explicitly warns against treating a transmitted COMMIT as proof of commitment. Confirmation arrives in its result. Because errors arrive asynchronously, the client needs to distinguish the last confirmed commit from work sent after it.

This matters most when the application uses several explicit transactions in one pipeline. A later error does not undo transactions whose commits already completed. Conversely, the presence of a COMMIT request in the outgoing queue does not establish that it executed.

Multiranges make a data distinction explicit, preserving the intervals and gaps in a single typed value. Pipeline mode makes request scheduling explicit while retaining statement order and transaction rules. For a pipeline client, the durable progress it can report depends on the commit results it has received and the synchronization results it has drained.