postgres-pro

PostgreSQL Pro

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

何时使用此技能

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

核心工作流程

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

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

-- 步骤1：识别慢查询
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 步骤2：分析特定慢查询
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- 查找：Seq Scan（大表上不好）、高Buffers命中、大集合上的嵌套循环

-- 步骤3：创建目标索引
CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status)
  WHERE status = 'pending';  -- 部分索引减小大小

-- 步骤4：验证索引被使用
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- 确认：Index Scan on idx_orders_customer_status，实际时间更低

-- 步骤5：批量更改后如有需要更新统计信息
ANALYZE orders;

参考指南

根据上下文加载详细指导：

主题	参考	加载时机
性能	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索引和查询

-- 为包含查询创建GIN索引
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- 高效的JSONB包含查询（使用GIN索引）
SELECT * FROM events WHERE payload @> '{"type": "login", "success": true}';

-- 提取嵌套值
SELECT payload->>'user_id', payload->'meta'->>'ip'
FROM events
WHERE payload @> '{"type": "login"}';

VACUUM和膨胀监控

-- 检查死元组数量高的表
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;

-- 手动清理高变更率表并验证
VACUUM (ANALYZE, VERBOSE) orders;

复制延迟监控

-- 在主库上：检查备库延迟
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解决方案时，提供：

1. 带有EXPLAIN (ANALYZE, BUFFERS)输出和解释的查询
2. 索引定义及其理由和前后验证
3. 配置更改及其前后值
4. 用于持续健康检查的监控查询
5. 性能影响的简要说明

知识参考

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

文档