PostgreSQL Optimization Kit
Query tuning scripts, index analysis, vacuum configuration, connection pooling, and performance monitoring dashboards.
Product Preview
Try the interactive reader and demo tools below, or get the full product with all content unlocked.
Interactive Reader (Free Preview) ⚙ Try Demo Tools Download Free SampleFile Structure 30 files
Documentation Preview README excerpt
PostgreSQL Optimization Kit
A hands-on toolkit for diagnosing and fixing PostgreSQL performance problems —
the queries, scripts, configuration references, and runbooks an experienced DBA
actually reaches for. No abstractions, no framework to learn: just runnable SQL,
shell scripts you can drop into a cron job, and tuning guides that explain the
why behind every setting.
Everything here is vendor-correct for PostgreSQL 12–16 and uses only the
standard psql client plus widely-available contrib extensions
(pg_stat_statements, optionally pgstattuple / pg_buffercache).
What's included
postgresql-optimization-kit/
├── README.md
├── LICENSE
├── sql/ # read-only diagnostics (+ one demo)
│ ├── 01_pg_stat_statements_top_queries.sql Top queries by time / I/O / temp spill
│ ├── 02_missing_indexes.sql Seq-scan hotspots & un-indexed FKs
│ ├── 03_unused_and_redundant_indexes.sql Dead weight to drop
│ ├── 04_table_and_index_bloat.sql Bloat estimates & dead tuples
│ ├── 05_autovacuum_monitoring.sql Overdue tables + XID wraparound risk
│ ├── 06_cache_hit_ratio.sql Buffer-cache effectiveness
│ ├── 07_locks_and_blocking.sql Blocking trees & idle-in-transaction
│ ├── 08_connection_activity.sql Connection states vs. max_connections
│ └── 09_partitioning_setup.sql Runnable declarative partitioning demo
├── scripts/ # bash automation (set -euo pipefail)
│ ├── run_explain_analyze.sh Capture & archive EXPLAIN plans
│ ├── vacuum_maintenance.sh Targeted, logged VACUUM/ANALYZE
│ └── health_report.sh Run all diagnostics into one report
├── config/
│ ├── postgresql.conf.tuned Annotated server tuning reference
│ └── pgbouncer.ini Annotated connection-pooler config
├── docs/
│ ├── INDEXING-STRATEGY.md Choosing the right index, E-R-S rule
│ ├── EXPLAIN-ANALYZE-GUIDE.md Reading plans top-to-bottom
│ ├── VACUUM-TUNING.md Autovacuum & wraparound runbook
│ ├── PARTITIONING-GUIDE.md When and how to partition
│ └── CONNECTION-POOLING.md PgBouncer pool modes & sizing
└── examples/
├── sample_schema.sql E-commerce schema + synthetic data
└── slow_query_examples.sql 6 before/after optimizations
Prerequisites
- PostgreSQL 12 or newer (examples validated on 13–16). A few queries note
where 13+ column names differ.
- The
psqlcommand-line client on your machine. - For
sql/01, thepg_stat_statementsextension loaded via
shared_preload_libraries (a restart) and CREATE EXTENSION in your database.
The config/postgresql.conf.tuned file shows exactly how to enable it.
- Connection details supplied the standard way (
PGHOST,PGPORT,PGUSER,
and a ~/.pgpass file). No credentials are stored in this kit.
... continues with setup instructions, usage examples, and more.