Skill

postgres-deep

Optimizes PostgreSQL queries via EXPLAIN ANALYZE and pg_stat_statements, designs indexes (B-tree, GIN, GiST, BRIN, partial/composite), implements RLS for multi-tenancy, covers window functions, CTEs, advisory locks, LISTEN/NOTIFY, and PgBouncer pooling.

PostgreSQL

database

npx claudepluginhub rnavarych/alpha-engineer --plugin billy-milligan

Popularity

Parent stars

Parent forks

Invocation

How this skill is triggered — by the user, by Claude, or both

Slash command

/billy-milligan:postgres-deep

User invocable

Model invocable

Inline context

Default effort

Tool Access

This skill is limited to the following tools:

ReadGrepGlob

Context Preview

The summary Claude sees in its skill listing — used to decide when to auto-load this skill

- Optimizing slow queries with EXPLAIN ANALYZE

Supporting Files

references/connection-diagnostics.mdreferences/connection-pooling.mdreferences/index-tuning.mdreferences/indexing-strategies.mdreferences/partition-management.mdreferences/partitioning.mdreferences/query-anti-patterns.mdreferences/query-optimization.mdscripts/analyze-slow-queries.sh

SKILL.md

40 lines · ~650 tokens

Similar Skills

postgres-optimization

1.7k

Provides PostgreSQL optimization guidance including index strategies, query plan analysis, table partitioning, JSONB operations, and connection pooling with pgbouncer.

claude-code-toolkit

postgres-ops

Guides PostgreSQL operations: schema design, index selection (B-tree/GIN/GiST/BRIN), query tuning (EXPLAIN ANALYZE), backups (pg_dump/pg_basebackup), replication, vacuum tuning, monitoring (pg_stat_statements), RLS, extensions (PostGIS/timescaledb).

6 files3 tools

claude-mods

postgres

Provides PostgreSQL references for SQL queries, psql commands, psycopg/asyncpg code, indexes, JSONB patterns, EXPLAIN analysis, PL/pgSQL, roles, RLS, and query tuning.

13 files

flow

Stats

LanguageShell

Parent stars13

Parent forks1

MaintenanceExcellent

Last CommitMar 5, 2026

Actions

View Source View Plugin View on GitHub View README

Help us improve

Share bugs, ideas, or general feedback.

Stats

Actions

Help us improve

Share bugs, ideas, or general feedback.

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

EXPLAIN ANALYZE before optimizing — never guess; measure first; the plan shows the actual cost

Index the access pattern, not the column — a composite index (user_id, created_at DESC) beats two separate indexes for paginated user queries

Partial indexes reduce size and maintenance cost — WHERE status = 'pending' on a 10M row table with 1000 pending rows = tiny, fast index

RLS is enforced at the DB layer — even if application code has a bug, unauthorized rows are invisible

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