Understanding Citus Distributed PostgreSQL: Query Processing Architecture

Cluster Architecture

A Citus cluster consists of one coordinator instance and multiple worker instances. Data is sharded and replicated on the workers, while the coordinator stores metadata about those shards. All queries are received by the coordinator, which splits each query into fragments that can run independently on the appropriate shards, assigns the fragments to workers, supervises execution, merges the results, and returns the final result to the client.

Distributed Query Planner

The planner receives the incoming SQL, builds a plan tree, and rewrites it into a distributable form to enable parallel execution and minimise network I/O. For a SELECT it creates two parts: a coordinator‑side fragment and worker‑side fragments that run on each shard. It then assigns the worker fragments to the workers and passes the distributed plan to the executor.

When a query references a distribution column, the planner extracts the column value, looks up the shard metadata, rewrites the SQL to point at the specific shard table, and routes the command to the correct worker.

Subquery/CTE Push‑Pull Execution

If a subquery (e.g., one containing LIMIT) cannot be inlined with the outer query, Citus executes the subquery on the workers, pushes the intermediate result back to the coordinator, and then pushes it again to the workers for the outer query. This push‑pull mechanism enables support for more complex SQL constructs.

Example: to find the number of distinct host IPs on the top‑20 pages by view count, the following query is used:

SELECT page_id, count(distinct host_ip)
FROM page_views
WHERE page_id IN (
  SELECT page_id
  FROM page_views
  GROUP BY page_id
  ORDER BY count(*) DESC
  LIMIT 20
)
GROUP BY page_id;

The LIMIT forces the subquery to be executed separately; Citus plans the subquery, pushes its result to all workers, runs the outer aggregation, and finally pulls the combined result.

EXPLAIN Walkthrough

Running EXPLAIN on the above query yields a plan that can be broken down step by step. The root node on the coordinator is a GroupAggregate that first sorts the incoming rows. Below it is a Custom Scan (Citus Adaptive) representing the distributed subplan. The subplan contains a Limit, a Sort, and a HashAggregate. The executor launches 32 worker tasks (one per shard). Each task performs a Seq Scan on its local shard, aggregates the results, and returns them to the coordinator, which then performs a final Hash Join with the intermediate results read via the read_intermediate_result function.

The function retrieves the intermediate result files that were replicated from the coordinator to the workers.

PostgreSQL Planner and Executor on Workers

Once the distributed executor sends a fragment to a worker, the worker runs it using the regular PostgreSQL planner and executor, choosing the optimal local plan for the shard table. The PostgreSQL planner and executor then return the fragment result to the distributed executor, which performs any final aggregation on the coordinator.

Further Reading

https://mp.weixin.qq.com/s?__biz=MzA4Mzc4NTE5MQ==∣=2692295989&idx=1&sn=8c446729a538b7838f512c535f05d13a&scene=21#wechat_redirect

https://mp.weixin.qq.com/s?__biz=MzA4Mzc4NTE5MQ==∣=2692295995&idx=1&sn=7336194782e023f19aa3d36a9fc30861&scene=21#wechat_redirect

https://mp.weixin.qq.com/s?__biz=MzA4Mzc4NTE5MQ==∣=2692296005&idx=1&sn=51658e78827e9872578043d5ea21037e&scene=21#wechat_redirect