Skip to content
LiveNew tools this week
Submit
AIMarketly
postgres-deep logo

postgres-deep

PostgreSQL deep-dive: EXPLAIN ANALYZE interpretation, index types (B-tree, GIN, GiST, BRIN), partial/covering/composite indexes, pg_stat_statements slow query analysis, Row Level Security, window functions, CTEs, advisory locks, LISTEN/NOTIFY, connection pooling with PgBouncer. Use when optimizin...

rnavarych/alpha-engineer0installs15starsDevOps & CI/CD

SKILL.md

Full skill instructions

PostgreSQL Deep Dive

When to Use This Skill

  • Optimizing slow queries with EXPLAIN ANALYZE
  • Designing indexes for specific access patterns
  • Implementing Row Level Security for multi-tenancy
  • Using window functions for analytics queries
  • Configuring PgBouncer for connection pooling

Core Principles

  1. EXPLAIN ANALYZE before optimizing — never guess; measure first; the plan shows the actual cost
  2. Index the access pattern, not the column — a composite index (user_id, created_at DESC) beats two separate indexes for paginated user queries
  3. Partial indexes reduce size and maintenance cost — WHERE status = 'pending' on a 10M row table with 1000 pending rows = tiny, fast index
  4. RLS is enforced at the DB layer — even if application code has a bug, unauthorized rows are invisible
  5. pg_stat_statements is your slow query log — enable it; it shows cumulative query costs across all calls

References available

  • references/​query-optimization.md — EXPLAIN ANALYZE interpretation, node types, warning signs, stale statistics, work_mem, CTEs
  • references/​query-anti-patterns.md — N+1 queries, missing FK indexes, over-fetching, OFFSET pagination, NOT IN gotcha, DISTINCT misuse
  • references/​indexing-strategies.md — B-tree, GIN, GiST, BRIN index types; composite and covering indexes; index type decision tree
  • references/​index-tuning.md — partial indexes, expression indexes, index maintenance, unused/​bloated index detection, CONCURRENTLY
  • references/​partitioning.md — range/​list/​hash partitioning, when to partition, partition pruning, partitioned table constraints
  • references/​partition-management.md — partition maintenance, DETACH/​DROP/​ATTACH, pg_partman automation, anti-patterns
  • references/​connection-pooling.md — PgBouncer setup, pool modes (session/​transaction/​statement), pool sizing, application config
  • references/​connection-diagnostics.md — SHOW POOLS, pg_stat_activity monitoring, connection troubleshooting runbook, Kubernetes deploy

Scripts available

  • scripts/​analyze-slow-queries.sh — query pg_stat_statements for top consumers and low cache-hit queries