postgresql-scaling

pluginagentmarketplace's avatarfrom pluginagentmarketplace

Scale PostgreSQL - partitioning, connection pooling, high availability

⭐1stars🔀0forks📁View on GitHub🕐Updated Jan 5, 2026
[SQL and Databases]

When & Why to Use This Skill

This Claude skill provides production-ready patterns and configurations for scaling PostgreSQL databases. It enables developers and DBAs to implement advanced database management techniques including table partitioning (range, list, hash), connection pooling via PgBouncer, and high availability through replication and monitoring. It is designed to optimize database performance, improve resource utilization, and ensure system resilience for high-traffic applications.

Use Cases

  • Large-Scale Data Management: Implementing range partitioning for time-series data to accelerate query performance and simplify data maintenance.
  • High-Concurrency Optimization: Configuring PgBouncer connection pooling to handle thousands of simultaneous web application connections efficiently.
  • Disaster Recovery & Reliability: Setting up physical replication slots and monitoring replication lag to ensure data redundancy and minimize downtime.
  • Performance Troubleshooting: Identifying and resolving bottlenecks related to partition pruning, connection exhaustion, or slow replicas.
namepostgresql-scaling
descriptionScale PostgreSQL - partitioning, connection pooling, high availability
version"3.0.0"
sasmp_version"1.3.0"
bonded_agent07-postgresql-scaling
bond_typePRIMARY_BOND
categorydatabase
difficultyadvanced
estimated_time5h

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

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

Quick Reference

Range Partitioning

CREATE 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

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

Replication Setup

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

Monitor Lag

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

Pool Mode Selection

Mode Use Case
session Long connections
transaction Web apps (recommended)
statement Simple queries

Troubleshooting

Problem Cause Solution
Partition not used Pruning disabled SET enable_partition_pruning = on
Too many connections No pooler Use PgBouncer
Replication lag Slow replica Check I/O, network

Usage

Skill("postgresql-scaling")