← Back to all products
$49
PostgreSQL Optimization Kit
Query tuning scripts, index analysis, vacuum configuration, connection pooling, and performance monitoring dashboards.
MarkdownShellSQLPostgreSQL
📄 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 28 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
├── 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
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.
📄 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 ...