Using PostgreSQL 16.1 and Citus 12.1 for Distributed Sharding Across Multiple Microservices

Setup Overview

Use PostgreSQL 16.1 with Citus 12.1 to host three microservices (user, time, ping) on a distributed Citus cluster.

Citus 12.1 Experiment Environment

Docker Compose for Distributed Citus Cluster

version: "3"

services:
  master:
    container_name: "${COMPOSE_PROJECT_NAME:-citus}_master"
    image: "citusdata/citus:12.1.1"
    ports: ["${COORDINATOR_EXTERNAL_PORT:-5432}:5432"]
    labels: ["com.citusdata.role=Master"]
    environment: &AUTH
      POSTGRES_USER: "${POSTGRES_USER:-postgres}"
      POSTGRES_PASSWORD: "${POSTGRES_PASSWORD:-citus}"
      PGUSER: "${POSTGRES_USER:-postgres}"
      PGPASSWORD: "${POSTGRES_PASSWORD:-citus}"
      POSTGRES_HOST_AUTH_METHOD: "${POSTGRES_HOST_AUTH_METHOD:-trust}"
  worker:
    image: "citusdata/citus:12.1.1"
    labels: ["com.citusdata.role=Worker"]
    depends_on: [manager]
    environment: *AUTH
    command: "/wait-for-manager.sh"
    volumes:
      - healthcheck-volume:/healthcheck
  manager:
    container_name: "${COMPOSE_PROJECT_NAME:-citus}_manager"
    image: "citusdata/membership-manager:0.3.0"
    volumes:
      - "${DOCKER_SOCK:-/var/run/docker.sock}:/var/run/docker.sock"
      - healthcheck-volume:/healthcheck
    depends_on: [master]
    environment: *AUTH
volumes:
  healthcheck-volume:

Start the cluster with three workers: docker-compose -p citus up --scale worker=3 Verify workers:

docker exec -it citus_master psql -U postgres
SELECT master_get_active_worker_nodes();

master_get_active_worker_nodes
--------------------------------
 (citus-worker-3,5432)
 (citus-worker-1,5432)
 (citus-worker-2,5432)
(3 rows)

Microservice Citus Backend Demo

Citus Official Example Repository

Repository: https://github.com/citusdata/citus-example-microservices

Distributed Schemas (Sharding Mode)

Each service gets its own schema and matching role; when the role name equals the schema name, the search_path is set automatically, so no application changes are required.

Connecting to the Citus Coordinator

docker exec -it citus_master psql -U postgres

Creating Database Roles

CREATE USER user_service;
CREATE USER time_service;
CREATE USER ping_service;

Distributing Schemas

Two methods:

Manually call citus_schema_distribute(schema_name) after creating the schema.

Enable citus.enable_schema_based_sharding to auto‑distribute newly created schemas.

CREATE SCHEMA AUTHORIZATION user_service;
CREATE SCHEMA AUTHORIZATION time_service;
CREATE SCHEMA AUTHORIZATION ping_service;
SELECT citus_schema_distribute('user_service');
SELECT citus_schema_distribute('time_service');
SELECT citus_schema_distribute('ping_service');

Only schemas without distributed or reference tables can be distributed.

SET citus.enable_schema_based_sharding TO ON;
CREATE SCHEMA AUTHORIZATION user_service;
CREATE SCHEMA AUTHORIZATION time_service;
CREATE SCHEMA AUTHORIZATION ping_service;

List distributed schemas:

select * from citus_schemas;

schema_name | colocation_id | schema_size | schema_owner
------------+---------------+-------------+--------------
 user_service | 1 | 0 bytes | user_service
 time_service | 2 | 0 bytes | time_service
 ping_service | 3 | 0 bytes | ping_service
(3 rows)

Creating Tables

Switch to each role and create tables.

\c postgres user_service
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

\c postgres time_service
CREATE TABLE query_details (
    id SERIAL PRIMARY KEY,
    ip_address INET NOT NULL,
    query_time TIMESTAMP NOT NULL
);

\c postgres ping_service
CREATE TABLE ping_results (
    id SERIAL PRIMARY KEY,
    host VARCHAR(255) NOT NULL,
    result TEXT NOT NULL
);

Service Configuration

Each service’s app.py contains a db_config dictionary pointing to the coordinator and the service role.

db_config = {
    'host': 'citus_master',
    'database': 'postgres',
    'user': 'user_service',
    'port': 5432
}
...
app.run(host="0.0.0.0", port=5000)

Similar blocks use time_service and ping_service as the user.

Docker Run for Services

docker run -d --name usersvc -p 6000:5000 \
    --network=citus_default \
    registry.cn-heyuan.aliyuncs.com/hacker-linner/citus-microsvc-user:1.0.0

docker run -d --name timesvc -p 6001:5000 \
    --network=citus_default \
    registry.cn-heyuan.aliyuncs.com/hacker-linner/citus-microsvc-time:1.0.0

docker run -d --name pingsvc -p 6002:5000 \
    --network=citus_default \
    registry.cn-heyuan.aliyuncs.com/hacker-linner/citus-microsvc-ping:1.0.0

Demo Operations

Create users:

curl -X POST -H "Content-Type: application/json" -d '[
  {"name": "John Doe", "email": "[email protected]"},
  {"name": "Jane Smith", "email": "[email protected]"},
  {"name": "Mike Johnson", "email": "[email protected]"},
  {"name": "Emily Davis", "email": "[email protected]"},
  {"name": "David Wilson", "email": "[email protected]"},
  {"name": "Sarah Thompson", "email": "[email protected]"},
  {"name": "Alex Miller", "email": "[email protected]"},
  {"name": "Olivia Anderson", "email": "[email protected]"},
  {"name": "Daniel Martin", "email": "[email protected]"},
  {"name": "Sophia White", "email": "[email protected]"}
]' http://localhost:6000/users

{"message":"Users created successfully","user_ids":[1,2,3,4,5,6,7,8,9,10]}

List users:

curl http://localhost:6000/users

Get current time:

curl http://localhost:6001/current_time

{"current_time":"2023-12-25 06:19:28","ip_address":"192.168.65.1"}

Ping external host:

curl -X POST -H "Content-Type: application/json" -d '{"host": "baidu.com"}' http://localhost:6002/ping

{"host":"baidu.com","result":"PING baidu.com (110.242.68.66): 56 data bytes
...4 packets transmitted, 4 packets received, 0% packet loss
round-trip min/avg/max/stddev = 56.996/93.054/130.946/26.731 ms
"}

Exploring the Database

Verify distributed schemas:

select * from citus_schemas;

schema_name | colocation_id | schema_size | schema_owner
------------+---------------+-------------+--------------
 user_service | 1 | 32 kB | user_service
 time_service | 2 | 32 kB | time_service
 ping_service | 3 | 32 kB | ping_service
(3 rows)

Inspect shard placement:

select nodename, nodeport, table_name, pg_size_pretty(sum(shard_size))
from citus_shards
group by nodename, nodeport, table_name;

nodename      | nodeport | table_name                 | pg_size_pretty
---------------+----------+---------------------------+----------------
 citus-worker-1 | 5432    | ping_service.ping_results | 32 kB
 citus-worker-2 | 5432    | user_service.users        | 32 kB
 citus-worker-3 | 5432    | time_service.query_details| 32 kB
(3 rows)