Citus Distributed PostgreSQL: SQL Support Limits and Practical Workarounds

Citus extends PostgreSQL to add distributed capabilities while remaining compatible with the PostgreSQL architecture, allowing users to leverage the full PostgreSQL ecosystem for distributed tables.

Citus guarantees 100% SQL coverage for queries that run on a single worker node, which is common in multi‑tenant applications when accessing data for a single tenant.

Most SQL features are supported across nodes, but certain operations are not, including: SELECT … FOR UPDATE works only on single‑shard queries TABLESAMPLE works only on single‑shard queries

Correlated subqueries are supported only when the correlation is on the distribution column

Distributed tables can be outer‑joined only on the distribution column

Outer joins between a distributed table and a reference or local table are supported only when the distributed table is on the outer side

Recursive CTEs work only on single‑shard queries

Grouping sets work only on single‑shard queries

Before applying workarounds, verify that Citus fits the use case; the current version targets real‑time analytics and multi‑tenant scenarios.

Using CTEs to Overcome Limitations

When a query is unsupported, wrapping the distributed part in a Common Table Expression (CTE) forces the query to be executed as a router query using the pull‑push mechanism.

SELECT * FROM ref LEFT JOIN dist USING (id) WHERE dist.value > 10;

The above fails with “cannot pushdown the subquery”. The CTE workaround rewrites it as:

WITH x AS (SELECT * FROM dist WHERE dist.value > 10)
SELECT * FROM ref LEFT JOIN x USING (id);

The coordinator sends the CTE result to all workers, so it is advisable to apply the most selective filters inside the CTE to reduce network overhead.

Temporary Tables: A Last‑Resort Solution

Some queries, such as grouping sets on a distributed table, remain unsupported even with CTEs. The article demonstrates a real‑time analytics tutorial using a github_events table distributed by user_id. An attempt to run a grouping‑set query fails:

SELECT repo_id, event_type, event_public,
       grouping(event_type, event_public),
       min(created_at)
FROM github_events
WHERE repo_id IN (8514, 15435, 19438, 21692)
GROUP BY repo_id, ROLLUP(event_type, event_public);

As a workaround, the data (minus the aggregate) is first materialized into a temporary table on the coordinator, and the aggregation is performed locally:

CREATE TEMP TABLE results AS (
  SELECT repo_id, event_type, event_public, created_at
  FROM github_events
  WHERE repo_id IN (8514, 15435, 19438, 21692)
);
SELECT repo_id, event_type, event_public,
       grouping(event_type, event_public),
       min(created_at)
FROM results
GROUP BY repo_id, ROLLUP(event_type, event_public);

Creating temporary tables on the coordinator is limited by the node’s disk size and CPU resources.