⚡

PostgreSQL数据库专家Skill postgres-pro

PostgreSQL数据库专家技能提供全面的PostgreSQL数据库管理、性能优化、高可用性设置、备份恢复和高级功能实现的专业知识。专注于PostgreSQL性能调优、JSONB索引优化、流复制配置、分区策略和高级扩展应用，帮助企业构建高性能、高可用的PostgreSQL数据库系统。关键词：PostgreSQL数据库管理、性能优化、高可用性、备份恢复、JSONB索引、流复制、分区策略、PostGIS、pgvector、数据库调优。

数据工程 0 次安装 4 次浏览 更新于 2/23/2026

name: postgres-pro description: 当用户需要PostgreSQL数据库管理、性能优化、高可用设置、备份恢复或高级PostgreSQL功能实现时使用。

PostgreSQL专家

目的

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

使用时机

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

快速开始

在以下情况调用此技能：

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

不要在以下情况调用：

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

核心能力

PostgreSQL架构

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

高级功能

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

性能调优

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

复制策略

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

备份与恢复

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

决策框架

JSONB索引策略

JSONB查询模式分析
│
├─ 包含查询（@> 操作符）？
│   └─ 使用带jsonb_path_ops的GIN
│       CREATE INDEX idx ON table USING GIN (column jsonb_path_ops);
│       • 比默认GIN小2-3倍
│       • 对@>包含检查更快
│       • 不支持键存在性（?）
│
├─ 键存在性查询（? 或 ?| 或 ?& 操作符）？
│   └─ 使用默认GIN操作符类
│       CREATE INDEX idx ON table USING GIN (column);
│       • 支持所有JSONB操作符
│       • 索引大小较大
│
├─ 特定路径频繁查询？
│   └─ 使用表达式索引
│       CREATE INDEX idx ON table ((column->>'key'));
│       • 对特定路径最有效
│       • B树允许范围查询
│
└─ 需要全文档搜索？
    └─ 组合GIN + 表达式索引
        • GIN用于灵活查询
        • 表达式用于热点路径

复制策略选择

需求 策略 配置
读取扩展 流复制（异步） 多个读取副本
零数据丢失 流复制（同步） 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 = 25% RAM
  • [ ] effective_cache_size = 75% RAM
  • [ ] work_mem针对工作负载调优（EXPLAIN中无临时文件溢出）
  • [ ] 自动清理已配置（大表的scale_factor ≤0.05）
  • [ ] max_connections适当（或使用PgBouncer）
  • [ ] 为PITR启用WAL归档

复制（如适用）：

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

扩展：

  • [ ] 所需扩展已安装（pg_trgm、PostGIS、pgvector等）
  • [ ] 扩展版本与PostgreSQL版本兼容
  • [ ] 为JSONB、tsvector、trigram创建GIN索引
  • [ ] 全文搜索配置了适当的语言词典

JSONB（如使用）：

  • [ ] 创建GIN索引（包含查询使用jsonb_path_ops）
  • [ ] 为频繁查询的路径创建表达式索引
  • [ ] 应用中进行JSONB验证（jsonschema或自定义）
  • [ ] 无深度嵌套JSONB（>3层 → 考虑规范化）

监控：

  • [ ] 配置慢查询日志（log_min_duration_statement = 200ms）
  • [ ] 安装并监控pg_stat_statements
  • [ ] 监控自动清理进度（pg_stat_progress_vacuum）
  • [ ] 监控表膨胀（<15%死元组）
  • [ ] 配置复制延迟警报（<1秒阈值）

附加资源