🤖 Citus Overview

Citus shu che?

Citus ek PostgreSQL extension che je database ne distributed system ma convert kare che. Horizontal scaling possible bane che aa through.

Basically, ek j PostgreSQL thi multiple nodes ma data distribute kari ne large scale applications handle kari shakay.

Architecture

Main Components

1. Coordinator Node - Query routing ane metadata management
2. Worker Nodes - Actual sharded data store ane execute kare
3. Distributed Tables - Tables je workers ma sharded hoi
4. Reference Tables - Small tables je badha workers ma replicated hoi

Installation

Ubuntu/Debian ma

# Citus repository add karo
curl https://install.citusdata.com/community/deb.sh | sudo bash

# Citus install karo
sudo apt-get install postgresql-16-citus-12.1

# shared_preload_libraries ma add karo
echo "shared_preload_libraries = 'citus'" | sudo tee -a /etc/postgresql/16/main/postgresql.conf

# PostgreSQL restart karo
sudo systemctl restart postgresql

Cluster Setup

-- Coordinator pe extension create karo
CREATE EXTENSION citus;

-- Worker nodes add karo
SELECT citus_add_node('worker1.example.com', 5432);
SELECT citus_add_node('worker2.example.com', 5432);

-- Verify cluster
SELECT * FROM citus_get_active_worker_nodes();

Tables Distribute Karva

Distributed Table Banavo

-- Normal table create karo
CREATE TABLE events (
    event_id bigserial,
    user_id bigint,
    event_type text,
    created_at timestamp
);

-- user_id thi distribute karo (shard key)
SELECT create_distributed_table('events', 'user_id');

-- Distribution check karo
SELECT * FROM citus_tables;

Reference Table

Small lookup tables badha workers pe replicate thay:

-- Country codes jaivi small tables
CREATE TABLE countries (
    country_code char(2) PRIMARY KEY,
    country_name text
);

-- Reference table banavo
SELECT create_reference_table('countries');

Shard Key Choose Karvu

Shard key selection bahuj important che performance mate.

Good Shard Keys

✅ High Cardinality - Vadhare distinct values ✅ Uniform Distribution - Evenly distributed data ✅ Co-location - Related data same shard pe ✅ JOIN Key - JOINs ma use thatu hoi

Examples:

• user_id
• tenant_id
• customer_id
• organization_id

Bad Shard Keys

❌ Status fields (low cardinality - active, inactive) ❌ Timestamps (skewed distribution - nava data ek shard pe) ❌ Sequential IDs across entities ❌ Boolean values

Query Patterns

Pushdown Queries

Workers pe execute thay - fast performance:

-- Workers pe execute thase
SELECT user_id, COUNT(*) 
FROM events 
WHERE event_type = 'login'
GROUP BY user_id;

-- JOINs pan optimized che jyare co-located hoi
SELECT e.*, u.name
FROM events e
JOIN users u ON e.user_id = u.user_id
WHERE e.created_at > CURRENT_DATE - INTERVAL '7 days';

Router Queries

Single shard pe execute thay - fastest:

-- Shard key WHERE ma hoi to single worker pe jase
SELECT * FROM events 
WHERE user_id = 12345 
  AND created_at > CURRENT_DATE - INTERVAL '30 days';
-- Aa bahuj fast che, ek j worker query kare

Shard Rebalancing

Workers add karya pachhi shards redistribute karva pade:

-- Automatic rebalancing start karo
SELECT citus_rebalance_start();

-- Progress check karo
SELECT * FROM citus_rebalance_status();

-- Manual shard move (advanced)
SELECT citus_move_shard_placement(
    shard_id := 102008,
    source_node_name := 'worker1.example.com',
    source_node_port := 5432,
    target_node_name := 'worker2.example.com',
    target_node_port := 5432
);

High Availability

Shard Replicas

Data loss prevent karva mate replication:

-- Default replication factor set karo
SELECT citus_set_default_replication_factor(2);

-- Existing table mate replicate karo
SELECT citus_replicate_distributed_table(
    'events', 
    shard_count => 32, 
    replication_factor => 2
);

Monitoring

-- Shard distribution joyo
SELECT nodename, count(*) as shard_count
FROM citus_shards
GROUP BY nodename;

-- Table sizes check karo
SELECT * FROM citus_table_size('events');

-- Active queries joyo
SELECT * FROM citus_stat_activity;

-- Worker health check
SELECT * FROM citus_check_cluster_node_health();

Common Use Cases

1. Multi-tenant SaaS - tenant_id thi shard karo
2. Real-time Analytics - Time-series data ingestion
3. IoT Applications - Device data high volume ma
4. Gaming Platforms - User activity ane leaderboards
5. E-commerce - Orders ane inventory distributed

Best Practices

1. Shard Key Carefully Choose - Aa decision baad ma change karvanu mushkel
2. Co-locate Related Tables - Same shard key use karo
3. Reference Tables Use - Small lookup tables mate
4. Monitor Shard Distribution - Even distribution maintain rakho
5. Connection Pooling - Coordinator pe PgBouncer use karo
6. Regular Rebalancing - Workers add/remove pachhi

Limitations

• Badha PostgreSQL features support nathi (e.g., some procedural features)
• Schema changes carefully plan karvi pade
• Cross-shard transactions overhead vadhare che
• Complex JOINs without shard key slow thai shake
• Foreign keys limited support che

Performance Tips

-- Statistics update karo regularly
SELECT citus_update_table_statistics('events');

-- Parallel execution check karo
SET citus.max_adaptive_executor_pool_size = 16;

-- Query pushdown verify karo
EXPLAIN SELECT * FROM events WHERE user_id = 123;
-- "Custom Scan (Citus Adaptive)" joyu to pushdown thai rahyu che

---

Next Steps: YugabyteDB ane other distributed options explore karo (coming soon)