postgres-pro

# 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...