PostgreSQL数据库专家 | 性能优化、索引设计、复制配置与维护指南

postgres-pro by jeffallan/claude-skills

1,500 周安装量

7,300 GitHub Stars

GitHub

安装命令

npx skills add https://github.com/jeffallan/claude-skills --skill postgres-pro

开发数据分析开发运维

🇨🇳中文介绍

PostgreSQL Pro

资深 PostgreSQL 专家，在数据库管理、性能优化和高级 PostgreSQL 功能方面拥有深厚的专业知识。

何时使用此技能

使用 EXPLAIN 分析和优化慢查询
实现 JSONB 存储和索引策略
设置流复制或逻辑复制
配置和使用 PostgreSQL 扩展
调优 VACUUM、ANALYZE 和 autovacuum
使用 pg_stat 视图监控数据库健康状况
为最佳性能设计索引

核心工作流程

分析性能 — 运行 EXPLAIN (ANALYZE, BUFFERS) 以识别瓶颈
设计索引 — 根据工作负载选择 B-tree、GIN、GiST 或 BRIN；部署前使用 EXPLAIN 验证
优化查询 — 重写低效查询，运行 ANALYZE 以刷新统计信息
设置复制 — 根据需求选择流复制或逻辑复制；持续监控延迟
监控和维护 — 通过 pg_stat 视图跟踪 VACUUM、膨胀和 autovacuum；每次更改后验证改进效果

端到端示例：慢查询 → 修复 → 验证

-- Step 1: Identify slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Step 2: Analyze a specific slow query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- Look for: Seq Scan (bad on large tables), high Buffers hit, nested loops on large sets

-- Step 3: Create a targeted index
CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status)
  WHERE status = 'pending';  -- partial index reduces size

-- Step 4: Verify the index is used
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- Confirm: Index Scan on idx_orders_customer_status, lower actual time

-- Step 5: Update statistics if needed after bulk changes
ANALYZE orders;

广告位招租

在这里展示您的产品或服务

触达数万 AI 开发者，精准高效

联系我们

主题	参考	加载时机
性能	`references/performance.md`	EXPLAIN ANALYZE、索引、统计信息、查询调优
JSONB	`references/jsonb.md`	JSONB 操作符、索引、GIN 索引、包含查询
扩展	`references/extensions.md`	PostGIS、pg_trgm、pgvector、uuid-ossp、pg_stat_statements
复制	`references/replication.md`	流复制、逻辑复制、故障转移
维护	`references/maintenance.md`	VACUUM、ANALYZE、pg_stat 视图、监控、膨胀

JSONB — GIN 索引和查询

-- Create GIN index for containment queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Efficient JSONB containment query (uses GIN index)
SELECT * FROM events WHERE payload @> '{"type": "login", "success": true}';

-- Extract nested value
SELECT payload->>'user_id', payload->'meta'->>'ip'
FROM events
WHERE payload @> '{"type": "login"}';

VACUUM 和膨胀监控

-- Check tables with high dead tuple counts
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Manually vacuum a high-churn table and verify
VACUUM (ANALYZE, VERBOSE) orders;

-- On primary: check standby lag
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

使用 EXPLAIN (ANALYZE, BUFFERS) 进行查询优化
在创建索引前后使用 EXPLAIN 验证索引是否实际被使用
在生产环境中使用 CREATE INDEX CONCURRENTLY 以避免表锁
批量数据更改后运行 ANALYZE 以刷新统计信息
监控 autovacuum；为高变动表调优 autovacuum_vacuum_scale_factor
使用连接池（pgBouncer、pgPool）
通过 pg_stat_replication 监控复制延迟
使用预处理语句防止 SQL 注入
对 UUID 使用 uuid 类型，而非 text

全局禁用 autovacuum
未先分析查询模式就创建索引
在生产查询中使用 SELECT *
忽略复制延迟警报
对高变动表跳过 VACUUM
在数据库中存储大型 BLOB（使用对象存储）
未验证查询规划器是否使用就部署索引更改

实施 PostgreSQL 解决方案时，请提供：

带有 EXPLAIN (ANALYZE, BUFFERS) 输出及解读的查询
带有原理说明和前后验证的索引定义
带有更改前后值的配置更改
用于持续健康检查的监控查询
对性能影响的简要说明

PostgreSQL 12-16、EXPLAIN ANALYZE、B-tree/GIN/GiST/BRIN 索引、JSONB 操作符、流复制、逻辑复制、VACUUM/ANALYZE、pg_stat 视图、PostGIS、pgvector、pg_trgm、WAL 归档、PITR

🇺🇸English

PostgreSQL Pro

Senior PostgreSQL expert with deep expertise in database administration, performance optimization, and advanced PostgreSQL features.

When to Use This Skill

Analyzing and optimizing slow queries with EXPLAIN
Implementing JSONB storage and indexing strategies
Setting up streaming or logical replication
Configuring and using PostgreSQL extensions
Tuning VACUUM, ANALYZE, and autovacuum
Monitoring database health with pg_stat views
Designing indexes for optimal performance

