postgresql-scaling

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PostgreSQL Scaling Skill

PostgreSQL 扩容 Skill

Atomic skill for horizontal and vertical scaling

用于水平和垂直扩容的原子化Skill

Overview

概述

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

适用于生产环境的分区、连接池与高可用实现方案。

Prerequisites

前提条件

PostgreSQL 16+
Understanding of replication
PgBouncer or Patroni experience

PostgreSQL 16+
具备复制相关知识
有PgBouncer或Patroni使用经验

Parameters

参数

yaml

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

yaml

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

Quick Reference

快速参考

Range Partitioning

范围分区

sql

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');

sql

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 配置

ini

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

ini

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

Replication Setup

复制设置

sql

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

sql

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

Monitor Lag

监控延迟

sql

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

sql

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

模式	使用场景
session	长连接场景
transaction	Web应用（推荐）
statement	简单查询场景

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

问题	原因	解决方案
分区未被使用	分区裁剪功能未启用	SET enable_partition_pruning = on
连接数过多	未使用连接池	使用PgBouncer
复制延迟	副本节点性能慢	检查I/O、网络状况

Usage

使用方式

Skill("postgresql-scaling")

Skill("postgresql-scaling")