Citus + PostgreSQL: Effortless Horizontal Scaling Without Leaving SQL Behind

PostgreSQL has long been a favorite in the developer community. It’s robust, open source, SQL-compliant, and packed with features like JSONB, full-text search, and window functions. But when your app scales — when traffic increases, datasets grow into terabytes, and concurrent users jump from hundreds to thousands — vanilla PostgreSQL on a single machine starts to show its limits.

What happens then?

• Your queries slow down.
• You hit I/O or memory bottlenecks.
• Scaling vertically (adding more CPU/RAM) becomes cost-prohibitive.
• You start thinking about data sharding… and immediately regret it.

Enter Citus.

🧩 What is Citus?

Citus is an open-source PostgreSQL extension (not a fork!) that turns PostgreSQL into a distributed database — one that can scale out across multiple machines or nodes.

The beauty? You keep everything you love about Postgres — SQL syntax, extensions, indexes, triggers — and gain the ability to horizontally scale your database to handle large workloads.

Tagline from the creators:
“Citus transforms Postgres into a distributed database.”

🏗️ Why Use Citus?

Let’s simplify this: PostgreSQL is amazing — until you need to scale across nodes.

When you hit that wall, most teams face tough choices:

1. Migrate to a distributed NoSQL system (and rewrite your queries).
2. Manually implement sharding logic in the app layer.
3. Move to costly enterprise DBs.
4. Accept poor performance and frustrated users.

Citus offers a better path:
✅ Keep PostgreSQL
✅ Achieve horizontal scalability
✅ Maintain SQL queries
✅ Use native extensions like PostGIS, JSONB, etc.

🧠 How Citus Works (Without Jargon)

Imagine you have a PostgreSQL database that’s running on one server. It has a big table called orders, with millions of rows.

You install Citus. Now you can:

• Distribute the orders table across multiple worker nodes.
• Define a distribution key like customer_id or tenant_id.
• Run a query like SELECT COUNT(*) FROM orders WHERE customer_id = 45; — and Citus will only hit the relevant shard on one node.

Components of Citus:

• Coordinator node: Acts like a normal Postgres server; accepts client connections.
• Worker nodes: Store the actual data; run parallel queries.
• Metadata tables: Citus tracks your distributed tables and shard mappings.

📚 Key Concepts Explained Simply

1. Distributed Tables

Your big tables are broken into shards and spread across nodes. This enables parallelism and better data distribution.

SELECT create_distributed_table('orders', 'customer_id');

2. Reference Tables

Smaller tables (like countries, products) that are replicated on all nodes — useful for fast joins.

SELECT create_reference_table('countries');

3. Colocated Joins

When two distributed tables use the same distribution key (e.g., customer_id), they are said to be colocated. Joins between them happen locally — no cross-node traffic.

4. Parallel Query Execution

Citus splits your query and executes it on all workers in parallel — much faster than serial execution.

🛠 Real-World Use Cases

✅ Multi-Tenant SaaS Apps

Imagine you’re building a CRM tool where every customer is a separate tenant. You can distribute data by tenant_id.

Get Mayur Parve’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Advantages:

• Each tenant’s data stays together on one shard.
• Queries for a tenant are fast and local.
• Easy to scale from 10 to 10,000+ customers.

✅ Event or Time-Series Data

Handling IoT events or logs? You can distribute on device_id or customer_id.

• Ingest millions of rows per second.
• Query recent data with parallel performance.
• Combine with TimescaleDB if needed.

✅ Real-Time Analytics Dashboards

Building an analytics platform like Mixpanel or Heap? Run large aggregate queries (e.g., group by day, segment, or cohort) in parallel without worrying about node limits.

🧪 Example: Setting Up Citus on Local with Docker

You can try Citus with a simple Docker command:

docker run -d -p 5432:5432 --name citus \
  -e POSTGRES_PASSWORD=mypassword \
  citusdata/citus

Connect with psql or any PostgreSQL client and start testing:

-- Create schema
CREATE TABLE orders (
  id BIGINT,
  customer_id BIGINT,
  amount NUMERIC,
  created_at TIMESTAMP
);

-- Distribute it
SELECT create_distributed_table('orders', 'customer_id');

-- Insert and query as usual
INSERT INTO orders VALUES (1, 100, 500.0, now());
SELECT * FROM orders WHERE customer_id = 100;

🚀 Performance Gains: How Much Faster?

Benchmark tests show:

• 20x faster analytics queries on large datasets.
• Linear scalability: Add more nodes, get more performance.
• Handles billions of rows across dozens of nodes.

Unlike manually sharded systems, your application code doesn’t change. SQL stays the same. The magic happens under the hood.

🔐 Is It Production Ready?

Absolutely.

• Fully ACID compliant
• Works with connection pooling tools like PgBouncer
• Compatible with major extensions (PostGIS, pgcrypto, etc.)
• Supports PostgreSQL up to version 15+

It’s also battle-tested. Citus was acquired by Microsoft in 2019 and is available as a fully managed service on Azure under Cosmos DB for PostgreSQL.

📈 Citus in Azure Cosmos DB for PostgreSQL

If managing nodes sounds like a hassle, you can use Citus on Azure:

• Start with a single node, scale to many.
• Pay-as-you-grow.
• Built-in monitoring, backups, and security.

📎 Final Thoughts

If you’re building:

• A SaaS platform with hundreds or thousands of tenants
• An analytics dashboard with massive joins and aggregates
• A real-time event ingestion pipeline

… and you’re already using Postgres — Citus is a no-brainer.