← Back to all products

PostgreSQL Optimization Kit

$49

Query tuning scripts, index analysis, vacuum configuration, connection pooling, and performance monitoring dashboards.

30 files
ShellMarkdownSQLJSONPostgreSQL

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 Sample

File Structure 30 files

postgresql-optimization-kit/ ├── LICENSE ├── README.md ├── config/ │ ├── pgbouncer.ini │ └── postgresql.conf.tuned ├── docs/ │ ├── CONNECTION-POOLING.md │ ├── EXPLAIN-ANALYZE-GUIDE.md │ ├── INDEXING-STRATEGY.md │ ├── PARTITIONING-GUIDE.md │ └── VACUUM-TUNING.md ├── examples/ │ ├── sample_schema.sql │ └── slow_query_examples.sql ├── free-sample.zip ├── guide/ │ ├── 01_what-s-included.md │ ├── 02_file-by-file-guide.md │ └── 03_faq.md ├── index.html ├── interactive.html ├── scripts/ │ ├── health_report.sh │ ├── run_explain_analyze.sh │ └── vacuum_maintenance.sh └── sql/ ├── 01_pg_stat_statements_top_queries.sql ├── 02_missing_indexes.sql ├── 03_unused_and_redundant_indexes.sql ├── 04_table_and_index_bloat.sql ├── 05_autovacuum_monitoring.sql ├── 06_cache_hit_ratio.sql ├── 07_locks_and_blocking.sql ├── 08_connection_activity.sql └── 09_partitioning_setup.sql

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 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.

... continues with setup instructions, usage examples, and more.

Code Sample .sql preview

examples/sample_schema.sql -- ============================================================================= -- examples/sample_schema.sql -- A small but realistic e-commerce schema with generated seed data. -- ----------------------------------------------------------------------------- -- Purpose: give every diagnostic and EXPLAIN example in this kit something real -- to run against. The data is synthetic (generate_series + random), contains no -- real people, and is safe to load into a scratch database: -- -- createdb optkit_demo -- psql -d optkit_demo -f examples/sample_schema.sql -- psql -d optkit_demo -f examples/slow_query_examples.sql -- -- Volumes are intentionally modest (~50k customers, ~300k orders) so it loads in -- seconds yet is large enough that a missing index actually shows up in EXPLAIN. -- -- Tested on: PostgreSQL 13-16. -- ============================================================================= -- Re-runnable: drop in dependency order. DROP TABLE IF EXISTS order_items CASCADE; DROP TABLE IF EXISTS orders CASCADE; DROP TABLE IF EXISTS products CASCADE; DROP TABLE IF EXISTS customers CASCADE; -- ----------------------------------------------------------------------------- -- Customers -- ----------------------------------------------------------------------------- CREATE TABLE customers ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL, full_name text NOT NULL, country text NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), is_active boolean NOT NULL DEFAULT true ); -- ----------------------------------------------------------------------------- -- Products -- ----------------------------------------------------------------------------- CREATE TABLE products ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, sku text NOT NULL, name text NOT NULL, category text NOT NULL, price_cents integer NOT NULL CHECK (price_cents >= 0), in_stock boolean NOT NULL DEFAULT true ); -- ----------------------------------------------------------------------------- -- Orders (note: customer_id deliberately starts WITHOUT an index so the # ... 85 more lines ...