A PostgreSQL Index Lab: Measure, Verify, Roll Back

Synthetic demonstration, recorded September 5, 2026. These are generated orders in a disposable local database. This is not a client case study, production benchmark or promised customer outcome.

The useful part of a performance change is the evidence around it: what was slow, why the plan changed, whether the answers stayed correct, what the change cost, and whether it could be reversed. This lab makes those steps inspectable for one query.

In the recorded run, median PostgreSQL execution time was 70.726 ms before an index and 0.320 ms after it. Removing the index restored a sequential scan; rebuilding it restored an index scan. The rollback phase was much noisier than the first baseline. All four phases and their observed ranges are shown below.

One workload and one change

The deterministic fixture contains one million orders across 1,000 tenants. Tenant 42 has 200 open orders. This unchanged query fetches its latest 50 with a stable two-column order:

SELECT order_id, tenant_id, status, created_at, total_cents
FROM proof.orders
WHERE tenant_id = 42 AND status = 'open'
ORDER BY created_at DESC, order_id DESC
LIMIT 50;

The baseline has a primary-key index but no index matching this access pattern. The intervention adds a composite B-tree index:

CREATE INDEX orders_tenant_status_created_id_idx
ON proof.orders
    (tenant_id, status, created_at DESC, order_id DESC);

The baseline plan scans the million rows, retains 200 matches and sorts for the first 50. With the new index, PostgreSQL can read the matching entries in the requested order and stop after 50 records. The planner chose the index naturally; sequential scans were not disabled and no plan was forced. See PostgreSQL’s multicolumn index documentation for the equality-leading key pattern.

Every measured phase, in execution order

Each phase has three excluded warmups followed by eleven measured executions, run serially. No other samples were discarded. These are EXPLAIN ANALYZE execution times with node-level timing disabled, excluding connection startup, result transmission and application rendering. PostgreSQL documents the measurement semantics and instrumentation overhead of EXPLAIN.

Correctness: each phase returned the same 50 rows, byte for byte. A separate verifier checks all five returned values against independently calculated expectations, validates source hashes and recalculates the medians and ranges from all 56 raw plans: 44 measured executions and 12 warmups.

Conditions that matter when reading the numbers

• Local, memory-backed storage. PostgreSQL 15.18 ran in an ARM64 container on Docker Desktop 29.7.2 on an ARM64 Mac. Database storage was 1 GiB tmpfs, backed by memory. Shared-buffer reads here do not establish physical disk reads or cloud-storage performance.
• Bounded resources, uncontrolled host load. The container had a one-CPU quota and 1.5 GiB memory limit, with 128 MB shared buffers and 4 MB work memory. Parallel query and JIT were disabled in every phase. A CPU quota is not a dedicated core; three unrelated containers were running in the Docker VM.
• Different working sets. The 205.59 MiB table heap exceeded shared buffers, while the indexed query needed a much smaller working set. Caches were not flushed. The run is neither a cold-cache comparison nor a comparison at equal cache residency.
• A deliberately narrow fixture. The generated data suits this indexing example. It does not model production skew, concurrency, write traffic or end-to-end application behavior. Eleven serial samples do not establish p95/p99 latency, throughput, scalability or cloud savings.

The rollback baseline ranged from 71.748 to 371.746 ms. That variation belongs in the result. It prevents treating one timing or a calculated ratio as a stable forecast for another environment.

Storage, build cost and rollback

The added index occupied 49,692,672 bytes (47.39 MiB). The two builds took 2.81 and 2.43 seconds wall time, including docker exec, psql and index creation. Write overhead was not measured; inserts and relevant updates must maintain the extra index.

This lab used ordinary CREATE INDEX, which can block writes. A production build needs its own reviewed locking, deployment and rollback plan. The lab rollback drops only the index that it added:

DROP INDEX proof.orders_tenant_status_created_id_idx;

Rebuilding the index after rollback checks that the indexed plan returns. It does not establish production recovery readiness or the safety of an unrelated database change.

Reproduce and inspect the evidence

The download contains the four SQL files, a Python standard-library runner, an independent saved-result verifier, the complete recorded run and SHA-256 checksums. Full raw plans, all timing samples, returned rows, settings and the immutable image ID are included.

Download the reproducible lab (.zip)

Requirements: Python 3, a running local Docker Desktop desktop-linux context, and an already-present postgres:15 image. Extract the archive, then run from its directory:

cd postgresql-performance-lab
python3 run_lab.py
python3 verify_results.py

The runner never pulls an image, opens a network port or accepts a database connection string. It creates a uniquely named container with networking disabled and a fresh synthetic database, then removes that container and its tmpfs data. SQL files are for this disposable lab. The runner fails if prerequisites are absent; a new run records the local environment and creates a separate evidence directory. Timings will vary.

To inspect the original evidence without running anything: all timings (CSV), summary, environment and source hashes, verification, baseline plan and indexed plan. Individual SQL: fixture, query, index, rollback. Download checksums.