PostgreSQL Professional

PostgreSQL 专业指南

Purpose

用途

Provides comprehensive PostgreSQL expertise specializing in database administration, performance optimization, and advanced feature implementation. Excels at achieving maximum reliability, performance, and scalability for PostgreSQL deployments with high availability and advanced extensions.

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

When to Use

使用场景

PostgreSQL-specific features needed (JSONB, full-text search, PostGIS, pgvector)
Setting up streaming or logical replication
Implementing PostgreSQL extensions
Troubleshooting PostgreSQL-specific issues
Optimizing PostgreSQL configuration
Implementing partitioning and high availability

需要PostgreSQL专属功能时（JSONB、全文搜索、PostGIS、pgvector）
搭建流式或逻辑复制
实现PostgreSQL扩展
排查PostgreSQL专属问题
优化PostgreSQL配置
实现分区与高可用性

Quick Start

快速开始

Invoke this skill when:

PostgreSQL-specific features needed (JSONB indexing, full-text search, PostGIS, pgvector)
Setting up streaming replication or logical replication for PostgreSQL
Implementing PostgreSQL extensions (pg_trgm, PostGIS, timescaledb, pg_partman)
Troubleshooting PostgreSQL-specific issues (autovacuum, bloat, WAL archiving)
Optimizing PostgreSQL configuration (shared_buffers, work_mem, vacuum settings)
Implementing PostgreSQL partitioning (declarative partitioning, constraint exclusion)
Setting up PostgreSQL high availability (Patroni, repmgr, pgpool-II)
Designing JSONB schema and query optimization with GIN indexes

Do NOT invoke when:

General SQL query writing (use sql-pro for ANSI SQL queries)
Cross-platform database optimization (use database-optimizer for general tuning)
MySQL or SQL Server specific features (use platform-specific skills)
Database administration basics (users, permissions - use database-administrator)
Simple query optimization without PostgreSQL-specific features
ORM query patterns (use backend-developer with ORM expertise)

在以下场景调用本技能：

需要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 schema并通过GIN索引优化查询

请勿在以下场景调用：

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

Core Capabilities

核心能力

PostgreSQL Architecture

PostgreSQL 架构

Process architecture and memory configuration
WAL mechanics and MVCC implementation
Storage layout and buffer management
Lock management and background workers

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

Advanced Features

高级功能

JSONB optimization with GIN indexes
Full-text search with tsvector and GIN indexes
PostGIS spatial queries and indexing
Time-series data handling and partitioning
Foreign data wrappers and cross-database queries
Parallel queries and JIT compilation

基于GIN索引的JSONB优化
结合tsvector与GIN索引的全文搜索
PostGIS空间查询与索引
时序数据处理与分区
外部数据包装器与跨数据库查询
并行查询与JIT编译

Performance Tuning

性能调优

Configuration optimization (memory, connections, checkpoints)
Query optimization and execution plan analysis
Index strategies and index usage monitoring
Vacuum tuning and autovacuum configuration
Connection pooling and parallel execution

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

Replication Strategies

复制策略

Streaming replication and logical replication
Synchronous setup and cascading replicas
Delayed replicas and failover automation
Load balancing and conflict resolution

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

Backup and Recovery

备份与恢复

pg_dump strategies and physical backups
WAL archiving and PITR setup
Backup validation and recovery testing
Automation scripts and retention policies

pg_dump策略与物理备份
WAL归档与PITR搭建
备份验证与恢复测试
自动化脚本与保留策略

Decision Framework

决策框架

JSONB Index Strategy

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

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

Replication Strategy Selection

复制策略选择

Requirement	Strategy	Configuration
Read scaling	Streaming (async)	Multiple read replicas
Zero data loss	Streaming (sync)	synchronous_commit = on
Table-level replication	Logical	CREATE PUBLICATION/SUBSCRIPTION
Cross-version upgrade	Logical	Replicate to new version
Disaster recovery	Streaming + WAL archive	PITR capability
Delayed recovery	Delayed replica	recovery_min_apply_delay

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

postgres-pro

Original

Translation