Building a Production‑Ready High‑Availability Sharded PostgreSQL Cluster with StackGres, Citus, and Patroni

What Is a Sharded Cluster?

A Sharded Cluster implements database sharding, distributing large tables across multiple PostgreSQL primary instances. This improves read/write throughput and can isolate data for security or compliance reasons.

How a Sharded Cluster Is Implemented

StackGres creates a coordinator SGCluster and one or more shards. The coordinator routes queries to the appropriate shard. The SGShardedCluster resource defines the sharding type (currently only Citus) and the target database.

Creating a Basic Citus Sharded Cluster

The following manifest creates a SGShardedCluster with two coordinator pods and four shards, each shard having two pods:

cat << EOF | kubectl apply -f -
apiVersion: stackgres.io/v1alpha1
kind: SGShardedCluster
metadata:
  name: cluster
spec:
  type: citus
  database: mydatabase
  postgres:
    version: '15'
  coordinator:
    instances: 2
    pods:
      persistentVolume:
        size: '10Gi'
  shards:
    clusters: 4
    instancesPerCluster: 2
    pods:
      persistentVolume:
        size: '10Gi'
EOF

This creates a coordinator with two pods and four shards, each shard containing two pods. After all pods are ready, you can view the topology with:

kubectl exec -n my-cluster cluster-coord-0 -c patroni -- patronictl list
+ Citus cluster: cluster --+------------------+--------------+---------+----+-----------+
| Group | Member           | Host             | Role         | State   | TL | Lag in MB |
+-------+------------------+------------------+--------------+---------+----+-----------+
|     0 | cluster-coord-0  | 10.244.0.16:7433 | Leader       | running |  1 |           |
|     0 | cluster-coord-1  | 10.244.0.34:7433 | Sync Standby | running |  1 |         0 |
|     1 | cluster-shard0-0 | 10.244.0.19:7433 | Leader       | running |  1 |           |
|     1 | cluster-shard0-1 | 10.244.0.48:7433 | Replica      | running |  1 |         0 |
| … (other shards omitted for brevity) …
+-------+------------------+------------------+--------------+---------+----+-----------+

The pg_dist_node table’s groupid column matches the Patroni group IDs; group 0 corresponds to the coordinator.

Creating a Custom Production‑Grade Citus Sharded Cluster

Custom Configuration Resources

SGInstanceProfile – defines pod resources. Example:

apiVersion: stackgres.io/v1
kind: SGInstanceProfile
metadata:
  namespace: demo
  name: size-small
spec:
  cpu: "4"
  memory: "8Gi"

SGPostgresConfig – custom PostgreSQL settings. Example:

apiVersion: stackgres.io/v1
kind: SGPostgresConfig
metadata:
  name: postgresconf
spec:
  postgresVersion: "11"
  postgresql.conf:
    password_encryption: 'scram-sha-256'
    random_page_cost: '1.5'
    shared_buffers: '256MB'
    wal_compression: 'on'

SGPoolingConfig – pgBouncer settings. Example:

apiVersion: stackgres.io/v1
kind: SGPoolingConfig
metadata:
  name: pgbouncerconf
spec:
  pgBouncer:
    pgbouncer.ini:
      pgbouncer:
        max_client_conn: '2000'
        default_pool_size: '50'
    databases:
      foodb:
        max_db_connections: 1000
        pool_size: 20
        dbname: 'bardb'
        reserve_pool: 5
    users:
      user1:
        pool_mode: transaction
        max_user_connections: 50
      user2:
        pool_mode: session
        max_user_connections: '100'

SGObjectStorage – S3‑compatible backup configuration. Example:

apiVersion: stackgres.io/v1beta1
kind: SGObjectStorage
metadata:
  name: objectstorage
spec:
  type: s3Compatible
  s3Compatible:
    bucket: stackgres
    region: k8s
    enablePathStyleAddressing: true
    endpoint: http://my-cluster-minio:9000
    awsCredentials:
      secretKeySelectors:
        accessKeyId:
          key: accesskey
          name: my-cluster-minio
        secretAccessKey:
          key: secretkey
          name: my-cluster-minio

SGDistributedLogs – distributed log server configuration. Example:

apiVersion: stackgres.io/v1
kind: SGDistributedLogs
metadata:
  name: distributedlogs
spec:
  persistentVolume:
    size: 10Gi

SQL Startup Scripts

StackGres can run managedSql scripts at startup. The example creates a PostgreSQL user via a Kubernetes secret and then creates a distributed table with Citus:

kubectl -n my-cluster create secret generic pgbench-user-password-secret \
  --from-literal=pgbench-create-user-sql="create user pgbench password 'admin123'"

The secret is referenced in an SGScript resource:

cat << EOF | kubectl apply -f -
apiVersion: stackgres.io/v1
kind: SGScript
metadata:
  namespace: my-cluster
  name: cluster-scripts
spec:
  scripts:
  - name: create-pgbench-user
    scriptFrom:
      secretKeyRef:
        name: pgbench-user-password-secret
        key: pgbench-create-user-sql
  - name: create-pgbench-tables
    database: mydatabase
    user: pgbench
    script: |
      CREATE TABLE pgbench_accounts (
          aid integer NOT NULL,
          bid integer,
          abalance integer,
          filler character(84)
      );
  - name: distribute-pgbench-tables
    database: mydatabase
    user: pgbench
    script: |
      SELECT create_distributed_table('pgbench_history', 'aid');
EOF

Creating the Citus Sharded Cluster

All required resources are applied in the correct order. The final SGShardedCluster manifest ties together the coordinator, shards, custom profiles, pooling, backup storage, distributed logs, and enables Prometheus monitoring:

cat << EOF | kubectl apply -f -
apiVersion: stackgres.io/v1alpha1
kind: SGShardedCluster
metadata:
  namespace: my-cluster
  name: cluster
spec:
  type: citus
  database: mydatabase
  postgres:
    version: '15.3'
  coordinator:
    instances: 2
    sgInstanceProfile: 'size-small'
    pods:
      persistentVolume:
        size: '10Gi'
    configurations:
      sgPostgresConfig: 'pgconfig1'
      sgPoolingConfig: 'poolconfig1'
    managedSql:
      scripts:
      - sgScript: cluster-scripts
  shards:
    clusters: 3
    instancesPerCluster: 2
    sgInstanceProfile: 'size-small'
    pods:
      persistentVolume:
        size: '10Gi'
    configurations:
      sgPostgresConfig: 'pgconfig1'
      sgPoolingConfig: 'poolconfig1'
  configurations:
    backups:
    - sgObjectStorage: 'backupconfig1'
      cronSchedule: '*/5 * * * *'
      retention: 6
  distributedLogs:
    sgDistributedLogs: 'distributedlogs'
  prometheusAutobind: true
EOF

Each resource must be created before the dependent ones (resources, secrets, permissions). The prometheusAutobind: true flag automatically enables monitoring for the sharded cluster when a Prometheus operator is present.