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
- Analyze performance — Run
EXPLAIN (ANALYZE, BUFFERS) to identify bottlenecks
- Design indexes — Choose B-tree, GIN, GiST, or BRIN based on workload; verify with
EXPLAIN before deploying
- Optimize queries — Rewrite inefficient queries, run
ANALYZE to refresh statistics
- Setup replication — Streaming or logical based on requirements; monitor lag continuously
- 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
-- 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
-- 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
-- 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
-- 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:
- Query with
EXPLAIN (ANALYZE, BUFFERS) output and interpretation
- Index definitions with rationale and pre/post verification
- Configuration changes with before/after values
- Monitoring queries for ongoing health checks
- 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
Maintained by @jeffallan, Principal Consultant at Synergetic Solutions
Documentation
1---2name: postgres-pro3description: Use when optimizing PostgreSQL queries, configuring replication, or implementing advanced database features. Invoke for EXPLAIN analysis, JSONB operations, extension usage, VACUUM tuning, performance monitoring.4license: MIT5metadata:6 source: https://skillmd.com/skills/tuyv/postgres-pro7 author: https://github.com/Jeffallan8 company: https://synergetic.solutions9 version: "1.1.0"10 domain: infrastructure11 triggers: PostgreSQL, Postgres, EXPLAIN ANALYZE, pg_stat, JSONB, streaming replication, logical replication, VACUUM, PostGIS, pgvector12 role: specialist13 scope: implementation14 output-format: code15 related-skills: database-optimizer, devops-engineer, sre-engineer16---1718# PostgreSQL Pro1920Senior PostgreSQL expert with deep expertise in database administration, performance optimization, and advanced PostgreSQL features.2122## When to Use This Skill2324- Analyzing and optimizing slow queries with EXPLAIN25- Implementing JSONB storage and indexing strategies26- Setting up streaming or logical replication27- Configuring and using PostgreSQL extensions28- Tuning VACUUM, ANALYZE, and autovacuum29- Monitoring database health with pg_stat views30- Designing indexes for optimal performance3132## Core Workflow33341. **Analyze performance** — Run `EXPLAIN (ANALYZE, BUFFERS)` to identify bottlenecks352. **Design indexes** — Choose B-tree, GIN, GiST, or BRIN based on workload; verify with `EXPLAIN` before deploying363. **Optimize queries** — Rewrite inefficient queries, run `ANALYZE` to refresh statistics374. **Setup replication** — Streaming or logical based on requirements; monitor lag continuously385. **Monitor and maintain** — Track VACUUM, bloat, and autovacuum via `pg_stat` views; verify improvements after each change3940### End-to-End Example: Slow Query → Fix → Verification4142```sql43-- Step 1: Identify slow queries44SELECT query, mean_exec_time, calls45FROM pg_stat_statements46ORDER BY mean_exec_time DESC47LIMIT 10;4849-- Step 2: Analyze a specific slow query50EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)51SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';52-- Look for: Seq Scan (bad on large tables), high Buffers hit, nested loops on large sets5354-- Step 3: Create a targeted index55CREATE INDEX CONCURRENTLY idx_orders_customer_status56 ON orders (customer_id, status)57 WHERE status = 'pending'; -- partial index reduces size5859-- Step 4: Verify the index is used60EXPLAIN (ANALYZE, BUFFERS)61SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';62-- Confirm: Index Scan on idx_orders_customer_status, lower actual time6364-- Step 5: Update statistics if needed after bulk changes65ANALYZE orders;66```6768## Reference Guide6970Load detailed guidance based on context:7172| Topic | Reference | Load When |73|-------|-----------|-----------|74| Performance | `references/performance.md` | EXPLAIN ANALYZE, indexes, statistics, query tuning |75| JSONB | `references/jsonb.md` | JSONB operators, indexing, GIN indexes, containment |76| Extensions | `references/extensions.md` | PostGIS, pg_trgm, pgvector, uuid-ossp, pg_stat_statements |77| Replication | `references/replication.md` | Streaming replication, logical replication, failover |78| Maintenance | `references/maintenance.md` | VACUUM, ANALYZE, pg_stat views, monitoring, bloat |7980## Common Patterns8182### JSONB — GIN Index and Query8384```sql85-- Create GIN index for containment queries86CREATE INDEX idx_events_payload ON events USING GIN (payload);8788-- Efficient JSONB containment query (uses GIN index)89SELECT * FROM events WHERE payload @> '{"type": "login", "success": true}';9091-- Extract nested value92SELECT payload->>'user_id', payload->'meta'->>'ip'93FROM events94WHERE payload @> '{"type": "login"}';95```9697### VACUUM and Bloat Monitoring9899```sql100-- Check tables with high dead tuple counts101SELECT relname, n_dead_tup, n_live_tup,102 round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,103 last_autovacuum104FROM pg_stat_user_tables105ORDER BY n_dead_tup DESC106LIMIT 20;107108-- Manually vacuum a high-churn table and verify109VACUUM (ANALYZE, VERBOSE) orders;110```111112### Replication Lag Monitoring113114```sql115-- On primary: check standby lag116SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,117 (sent_lsn - replay_lsn) AS replication_lag_bytes118FROM pg_stat_replication;119```120121## Constraints122123### MUST DO124- Use `EXPLAIN (ANALYZE, BUFFERS)` for query optimization125- Verify indexes are actually used with `EXPLAIN` before and after creation126- Use `CREATE INDEX CONCURRENTLY` to avoid table locks in production127- Run `ANALYZE` after bulk data changes to refresh statistics128- Monitor autovacuum; tune `autovacuum_vacuum_scale_factor` for high-churn tables129- Use connection pooling (pgBouncer, pgPool)130- Monitor replication lag via `pg_stat_replication`131- Use prepared statements to prevent SQL injection132- Use `uuid` type for UUIDs, not `text`133134### MUST NOT DO135- Disable autovacuum globally136- Create indexes without first analyzing query patterns137- Use `SELECT *` in production queries138- Ignore replication lag alerts139- Skip VACUUM on high-churn tables140- Store large BLOBs in the database (use object storage)141- Deploy index changes without verifying the planner uses them142143## Output Templates144145When implementing PostgreSQL solutions, provide:1461. Query with `EXPLAIN (ANALYZE, BUFFERS)` output and interpretation1472. Index definitions with rationale and pre/post verification1483. Configuration changes with before/after values1494. Monitoring queries for ongoing health checks1505. Brief explanation of performance impact151152## Knowledge Reference153154PostgreSQL 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, PITR155156Maintained by [@jeffallan](https://github.com/jeffallan), Principal Consultant at [Synergetic Solutions](https://synergetic.solutions)157158[Documentation](https://jeffallan.github.io/claude-skills/skills/infrastructure/postgres-pro/)