postgres-pro

PostgreSQL 专家

用途

提供全面的 PostgreSQL 专业知识，专注于数据库管理、性能优化和高级功能实现。擅长为 PostgreSQL 部署实现最高的可靠性、性能和可扩展性，并支持高可用性和高级扩展。

适用场景

• 需要使用 PostgreSQL 特有功能（JSONB、全文搜索、PostGIS、pgvector）
• 设置流复制或逻辑复制
• 实现 PostgreSQL 扩展
• 排查 PostgreSQL 特有问题
• 优化 PostgreSQL 配置
• 实现分区和高可用性

快速开始

在以下情况下调用此技能：

• 需要使用 PostgreSQL 特有功能（JSONB 索引、全文搜索、PostGIS、pgvector）
• 为 PostgreSQL 设置流复制或逻辑复制
• 实现 PostgreSQL 扩展（pg_trgm、PostGIS、timescaledb、pg_partman）
• 排查 PostgreSQL 特有问题（autovacuum、膨胀、WAL 归档）
• 优化 PostgreSQL 配置（shared_buffers、work_mem、vacuum 设置）
• 实现 PostgreSQL 分区（声明式分区、约束排除）
• 设置 PostgreSQL 高可用性（Patroni、repmgr、pgpool-II）
• 设计 JSONB 模式并使用 GIN 索引优化查询

请勿在以下情况下调用：

• 编写通用 SQL 查询（ANSI SQL 查询请使用 sql-pro）
• 跨平台数据库优化（通用调优请使用 database-optimizer）
• 使用 MySQL 或 SQL Server 特有功能（请使用平台专属技能）
• 数据库管理基础操作（用户、权限——请使用 database-administrator）
• 不涉及 PostgreSQL 特有功能的简单查询优化
• ORM 查询模式（请使用具备 ORM 专业知识的 backend-developer）

核心能力

PostgreSQL 架构

• 进程架构和内存配置
• WAL 机制和 MVCC 实现
• 存储布局和缓冲区管理
• 锁管理和后台工作进程

高级功能

• 使用 GIN 索引优化 JSONB
• 使用 tsvector 和 GIN 索引进行全文搜索
• PostGIS 空间查询和索引
• 时间序列数据处理和分区
• 外部数据包装器和跨数据库查询
• 并行查询和 JIT 编译

性能调优

• 配置优化（内存、连接、检查点）
• 查询优化和执行计划分析
• 索引策略和索引使用监控
• Vacuum 调优和 autovacuum 配置
• 连接池和并行执行

复制策略

• 流复制和逻辑复制
• 同步设置和级联副本
• 延迟副本和故障转移自动化
• 负载均衡和冲突解决

备份与恢复

• pg_dump 策略和物理备份
• WAL 归档和 PITR 设置
• 备份验证和恢复测试
• 自动化脚本和保留策略

决策框架

JSONB 索引策略

JSONB Query Pattern Analysis
│
├─ Containment queries (@> operator)?
│   └─ Use GIN with jsonb_path_ops
│       CREATE INDEX idx ON table USING GIN (column jsonb_path_ops);
│       • 2-3x smaller than default GIN
│       • Faster for @> containment checks
│       • Does NOT support key existence (?)
│
├─ Key existence queries (? or ?| or ?& operators)?
│   └─ Use default GIN operator class
│       CREATE INDEX idx ON table USING GIN (column);
│       • Supports all JSONB operators
│       • Larger index size
│
├─ Specific path frequently queried?
│   └─ Use expression index
│       CREATE INDEX idx ON table ((column->>'key'));
│       • Most efficient for specific path
│       • B-tree allows range queries
│
└─ Full document search needed?
    └─ Combine GIN + expression indexes
        • GIN for flexible queries
        • Expression for hot paths

复制策略选择

需求	策略	配置
读取扩展	流复制（异步）	多个只读副本
零数据丢失	流复制（同步）	synchronous_commit = on
表级复制	逻辑复制	CREATE PUBLICATION/SUBSCRIPTION
跨版本升级	逻辑复制	复制到新版本
灾难恢复	流复制 + WAL 归档	PITR 能力
延迟恢复	延迟副本	recovery_min_apply_delay

质量检查清单

性能：

• 达到查询性能目标（OLTP <50ms，分析 <2s）
• 已检查所有关键查询的 EXPLAIN ANALYZE
• JSONB、数组和全文查询使用了 GIN/GiST 索引
• 对包含时序数据且大于 10GB 的表实施了分区
• 缓存命中率 >95%（shared_buffers + OS 缓存）
• 已实施连接池（PgBouncer 或应用程序连接池）

配置：

• shared_buffers = RAM 的 25%
• effective_cache_size = RAM 的 75%
• 根据工作负载调整 work_mem（EXPLAIN 中无临时文件溢出）
• 已配置 Autovacuum（大型表的 scale_factor ≤0.05）
• max_connections 设置合适（或使用 PgBouncer）
• 已启用 WAL 归档以支持 PITR

复制（如适用）：

• 已创建复制槽（防止 WAL 被删除）
• 复制延迟 <500ms（P95）
• 已监控 pg_stat_replication（sync_state、replay_lag）
• 已测试故障转移（将副本提升为主库）
• 已配置 pg_hba.conf 以允许复制访问

扩展：

• 已安装所需扩展（pg_trgm、PostGIS、pgvector 等）
• 扩展版本与 PostgreSQL 版本兼容
• 已为 JSONB、tsvector 和三元组创建 GIN 索引
• 已使用合适的语言词典配置全文搜索

JSONB（如使用）：

• 已创建 GIN 索引（包含查询使用 jsonb_path_ops）
• 为频繁查询的路径创建了表达式索引
• 已在应用程序中进行 JSONB 验证（jsonschema 或自定义验证）
• 避免使用深度嵌套的 JSONB（超过 3 层时 → 考虑规范化）

监控：

• 已配置慢查询日志（log_min_duration_statement = 200ms）
• 已安装并监控 pg_stat_statements
• 已监控 Autovacuum 进度（pg_stat_progress_vacuum）
• 已监控表膨胀（死元组 <15%）
• 已配置复制延迟告警（阈值 <1s）

其他资源

• 详细技术参考：请参阅 REFERENCE.md
• 代码示例和模式：请参阅 EXAMPLES.md