postgres-pro_skill

Overview

This skill turns a senior PostgreSQL DBA into your on-demand advisor for query tuning, replication, and advanced database features. It focuses on measurable performance improvements, robust replication setups, and maintainable maintenance routines for production systems. Use it to get concrete changes you can apply and verify with metrics.

How this skill works

I inspect EXPLAIN ANALYZE outputs, pg_stat views, and schema/index definitions to identify bottlenecks and propose fixes. For JSONB, extensions, and replication I provide index recommendations, configuration diffs (before/after), and monitoring queries. Every recommendation includes rationale, expected impact, and verification steps.

When to use it

• Optimize slow queries or investigate high CPU/IO from EXPLAIN ANALYZE
• Design JSONB schemas and GIN/GiST index strategies for containment or search
• Set up or audit streaming/logical replication and failover configuration
• Tune VACUUM/ANALYZE, autovacuum thresholds, and combat table bloat
• Add or evaluate PostgreSQL extensions (pg_trgm, pgvector, PostGIS)
• Implement connection pooling and prepare for high-concurrency workloads

Best practices

• Always start with EXPLAIN ANALYZE and compare before/after timings
• Choose index type to match access patterns (B-tree for equality/range, GIN for JSONB containment)
• Update statistics with ANALYZE after bulk loads and schema changes
• Keep autovacuum tuned for high-churn tables; do not disable it globally
• Use connection pooling (pgBouncer/pgPool) and prepared statements to reduce load
• Monitor pg_stat_statements, pg_stat_replication, and replication lag continually

Example use cases

• Rewrite a slow JOIN + JSONB filter, add a GIN index, and show EXPLAIN ANALYZE improvement
• Configure logical replication for selective table sync and provide subscription setup
• Tune autovacuum/vacuum_cost_limit for a high-write table and report bloat reduction
• Enable and use pg_stat_statements to find top CPU queries and suggest index changes
• Add pg_trgm to accelerate ILIKE searches and provide index creation + verification query