Citus — Distributed PostgreSQL for Sharding and HTAP

What it is

Citus is a PostgreSQL extension that transforms a single-node Postgres database into a distributed cluster. It adds horizontal sharding, columnar storage, and parallel query execution while preserving full SQL compatibility, ACID transactions, JSONB, PostGIS, and the entire Postgres extension ecosystem.

Citus is designed for multi-tenant SaaS applications and real-time analytics workloads (HTAP). It distributes tables across worker nodes by a chosen distribution column (typically tenant_id), allowing queries to be parallelized across shards. Now part of Microsoft Azure (Azure Cosmos DB for PostgreSQL), Citus remains fully open-source.

How it saves time or tokens

Citus eliminates the need to migrate away from PostgreSQL when your data outgrows a single node. Instead of rewriting your application for a different database, you add the Citus extension and distribute your tables. Existing SQL queries, indexes, and Postgres features continue to work. Columnar storage compresses analytical data by up to 10x, reducing storage costs for time-series and log data.

How to use

1. Spin up a Citus cluster: docker compose -p citus up -d --scale worker=2 using the official Docker setup.
2. Connect to the coordinator node: psql -U postgres.
3. Create tables and distribute them: SELECT create_distributed_table('events', 'tenant_id');.
4. Query normally -- Citus parallelizes execution across workers automatically.

Example

-- Create and distribute a table
CREATE TABLE events (
  tenant_id BIGINT,
  id BIGSERIAL,
  payload JSONB,
  ts TIMESTAMPTZ DEFAULT now()
);
SELECT create_distributed_table('events', 'tenant_id');

-- Columnar storage for analytics
CREATE TABLE logs (ts TIMESTAMPTZ, level TEXT, message TEXT)
  USING columnar;

-- Query across shards
SELECT tenant_id, COUNT(*), AVG(payload->>'duration')
FROM events
WHERE ts > now() - INTERVAL '1 day'
GROUP BY tenant_id;

Related on TokRepo

• AI tools for database -- explore database tools and extensions curated on TokRepo.
• Featured workflows -- discover popular developer tools and AI workflows.

Common pitfalls

• Choosing the wrong distribution column leads to data skew and hot shards. Pick a column with high cardinality and even distribution (tenant_id is the classic choice).
• Cross-shard joins are expensive. Design your schema so that co-located tables share the same distribution column to keep joins local.
• Not all Postgres features work identically in distributed mode. Check Citus documentation for limitations around CTEs, window functions, and certain DDL operations.