PostgreSQL Reproduction: Methods and Detailed Results

This companion retains the technical detail behind the concise article. The dashboard in the article is an illustrative application of the query, not a measured customer workflow.

Scope and setup

The original and fresh runs took place on October 11, 2026. The fresh run rebuilt the fixtures in a new isolated PostgreSQL cluster using the same frozen schema, query template, generator and runner. Recorded hardware, software and relevant engine settings matched. Sampled planner statistics for the million-row table differed and remain in the evidence.

The recorded environment was PostgreSQL 18.4 on macOS 26.5.2, using an Apple M4 desktop with 10 logical CPUs and 16 GiB RAM. The lab used 256 MiB shared buffers, 16 MiB work memory and a statistics target of 1,000, with parallel query, JIT and autovacuum disabled. These define the experiment; they are not production tuning recommendations.

The generated tables contained 10,000, 100,000 and 1,000,000 rows. They shared a schema, primary-key index, (tenant_id, status) index and 128-byte payload per row. Each had 100 equally sized tenants, with 20% of rows open. The query template was:

SELECT count(*) AS open_count,
       sum(amount_cents) AS open_amount_cents
FROM lab.orders_10000
WHERE tenant_id = 42 AND status = 'open';

Only the validated fixture table name changed between sizes. Every response contained one row with two aggregates, but the values changed as matching input grew. Each table received VACUUM (ANALYZE, FREEZE) once during preparation. The formal runs did not restart the server, re-analyze or flush caches between conditions. Both owned experiment clusters were stopped after their runs.

Sampling and timer boundary

Each run used a feasibility and correctness pilot before the protocol was frozen. Formal measurement comprised three cycles covering all six table-order permutations, with permutation order changed between cycles. For each table block, the runner recorded ten excluded warmups, 30 ordinary queries and one separately instrumented plan.

Each run therefore contains 1,620 primary observations, 540 excluded warmups and 54 plan captures. All recorded primary results matched independently calculated counts and sums. Both runs recorded zero query errors, timeouts or result mismatches. Their formal windows lasted 23.393317 and 23.920362 seconds.

One persistent local connection issued synchronous libpq calls with one query in flight. The primary timer ended when the complete aggregate result arrived. Server processing, local communication and scheduling during the call were included; connection startup, Python-side result decoding and checksum calculation were outside it. A requested 10 ms pause followed each recorded call outside the timer. This was a serial workload, not a fixed-arrival-rate test.

The observations share a connection, fixtures, cache history and host conditions within each short run. They are not independent experimental replications. Host activity and CPU state were not measured, so those cannot explain the timing differences from this record.

Overall summaries

The overall median changes were +42.5%, +40.2% and −1.6%. Every cycle retained the median ordering 10k < 100k < 1m. There was no predeclared tolerance for numerical agreement, so this is a descriptive comparison rather than a pass/fail result for exact reproducibility.

Each condition contains 540 observations. Empirical p95 is the 513th sorted observation, using the nearest-rank rule. It is a descriptive sample quantile, not an uncertainty interval or an SLO guarantee.

Plans, pages and footprint

All 54 captures per run retained the broad shape Aggregate → Bitmap Heap Scan → Bitmap Index Scan. For 10k, 100k and one million rows, the bitmap heap scans returned 20, 200 and 2,000 rows and visited 20, 200 and 2,000 exact heap blocks. There were no lossy heap blocks in these captures.

The bitmap index scan reported 2, 2 and 4 shared-buffer hits; root shared-hit totals were 22, 202 and 2,004. These instrumented observations had zero shared block reads and zero temporary block reads or writes. That does not establish CPU-only execution, measure physical disk activity or show that every preceding ordinary query had identical buffer behavior.

The generated matching ids follow 42 + 500k within each fixture's bounds. In this layout each qualifying row occupied a different heap page. At the recorded 8 KiB block size, 2,000 distinct heap pages represent 15.625 MiB. The million-row table's total relation footprint was 210.046875 MiB; all three tables together were 233.296875 MiB.