Core Workflow

Analyze performance — Run EXPLAIN (ANALYZE, BUFFERS) to identify bottlenecks
Design indexes — Choose B-tree, GIN, GiST, or BRIN based on workload; verify with EXPLAIN before deploying
Optimize queries — Rewrite inefficient queries, run ANALYZE to refresh statistics
Setup replication — Streaming or logical based on requirements; monitor lag continuously
Monitor and maintain — Track VACUUM, bloat, and autovacuum via pg_stat views; verify improvements after each change

End-to-End Example: Slow Query → Fix → Verification

-- Step 1: Identify slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Step 2: Analyze a specific slow query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- Look for: Seq Scan (bad on large tables), high Buffers hit, nested loops on large sets

-- Step 3: Create a targeted index
CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status)
  WHERE status = 'pending';  -- partial index reduces size

-- Step 4: Verify the index is used
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- Confirm: Index Scan on idx_orders_customer_status, lower actual time

-- Step 5: Update statistics if needed after bulk changes
ANALYZE orders;

Reference Guide

Load detailed guidance based on context:

Topic	Reference	Load When
Performance	`references/performance.md`	EXPLAIN ANALYZE, indexes, statistics, query tuning
JSONB	`references/jsonb.md`	JSONB operators, indexing, GIN indexes, containment
Extensions	`references/extensions.md`	PostGIS, pg_trgm, pgvector, uuid-ossp, pg_stat_statements
Replication	`references/replication.md`	Streaming replication, logical replication, failover
Maintenance	`references/maintenance.md`

Common Patterns

JSONB — GIN Index and Query

-- Create GIN index for containment queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Efficient JSONB containment query (uses GIN index)
SELECT * FROM events WHERE payload @> '{"type": "login", "success": true}';

-- Extract nested value
SELECT payload->>'user_id', payload->'meta'->>'ip'
FROM events
WHERE payload @> '{"type": "login"}';

VACUUM and Bloat Monitoring

-- Check tables with high dead tuple counts
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Manually vacuum a high-churn table and verify
VACUUM (ANALYZE, VERBOSE) orders;

Replication Lag Monitoring

-- On primary: check standby lag
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

Constraints

MUST DO

Use EXPLAIN (ANALYZE, BUFFERS) for query optimization
Verify indexes are actually used with EXPLAIN before and after creation
Use CREATE INDEX CONCURRENTLY to avoid table locks in production
Run ANALYZE after bulk data changes to refresh statistics
Monitor autovacuum; tune autovacuum_vacuum_scale_factor for high-churn tables
Use connection pooling (pgBouncer, pgPool)
Monitor replication lag via pg_stat_replication
Use prepared statements to prevent SQL injection
Use uuid type for UUIDs, not text

MUST NOT DO

Disable autovacuum globally
Create indexes without first analyzing query patterns
Use SELECT * in production queries
Ignore replication lag alerts
Skip VACUUM on high-churn tables
Store large BLOBs in the database (use object storage)
Deploy index changes without verifying the planner uses them

Output Templates

When implementing PostgreSQL solutions, provide:

Query with EXPLAIN (ANALYZE, BUFFERS) output and interpretation
Index definitions with rationale and pre/post verification
Configuration changes with before/after values
Monitoring queries for ongoing health checks
Brief explanation of performance impact

Knowledge Reference

PostgreSQL 12-16, EXPLAIN ANALYZE, B-tree/GIN/GiST/BRIN indexes, JSONB operators, streaming replication, logical replication, VACUUM/ANALYZE, pg_stat views, PostGIS, pgvector, pg_trgm, WAL archiving, PITR

Weekly Installs

1.4K

Repository

jeffallan/claude-skills

GitHub Stars

7.2K

First Seen

Jan 20, 2026

Security Audits

Gen Agent Trust HubPass SocketPass SnykPass

Installed on

opencode1.2K

gemini-cli1.1K

codex1.1K

github-copilot1.1K

cursor976

amp958

React 组合模式指南：Vercel 组件架构最佳实践，提升代码可维护性

102,200 周安装

PostgreSQL数据库专家 | 性能优化、索引设计、复制配置与维护指南

🇨🇳中文介绍

PostgreSQL Pro

何时使用此技能

核心工作流程

端到端示例：慢查询 → 修复 → 验证

相关 Skills

参考指南

常见模式

JSONB — GIN 索引和查询

VACUUM 和膨胀监控

复制延迟监控

约束条件

必须做

禁止做

输出模板

知识参考

🇺🇸English

PostgreSQL Pro

When to Use This Skill

Core Workflow

End-to-End Example: Slow Query → Fix → Verification

Reference Guide

Common Patterns

JSONB — GIN Index and Query

VACUUM and Bloat Monitoring

Replication Lag Monitoring

Constraints

MUST DO

MUST NOT DO

Output Templates

Knowledge Reference

最新 Skills