What's included

This chapter covers the core features and capabilities of PostgreSQL Optimization Kit.

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 psql command-line client on your machine.
• For sql/01, the pg_stat_statements extension 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.

• A role with pg_monitor (or superuser) to read the statistics views.

Quick start (5 minutes)

# 1) Spin up a throwaway database and load the demo schema + data.
createdb optkit_demo
psql -d optkit_demo -f examples/sample_schema.sql

# 2) Watch a missing index get fixed, with real EXPLAIN numbers.
psql -d optkit_demo -f examples/slow_query_examples.sql | less

# 3) Point the diagnostics at a real database and generate a health report.
./scripts/health_report.sh -d your_database
#   -> writes health-reports/health_your_database_<timestamp>.txt

# 4) Capture an EXPLAIN (ANALYZE, BUFFERS) plan for one slow query.
./scripts/run_explain_analyze.sh -d your_database -f my_slow_query.sql

Make the scripts executable once after unzipping: chmod +x scripts/*.sh.

File-by-file guide

Follow this guide to get PostgreSQL Optimization Kit up and running in your environment.

File-by-file guide

sql/ — diagnostics you run against a live database

All of 01–08 are read-only (they only query the statistics catalogs), so

they are safe to run on production. Start with health_report.sh for the full

picture, then drill into individual files. 09_partitioning_setup.sql is the one

exception — it creates and drops an events table to demonstrate

partitioning, so run it only in a scratch database.

scripts/ — automation

• run_explain_analyze.sh wraps EXPLAIN (ANALYZE, BUFFERS, VERBOSE),

rolls back any write so it is safe to profile an UPDATE, and archives each

plan with a timestamp for before/after diffs.

• vacuum_maintenance.sh runs targeted ANALYZE or VACUUM (ANALYZE) on

the tables with the most dead tuples, with a full log. It never runs

VACUUM FULL.

• health_report.sh executes diagnostics 01–08 and concatenates the

output into a single timestamped report.

config/ — tuning references (read, understand, then copy)

Annotated postgresql.conf and pgbouncer.ini. These are not drop-in files;

every parameter is explained with a recommended range and the reasoning, sized

around an example 32 GB OLTP server so you can scale to your hardware.

docs/ — the runbooks

Five focused guides that turn the raw diagnostics into decisions: how to choose

and order index columns, how to read a query plan, how to keep autovacuum healthy

(and avoid wraparound), when partitioning pays off, and how to size a PgBouncer

pool.

examples/ — a sandbox that actually runs

A synthetic e-commerce schema (~50k customers, ~300k orders, ~750k line items)

deliberately built with a couple of missing indexes so the six worked

optimizations in slow_query_examples.sql show real, measurable improvements.

A suggested workflow

1. Run health_report.sh and read it in triage order: locks → autovacuum/

wraparound → cache hit → missing indexes → bloat → connections.

2. For each slow query from sql/01, capture a plan with run_explain_analyze.sh

and read it using docs/EXPLAIN-ANALYZE-GUIDE.md.

3. Apply the indexing decisions from docs/INDEXING-STRATEGY.md. Build indexes

CONCURRENTLY, ANALYZE, and re-check the plan.

4. Tune config/postgresql.conf.tuned one group at a time, measuring between

changes.

5. Put vacuum_maintenance.sh and partition pre-creation on a schedule.

Safety notes

• The sql/01–08 diagnostics are read-only. sql/09 and parts of

slow_query_examples.sql create objects — run those in a scratch database.

• Build and drop indexes with CONCURRENTLY on production to avoid blocking.
• EXPLAIN ANALYZE executes the statement; use the provided script (it wraps

writes in a rolled-back transaction) or do the same yourself.

• This kit reads your database; it never stores connection details or

credentials. Use ~/.pgpass (chmod 600) and a pg_monitor role.