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 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"}'; ``` ###