Choosing the Right Distribution Column in Citus: Best Practices for Efficient Queries

Determine Application Type

Running efficient queries on a Citus cluster requires data to be distributed according to the application’s query patterns. Two workload categories work well:

Multitenant SaaS – dozens to hundreds of tables per schema, queries scoped to a single tenant (company/account), Web‑OLTP and tenant‑specific OLAP workloads.

Real‑time analytics – few tables centered on a large event table, heavy ingestion of mostly immutable data, aggregation queries grouped by date or category.

If the workload matches either category, choose a distribution column that aligns with typical query filters.

Select Distribution Column

The distribution column determines how rows are hashed to shards. A correct choice keeps related rows on the same worker, enabling fast joins and full SQL support; a poor choice creates hotspots and loses cross‑node functionality.

Multitenant applications

These SaaS apps naturally shard by a tenant_id (e.g., company_id). Queries are always filtered on that column, so each request touches only one tenant’s shard.

SELECT create_distributed_table('event', 'tenant_id');
SELECT create_distributed_table('page', 'tenant_id', colocate_with => 'event');

Co‑locating event and page on the same tenant_id allows a dashboard query to run on a single node:

SELECT page_id, count(event_id)
FROM page
LEFT JOIN (
  SELECT * FROM event
  WHERE (payload->>'time')::timestamptz >= now() - interval '1 week'
) recent USING (tenant_id, page_id)
WHERE tenant_id = 6 AND path LIKE '/blog%'
GROUP BY page_id;

Real‑time analytics applications

These workloads shard by a high‑cardinality “entity id” (user, host, device) rather than by tenant. The column should be frequently used in GROUP BY or join clauses and must distribute rows evenly to avoid hotspot shards.

Example schema:

CREATE TABLE event (
  entity_id int,
  event_id bigint,
  payload jsonb,
  primary key (entity_id, event_id)
);

Time‑series data

Do not use a timestamp as the distribution column because hashing spreads adjacent time rows across many shards, increasing network overhead for recent‑data queries. Use tenant_id for multitenant time‑series or entity_id for real‑time workloads, and rely on native PostgreSQL table partitioning by time.

Table co‑location (data co‑location)

When multiple tables share the same distribution column, Citus guarantees that rows with the same column value reside on the same worker, even after rebalancing. This enables efficient joins without cross‑node traffic.

Co‑location commands:

SELECT create_distributed_table('event', 'tenant_id');
SELECT create_distributed_table('page', 'tenant_id', colocate_with => 'event');

Query performance

Citus parallelises incoming queries by fragmenting them across worker shards. The planner uses a two‑stage optimizer: the first stage rewrites SQL for push‑down and parallel execution; the second stage runs on each worker using PostgreSQL’s native planner. Proper distribution column selection minimises network I/O and leverages PostgreSQL‑level tuning (e.g., work_mem, max_parallel_workers).