Skip to content
Skills Library
Programming · Analysis

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.

Licence
MIT
Last repository change
24.07.2026

Assessment and security

90/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content25 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
10
Scripts
1
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 24.07.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What to check before installing
  • 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

  1. When to Use This Skill
  2. Core Principles
  3. References available
  4. Scripts available
Original SKILL.md headings · 31 lines, 274 words
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…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.