Citus and Distributed Postgres
Use Citus to turn Postgres into a distributed database, with co-located tables and reference tables.
Citus and Distributed Postgres is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is Citus?
Citus is a PostgreSQL extension that turns a cluster of PG servers into a distributed database. You SELECT/INSERT against the coordinator; Citus routes to the right worker shards transparently.
Architecture
Two roles:
- Coordinator — receives queries, holds the cluster's metadata, routes/aggregates
- Workers — hold shard data, execute the actual queries
Setting Up
Install the extension on coordinator and workers, then register workers:
CREATE EXTENSION citus;
-- On the coordinator, add worker nodes:
SELECT * FROM master_add_node('worker1', 5432);
SELECT * FROM master_add_node('worker2', 5432);Distributed Tables
Mark a table as distributed by a column:
CREATE TABLE orders (
id BIGSERIAL,
user_id BIGINT NOT NULL,
total NUMERIC(10,2),
...
);
SELECT create_distributed_table('orders', 'user_id');
-- Citus splits orders into shards by hash(user_id).Reference Tables
Small tables replicated to every worker — JOIN-local:
CREATE TABLE countries (id INT, name TEXT);
SELECT create_reference_table('countries');
-- Every worker has a full copy of countries.Co-Location
Tables with the same distribution column are co-located: rows for a given user_id always live on the same worker. JOINs on user_id stay local:
SELECT create_distributed_table('orders', 'user_id');
SELECT create_distributed_table('events', 'user_id', colocate_with => 'orders');Single-Tenant Queries
Citus routes user_id-filtered queries to the right worker — fast:
SELECT * FROM orders WHERE user_id = 42;
-- Goes to one worker shard, returns in milliseconds.Distributed Aggregations
Citus parallelises COUNT/SUM across workers:
SELECT user_id, SUM(total) FROM orders GROUP BY user_id;
-- Each worker computes locally; coordinator concatenates.Re-Balancing
Add a worker, rebalance shards:
SELECT * FROM citus_add_node('worker3', 5432);
SELECT * FROM rebalance_table_shards('orders');
-- Citus moves some shards onto the new worker.Limitations
- Transactions across non-co-located tables limited
- Some Postgres features don't apply to distributed tables
- Schema changes propagate, but require coordination
Citus Cloud / Azure
Citus was acquired by Microsoft — now part of Azure Database for PostgreSQL — Hyperscale (Citus). Also still open source.
Alternatives
- YugabyteDB — distributed PG-compatible
- CockroachDB — Postgres wire-compatible distributed
- Pgvector / TimescaleDB — single-node specialisations
When Citus Wins
Multi-tenant SaaS where each tenant's queries are isolated. Time-series where you partition by tenant + time. Analytics with parallel aggregation.
Recap
Citus makes Postgres distributed.
- Coordinator + workers
- Distributed by column; co-located for local JOINs
- Reference tables for shared dimensions
- Best fit: multi-tenant SaaS
Quick Check
What's the benefit of "co-locating" two distributed tables on the same key in Citus?
Frequently asked questions
Is the “Citus and Distributed Postgres” lesson free?
Yes — the full text of “Citus and Distributed Postgres” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Citus and Distributed Postgres”?
Use Citus to turn Postgres into a distributed database, with co-located tables and reference tables. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Citus and Distributed Postgres” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Sharding Strategies: Range, Hash, Directory
- Cross-Shard Queries: The Hard Problem
- Citus and Distributed Postgres
- When NOT to Shard