---

name: postgresql-scaling description: Scale PostgreSQL - partitioning, connection pooling, high availability version: "3.0.0" sasmp_version: "1.3.0" bonded_agent: 07-postgresql-scaling bond_type: PRIMARY_BOND category: database difficulty: advanced estimated_time: 5h

PostgreSQL Scaling Skill

Atomic skill for horizontal and vertical scaling

Overview

Production-ready patterns for partitioning, connection pooling, and high availability.

Prerequisites

• PostgreSQL 16+
• Understanding of replication
• PgBouncer or Patroni experience

Parameters

Copyparameters:
  operation:
    type: string
    required: true
    enum: [partition, pool, replicate, shard]
  partition_type:
    type: string
    enum: [range, list, hash]

Quick Reference

Range Partitioning

CopyCREATE TABLE events (id BIGINT, data JSONB, created_at TIMESTAMPTZ)
PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_q1 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

PgBouncer Config

Copy[pgbouncer]
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000

Replication Setup

CopyCREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secret';
SELECT pg_create_physical_replication_slot('replica1');

Monitor Lag

CopySELECT client_addr, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) as lag
FROM pg_stat_replication;

Pool Mode Selection

Troubleshooting

Usage

CopySkill("postgresql-scaling")