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