postgres-deep
/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 optimizing slow queries, designing indexing strategy, implementing multi-tenancy.
- Author
- rnavarych ↗
- Repository
- alpha-engineer ↗
- Licence
- MIT
- Last repository change
- 24.07.2026
Assessment and security
- Files in this package
- 10
- Scripts
- 1
- HTML comments
- 0
- URLs in SKILL.md
- 0
No signals detected. Repository changed on 24.07.2026, without references to retired models or APIs.
MIT, open: use and modify while retaining author attribution.
- 1 scripts in this package. Review them before installing, as Claude may execute them.
Scripts: scripts/analyze-slow-queries.sh
What it does and when to use it
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 optimizing slow queries, designing indexing strategy, implementing multi-tenancy.
Original author description
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 optimizing slow queries, designing indexing strategy, implementing multi-tenancy.
How it works
- When to Use This Skill
- Core Principles
- References available
- Scripts available
SKILL.md preview
--- name: postgres-deep description: | 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 optimizing slow queries, designing indexing strategy, implementing multi-tenancy. allowed-tools: Read, Grep, Glob --- # 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
Files in this package (10)
- SKILL.md 3 KB
- references/connection-diagnostics.md 4 KB
- references/connection-pooling.md 4 KB
- references/index-tuning.md 4 KB
- references/indexing-strategies.md 4 KB
- references/partition-management.md 4 KB
- references/partitioning.md 4 KB
- references/query-anti-patterns.md 5 KB
- references/query-optimization.md 4 KB
- scripts/analyze-slow-queries.sh 7 KB
Versions
The database has no other skills named postgres-deep.
Database snapshot: 13.11.2026 · package version 4038bc39852c (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 95postgresql-optimization
SQL performance work in PostgreSQL - EXPLAIN/ANALYZE, index strategy (composite, partial, expression, covering, GIN/GiST/BRIN), planner and join analysis, statistics, MVCC/vacuum bloat, pagination, aggregation rewrites, locks, WAL, and connection pooling. Use when a query is… - 100postgresql
PostgreSQL schema design, query optimization, indexing, and administration. Use when writing schemas, queries, migrations, or mentions PostgreSQL, Postgres, JSONB, partitioning, RLS, CTEs, window functions, EXPLAIN ANALYZE, or connection pooling. - 95data-relational-database
Use this skill when asked about PostgreSQL, MySQL, relational database, partitioning, replication, indexing, vacuum, connection pooling, query optimization, EXPLAIN, CTE, window functions, or transaction isolation. This skill enforces: PostgreSQL architecture understanding… - 100postgresql-indexing-btree-gin-gist
Use this skill when optimizing relational query execution selecting B-Tree, GIN, GiST, and BRIN index structures in PostgreSQL. - 100sql-optimization-patterns
Transforms slow PostgreSQL queries into fast operations through systematic EXPLAIN ANALYZE, indexing strategies, N+1 elimination, cursor pagination, and materialized views. Use when diagnosing slow queries, optimizing database performance, or designing index strategies — always… - 90pgsql-indexing
This skill should be used when a task adds, chooses, reviews or drops a PostgreSQL index, or asks why a query ignores one, for example "which index should I add", "what column order", "why isn't my index used", "deleting a parent row is slow", "can we drop this index", "how do I…