When is Postgres underkill?

Local Docker Compose stack pairing Postgres 18 (OLTP) and ClickHouse 26 (OLAP) for side-by-side comparison using TPC-H data. Built as the live demo for a talk on engine and modeling effects: same data, same queries, four targets.

The workload is derived from TPC-H (dbgen data, custom queries and methodology); results are not comparable to published TPC-H results and this is not a TPC-H benchmark in the spec's sense. See the "What this is, and is not" section in RESULTS.md.

Both engines get both schemas, giving a 2x2 (engine x model) matrix:

• Normalized (3NF): 8-table TPC-H schema (region, nation, part, supplier, partsupp, customer, orders, lineitem) with PKs/FKs.
• Star: derived fct_lineorder fact plus dim_customer, dim_supplier, dim_part (nation/region folded into dims).

Same .tbl files feed both engines, so the row contents match by construction. just verify proves the four quadrants return identical query answers.

Both containers run under an identical resource contract (8 CPUs, 16 GB RAM, 4 GB shm, same SSD) with engine-appropriate internal tuning; bench/results/runs.meta.json records the contract for each run. The measurement methodology is documented in RESULTS.md and the "Benchmark harness" section below.

Requirements

Docker Compose, Python 3 (stdlib only), and just.

Quick start

just up          # start both engines, wait for healthchecks
just load        # generate TPC-H data and load both engines
just verify      # cross-quadrant answer check (same query, same answer)
just bench q1    # benchmark one query across all four quadrants
just report      # slice runs.csv into bench/results/REPORT.md

Run just with no arguments to list every recipe (shells, row counts, disk sizes, live-demo panes, cleanup).

Scale factors

• SF=30 (~180M lineitems, ~45 GB of .tbl) is the measurement scale; all slide numbers come from it. Loading takes about 45-60 min.
• SF=3 (~4.5M orders / ~18M lineitems, ~3 GB) is the iteration scale for developing queries and harness code. Loads in minutes.
• just recipes default to SF=30; raw docker compose --profile load run --rm loader defaults to SF=3. Override either with SCALE_FACTOR=<sf>.

Generated .tbl files are cached in the tpch_data volume; re-running with the same SF skips dbgen.

The scale sweep

just sweep       # SFs 0.1 0.3 1 3 10 30: reload + bench Q1/Q3 at each (~1.5-3h)
just crossover   # where each quadrant crosses 100ms / 1s / 10s latency
python3 bench/make_report_html.py   # log-log charts at bench/results/charts.html

The sweep answers "at what data volume does each option stop being fast enough" (an absolute-latency question, not a ratio). It wipes and reloads the data volumes at every SF point and ends with SF=30 resident.

Benchmark harness

bench/collect.py (stdlib-only) shells out to docker compose exec. Subcommands: run, snapshot, report, crossover, verify, concurrency, cleanup. One CSV row per measurement lands in bench/results/runs.csv; EXPLAIN dumps in bench/results/plans/. Wall times on both engines are client-observed, measured inside each database's own CLI: psql \timing on Postgres, clickhouse-client --time on ClickHouse (EXPLAIN output is captured separately for plan inspection, never for time). Point lookups run 11 iterations with a fresh random customer key each, so medians sample the key distribution rather than one key's warm best case.

Lifecycle

just down        # stop containers, keep data volumes
just wipe        # stop AND delete all data volumes (destructive)
just reload      # wipe + up + load, for a clean slate