Citus 7.2 broadens SQL across distributed tables

A query that works for one customer's records can become a different engineering problem when it must compare every customer. The SQL may look much the same, but its intermediate results no longer belong to one machine. Citus 7.2 addresses that problem by broadening the queries it can execute across distributed data. The January 26 announcement identifies analytics and multi-tenant applications as the main beneficiaries.

The distinction matters because SQL compatibility has several meanings. Accepting PostgreSQL syntax does not establish that a distributed executor can run every combination of operations. A query can require information that another part of the query has yet to compute elsewhere. Application code then acquires responsibility for staging results, coordinating separate requests, or reformulating the question. Broader execution support removes some of that work without requiring the application to abandon its relational model.

I default to PostgreSQL for relational systems, and its extension interface is one reason for that preference. Citus makes the extension model relevant to a difficult question about growth. The interesting change here is how much of an application's existing query structure can survive when the data stops fitting a single execution location.

Intermediate results become part of the query

Ozgun Erdogan's release explanation describes expanded CTE and subquery support, including joins between non-colocated subqueries. It also discusses set operations and joins between local and distributed tables through a CTE. These are useful together because they let one stage of a relational calculation supply another stage inside the same SQL statement.

Consider a dashboard asking how many distinct sessions visited the most popular pages. The question has two different aggregations. First it needs the pages with the most visits across the dataset. Then it needs a distinct-session count restricted to those pages. Selecting a separate top group on each shard would answer a different question. A page that ranks below the cutoff everywhere might still qualify when its visits are combined.

That example explains why cross-shard query support cannot be assessed by counting supported keywords. Both stages can use familiar aggregation syntax while their combination requires coordination. The application benefits when the database accepts the complete question. The SQL itself then records which intermediate result determines the filter, which makes the dependency available to someone reading the query.

For a visits relation with page_id and session_id columns, the release article gives this query shape. The inner query ranks pages by visit count, while the outer aggregate counts distinct sessions for the selected pages.

SELECT page_id, count(DISTINCT session_id)
FROM visits
WHERE page_id IN (
  SELECT page_id
  FROM visits
  GROUP BY page_id
  ORDER BY count(*) DESC
  LIMIT 25
)
GROUP BY page_id;

The same reasoning applies to a CTE used in several parts of a statement. A named intermediate result can clarify what the query means. Its name does not prove that execution is cheap, or that the result is small. Query readability and execution cost remain separate properties, even when improved compatibility makes it possible to express the calculation directly.

Compatibility reduces application state

An application-side workaround often introduces a second representation of the query's intermediate results. Someone has to decide how to represent those results, how to pass them into the next request, and what to do if that request fails. Those decisions become application behavior. They also make it harder to compare the implementation with the original relational question.

Support for a larger statement can therefore reduce code beyond the SQL itself. It can eliminate a reason to build a temporary orchestration path around one unsupported query shape. This is an architectural consequence of broader query execution, not a claim that every application will delete a particular number of lines. The benefit depends on which workarounds the application actually needs.

I find this more persuasive than a general promise of transparent scale. A concrete query shape establishes something that can be inspected. A statement that joins two calculated results has a clear meaning, and its execution plan can be considered against that meaning. Compatibility claims become useful when they identify such cases precisely.

Local data also complicates the idea that a distributed database contains only distributed tables. An application may have a small relation that does not justify the same placement policy as a large event table. Requiring every relation to adopt the same storage strategy would make query convenience dictate data organization. Supporting interaction between different kinds of tables gives the schema designer more room to make that decision independently.

Data placement still affects the question

The release also integrates pg_partman for partition maintenance and supports PostgreSQL 9.6 and 10. That combination connects the query work to the lifecycle of the underlying data. Broader SQL support helps express questions over retained records. Partition management determines which records remain available and how much recurring work retention creates.

Distribution and retention solve different problems. Dividing records among machines changes where work occurs. Dividing a history into time periods gives administrators a unit for managing that history. An application with an ever-growing event stream can need both, even when its queries continue to present one logical relation to the developer.

My interest in this release is therefore quite specific. It gives PostgreSQL users more ways to retain relational expressions as their deployment becomes distributed. That preserves an important property of SQL, the ability to describe related calculations together. It also leaves an ordinary engineering question to answer for each workload, how much intermediate information those calculations need to exchange.

For a cross-customer report, that exchange follows from the question itself. A global ranking needs enough information to distinguish globally frequent values from locally frequent ones. Citus 7.2 expands the set of cases in which the database can manage that dependency within the submitted statement, reducing the need for application code to encode the query's stages separately.