postgres pro

Writing Community

Skill notes

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

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

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

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

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

Fluxly profile Runtime and permissions are inferred by Fluxly static checks, not author declarations or runtime test results.
Domain
Writing
Author-declared agents
No explicit declaration found; this is not inferred or tested compatibility
Static check
94 / 100 · heuristic scan, not runtime safety proof
Runtime evidence
Not run; no isolated runtime test evidence is currently recorded
Author / version / license
@Jeffallan · MIT
Token usage
Lean
Setup complexity
Guided setup
External API key
Not required
Operating systems
Unspecified (assume cross-platform)
Runtime requirements
No explicit requirements detected
Detected file/system behavior
  • Read-only
  • Write / modify
  • Shell exec
Network behavior
Local-only
Install commands
None (reference only)

Heads up: Tool permissions are not declared; actual access depends on the runtime.

Output preview postgres-pro.preview
# 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;

Discussion

Powered by GitHub Discussions. Sign in with GitHub to comment, react, or subscribe.