{"id":"postgres-pro","name":"postgres-pro","summary":"PostgreSQLクエリの最適化、レプリケーションの設定、高度なデータベース機能の実装時に使用します。","body":"# PostgreSQL Pro\n\nSenior PostgreSQL expert with deep expertise in database administration, performance optimization, and advanced PostgreSQL features.\n\n## When to Use This Skill\n\n- Analyzing and optimizing slow queries with EXPLAIN\n- Implementing JSONB storage and indexing strategies\n- Setting up streaming or logical replication\n- Configuring and using PostgreSQL extensions\n- Tuning VACUUM, ANALYZE, and autovacuum\n- Monitoring database health with pg_stat views\n- Designing indexes for optimal performance\n\n## Core Workflow\n\n1. **Analyze performance** — Run `EXPLAIN (ANALYZE, BUFFERS)` to identify bottlenecks\n2. **Design indexes** — Choose B-tree, GIN, GiST, or BRIN based on workload; verify with `EXPLAIN` before deploying\n3. **Optimize queries** — Rewrite inefficient queries, run `ANALYZE` to refresh statistics\n4. **Setup replication** — Streaming or logical based on requirements; monitor lag continuously\n5. **Monitor and maintain** — Track VACUUM, bloat, and autovacuum via `pg_stat` views; verify improvements after each change\n\n### End-to-End Example: Slow Query → Fix → Verification\n\n```sql\n-- Step 1: Identify slow queries\nSELECT query, mean_exec_time, calls\nFROM pg_stat_statements\nORDER BY mean_exec_time DESC\nLIMIT 10;\n\n-- Step 2: Analyze a specific slow query\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)\nSELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';\n-- Look for: Seq Scan (bad on large tables), high Buffers hit, nested loops on large sets\n\n-- Step 3: Create a targeted index\nCREATE INDEX CONCURRENTLY idx_orders_customer_status\n  ON orders (customer_id, status)\n  WHERE status = 'pending';  -- partial index reduces size\n\n-- Step 4: Verify the index is used\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';\n-- Confirm: Index Scan on idx_orders_customer_status, lower actual time\n\n-- Step 5: Update statistics if needed after bulk changes\nANALYZE orders;\n```\n\n## Reference Guide\n\nLoad detailed guidance based on context:\n\n| Topic | Reference | Load When |\n|-------|-----------|-----------|\n| Performance | `references/performance.md` | EXPLAIN ANALYZE, indexes, statistics, query tuning |\n| JSONB | `references/jsonb.md` | JSONB operators, indexing, GIN indexes, containment |\n| Extensions | `references/extensions.md` | PostGIS, pg_trgm, pgvector, uuid-ossp, pg_stat_statements |\n| Replication | `references/replication.md` | Streaming replication, logical replication, failover |\n| Maintenance | `references/maintenance.md` | VACUUM, ANALYZE, pg_stat views, monitoring, bloat |\n\n## Common Patterns\n\n### JSONB — GIN Index and Query\n\n```sql\n-- Create GIN index for containment queries\nCREATE INDEX idx_events_payload ON events USING GIN (payload);\n\n-- Efficient JSONB containment query (uses GIN index)\nSELECT * FROM events WHERE payload @> '{\"type\": \"login\", \"success\": true}';\n\n-- Extract nested value\nSELECT payload->>'user_id', payload->'meta'->>'ip'\nFROM events\nWHERE payload @> '{\"type\": \"login\"}';\n```\n\n### VACUUM and Bloat Monitoring\n\n```sql\n-- Check tables with high dead tuple counts\nSELECT relname, n_dead_tup, n_live_tup,\n       round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,\n       last_autovacuum\nFROM pg_stat_user_tables\nORDER BY n_dead_tup DESC\nLIMIT 20;\n\n-- Manually vacuum a high-churn table and verify\nVACUUM (ANALYZE, VERBOSE) orders;\n```\n\n### Replication Lag Monitoring\n\n```sql\n-- On primary: check standby lag\nSELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,\n       (sent_lsn - replay_lsn) AS replication_lag_bytes\nFROM pg_stat_replication;\n```\n\n## Constraints\n\n### MUST DO\n- Use `EXPLAIN (ANALYZE, BUFFERS)` for query optimization\n- Verify indexes are actually used with `EXPLAIN` before and after creation\n- Use `CREATE INDEX CONCURRENTLY` to avoid table locks in production\n- Run `ANALYZE` after bulk data changes to refresh statistics\n- Monitor autovacuum; tune `autovacuum_vacuum_scale_factor` for high-churn tables\n- Use connection pooling (pgBouncer, pgPool)\n- Monitor replication lag via `pg_stat_replication`\n- Use prepared statements to prevent SQL injection\n- Use `uuid` type for UUIDs, not `text`\n\n### MUST NOT DO\n- Disable autovacuum globally\n- Create indexes without first analyzing query patterns\n- Use `SELECT *` in production queries\n- Ignore replication lag alerts\n- Skip VACUUM on high-churn tables\n- Store large BLOBs in the database (use object storage)\n- Deploy index changes without verifying the planner uses them\n\n## Output Templates\n\nWhen implementing PostgreSQL solutions, provide:\n1. Query with `EXPLAIN (ANALYZE, BUFFERS)` output and interpretation\n2. Index definitions with rationale and pre/post verification\n3. Configuration changes with before/after values\n4. Monitoring queries for ongoing health checks\n5. Brief explanation of performance impact\n\n## Knowledge Reference\n\nPostgreSQL 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\n\n[Documentation](https://jeffallan.github.io/claude-skills/skills/infrastructure/postgres-pro/)","author":"@Jeffallan","ownerProfile":null,"authorContacts":null,"sourceUrl":"https://github.com/Jeffallan/claude-skills/tree/main/skills/postgres-pro","license":"MIT","category":"data","lang":"en","tokens":1305,"stars":0,"calls30d":1,"claimed":false,"visibility":"public","origin":"crawler","version":"0.1.0","createdAt":"2026-08-22","updatedAt":"2026-08-22","files":[{"path":"references/extensions.md","size":9587,"sha256":"24151c2645e07c139afa9b6ff4ef1f2972950b7e7f08195772256a6f7f216b10"},{"path":"references/jsonb.md","size":8152,"sha256":"8ad7086e676f86f23305a5127714566898e4c500593e1a4679194a32c7f530f2"},{"path":"references/maintenance.md","size":12114,"sha256":"a9915cf08759fb92ccfbcfd2b811f36ac7c02ad0bffff2dc7f59aca9e670203c"},{"path":"references/performance.md","size":7085,"sha256":"b137e04782ab4c5387184862cb689d7657d5cbb13c3c41d61980a2edf7c2c8d4"},{"path":"references/replication.md","size":9991,"sha256":"c4993a7c459f10f4dba0ce5a2c4a703e534e588439a5e94168087f3460f15412"}],"requires":{"mcp":[],"tools":[]},"safety":{"flags":[],"scannedAt":"2026-08-22","hasScripts":false,"networkEndpoints":["jeffallan.github.io"]}}