专业版
- 作者仓库星标 9,590
- 许可证 MIT
- 作者仓库 claude-skills
- 领域
- 写作
- 作者声明的智能体
- 未找到明确声明;不据此推断已兼容或已测试
- 静态检查
- 94 / 100 · 启发式扫描,不代表运行安全
- 运行环境证据
- 未执行;当前没有隔离运行测试证据
- 作者 / 版本 / 许可证
- @Jeffallan · MIT
- 上下文用量评级
- 低消耗
- 接入复杂程度
- 需简单配置
- 是否需要外部接口密钥
- 不需要
- 兼容的系统
- 未声明(默认跨平台)
- 运行环境要求
- 未检出明确要求
- 检测到的文件与系统行为
-
- 只读
- 允许写入 / 修改
- 命令执行
- 网络行为
- 仅限本地
- 安装命令数
- 无(仅作为资料)
需要注意: 未声明工具权限范围;实际权限由运行环境决定。
-- 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;
# 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
1. **Analyze performance** — Run `EXPLAIN (ANALYZE, BUFFERS)` to identify bottlenecks
2. **Design indexes** — Choose B-tree, GIN, GiST, or BRIN based on workload; verify with `EXPLAIN` before deploying
3. **Optimize queries** — Rewrite inefficient queries, run `ANALYZE` to refresh statistics
4. **Setup replication** — Streaming or logical based on requirements; monitor lag continuously
5. **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
```sql
-- 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` | VACUUM, ANALYZE, pg_stat views, monitoring, bloat |
## Common Patterns
### JSONB — GIN Index and Query
```sql
-- 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
```sql
-- 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
```sql
-- 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:
1. Query with `EXPLAIN (ANALYZE, BUFFERS)` output and interpretation
2. Index definitions with rationale and pre/post verification
3. Configuration changes with before/after values
4. Monitoring queries for ongoing health checks
5. 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
[Documentation](https://jeffallan.github.io/claude-skills/skills/infrastructure/postgres-pro/)
章节与文件索引
按说明文件中的顺序排列。
何时使用此技能
核心工作流程
参考指南
必须做
绝对不能做
文件/命令 -> EXPLAIN (ANALYZE, BUFFERS) · EXPLAIN · ANALYZE · pg_stat · references/performance.md · references/jsonb.md · references/extensions.md · references/replication.md
内容校验值 -> 9dcdd06aa607
当前原文的自动摘译,尚未人工审校。
何时使用此技能
使用 EXPLAIN 分析和优化慢查询 实施 JSONB 存储和索引策略 设置流或逻辑复制 配置和使用 PostgreSQL 扩展 调优 VACUUM、ANALYZE 和 autovacuum
核心工作流程
分析性能 — 运行
EXPLAIN (ANALYZE, BUFFERS)识别瓶颈 设计索引 — 根据工作负载选择 B-tree、GIN、GiST 或 BRIN;部署前使用EXPLAIN进行验证 优化查询 — 重写低效查询,运行ANALYZE刷新统计数据 设置复制 — 根据需求进行流式或逻辑式;持续监控滞后 监控和维护 — 通过pg_stat视图跟踪 VACUUM、bloat 和 autovacuum;每次更改后验证改进参考指南
根据上下文加载详细指南:主题 · 参考 · 加载时性能 ·
references/performance.md· EXPLAIN ANALYZE、索引、统计、查询调优 JSONB ·references/jsonb.md· JSONB 运算符、索引、GIN 索引、包含扩展 ·references/extensions.md· PostGIS、pg_trgm、pgvector、uuid-ossp、pg_stat_statements必须做
使用
EXPLAIN (ANALYZE, BUFFERS)进行查询优化 在创建前后验证索引是否实际与EXPLAIN一起使用 使用CREATE INDEX CONCURRENTLY避免生产中的表锁 在批量数据更改后运行ANALYZE以刷新统计信息 监控autovacuum;针对高流失率表调整autovacuum_vacuum_scale_factor绝对不能做
全局禁用 autovacuum 在不首先分析查询模式的情况下创建索引 在生产查询中使用
SELECT *忽略复制滞后警报 在高变动表上跳过 VACUUM