postgres-pro

SKILL.md

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

Documentation