Module 05 — Query Routing: How Citus Executes SQL¶
Reading a Citus EXPLAIN is the core skill of this course. Two plan shapes
matter.
Router (single-shard) query¶
Custom Scan (Citus Adaptive)
Task Count: 1
Tasks Shown: All
-> Task
Node: host=worker1 port=5432 dbname=fleet
-> Bitmap Heap Scan on vehicles_10203x ...
fleet_id is the distribution column ⇒ hash(1) names exactly one shard ⇒
one task on one node. Task Count is the tell. Latency ≈ a single-node query
on 1/8th of the data.
Scatter-gather (fan-out) query¶
Same query when vehicles was distributed by vehicle_id:
Custom Scan (Citus Adaptive)
Task Count: 8
Tasks Shown: One of 8
-> Task ... (one task sent to every shard)
fleet_id isn't the shard key ⇒ every shard might hold a matching row ⇒ one
task per shard, results merged on the coordinator. Cost ≈ max(shard latencies)
+ merge — and it burns CPU on every worker for every request.
Tasks Shown: One of N means Citus printed one representative task; the other
N−1 are structurally identical.
Where the work happens¶
| Query shape | Runs on workers | Runs on coordinator |
|---|---|---|
| Router | everything | pass-through |
| Scatter-gather + aggregate | per-shard partial count/sum |
final merge |
| Co-located join | entire join inside one task | pass-through |
| Non-co-located join | fetch shards | move data, local join, or repartition subplans (Distributed Subplan) |
| Cross-shard UPDATE/DELETE | per-shard | must touch multiple shards → restrictions apply |
Rule of thumb: the more the plan looks like one task doing real Postgres work on one worker, the better your shard key serves that query.
Executors (a note on names)¶
Older docs distinguish the router executor (single-shard) from the
real-time/adaptive executor (multi-shard, parallel). Citus 12+ plans both
under Custom Scan (Citus Adaptive) — the Task Count is what you read.
Co-located joins¶
When fleets ⋈ vehicles share colocation_id and join USING (fleet_id):
Task Count: 1
-> Task
Node: host=worker1
-> Nested Loop
-> Index Scan on fleets_102016
-> Bitmap Heap Scan on vehicles_102032
The coordinator ships the whole join to the worker; shard 102016 of fleets
and shard 102032 of vehicles are the same bucket of fleet ids on the same
machine. Zero data movement.
Transactions across shards¶
Single-shard statements keep normal Postgres semantics. Statements touching multiple shards run with one connection per node; multi-statement transactions spanning shards are coordinated with two-phase commit (2PC) to stay atomic — supported, but slower and with more failure modes. Design so your hot transactions stay single-shard.
Quick check¶
A query has WHERE fleet_id = 42 on a table distributed by fleet_id. How does Citus execute it?
What keeps a transaction spanning multiple shards atomic in Citus?