Those quantities describe different things. Relation footprint includes more than the visited heap pages. Buffer-hit counters measure accesses, not unique pages. Upper-node totals include child activity, so summing the children again would double-count work. PostgreSQL 18: EXPLAIN

Plans were collected after each block using EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON, FORMAT JSON). EXPLAIN ANALYZE still adds instrumentation with node timing disabled. Its result-delivery boundary also differs from the ordinary aggregate query. Subtracting separate plan and client timings would not isolate client overhead. PostgreSQL 18: EXPLAIN

Variation across blocks

The article's block chart groups each run into 18 comparison blocks. Each size's point contains 30 recorded ordinary queries; the three sizes were measured sequentially within a comparison block. This grouping was inspected after collection, and all observations remain included.

In fresh blocks 9 and 16, the 100k median was lower than the 10k median:

Fresh 100k block medians ranged from 0.1089585 to 1.0281455 ms. Million-row block medians ranged from 1.1781875 to 3.2384375 ms in the fresh run, compared with 2.168021 to 2.737271 ms originally. The similar overall million-row medians therefore conceal different variation within the windows.

The chart's lines show recorded order, not simultaneous measurements, uncertainty bounds or a causal explanation. The block reversals do not negate the whole-cycle ordering; they show why both levels of aggregation matter.

Warmup pacing and call position

Warmups ran back-to-back. Recorded calls had 10 ms pauses after each call, outside the measured interval. The first recorded call in each table block therefore followed unpaced warmups; subsequent calls followed a pause.

A post-hoc grouping found lower first-call medians at every size in both runs:

Each first-call group contains 18 observations; each subsequent-call group contains 522. These are summaries of different groups, not paired causal-effect estimates.

This association identifies a control to examine, without establishing a scheduler, power-state or cache cause. Variation also appeared among subsequent calls, so the first-call distinction alone does not settle the stability question. No samples were removed, and the overall summaries retain all 540 calls per size.

Stronger next comparison

The next protocol should predeclare consistent pacing for warmups and formal calls, including the first formal call, and record actual monotonic gaps. A deliberate paced/unpaced contrast, if useful, should have its own frozen, counterbalanced design. Profiling to investigate a cause also needs a defined question and an assessment of observer effects.

Use longer runs and complete fresh-cluster repetitions across separate time windows before making dependable timing predictions. PostgreSQL's benchmarking guidance recommends measurements lasting at least minutes and repeated trials to assess reproducibility. Duration and repetition improve the evidence; they do not provide a causal explanation by themselves. PostgreSQL 18: pgbench good practices

The prepared extension compares proportional matching input with a family that retains the exact same 20 matching ids, amounts and payloads while adding nonmatching rows. Its two 10k fixtures are logically identical and serve as a duplicate control. It holds logical input fixed, while tenant/status correlation, selectivity, sampled estimates, plans, index footprint and physical layout can still change. It does not isolate a pure physical-footprint cost. PostgreSQL 18: planner statistics

The extension is prepared and unexecuted. Fixture arithmetic and scheduling were reviewed offline; the six-condition runner and verifier still need implementation. Settle pacing and repetition before freezing that protocol. A decrease, unresolved difference, changed plan, failure or inconclusive attribution belongs in the result record.

Inspect and recompute

The downloadable evidence package includes the recorded datasets and an inventory at evidence/README.md. That inventory links the published original reproduction bundle. It supports offline inspection and recomputation; it is not a standalone database lifecycle manager.

With Python 3.9 or later, run from the package root:

python3 tools/recompute.py

The command reads local evidence and creates no database or network connection. Figure data and rendering commands are documented in assets/asset-notes.md inside the package; rendering additionally requires Matplotlib. These checks verify consistency among the included artifacts; they do not recreate historical timings or validate a production performance model.

Download the recorded evidence and recomputation package (ZIP, 754 KiB).