alloydb-bench

TPC-H run across four Postgres-compatible engines, on matched hardware, to see what the AlloyDB columnar engine actually buys you and where the "up to 100x" number holds up. Companion data for the boringSQL post on AlloyDB.

Everything here is meant to be re-run. If a number doesn't reproduce for you, that's a bug worth an issue.

All europe-west1, TPC-H scale factor 10 (same dbgen output everywhere, checksums in checksums-sf10.sha256). Query set: Q1, Q5, Q6, Q12, Q14, Q18, plus a point-lookup control. At SF10 each query is run 5 times warm with the cold run dropped; the SF100 runs below used 3 warm runs. Median and p90 are reported over the runs that completed, and each summary table states how many that was. Verbatim EXPLAIN (ANALYZE, BUFFERS) for every run is under results/raw/<label>/.

The engine variants (Omni and managed only) carry a suffix:

• C0 / D0 columnar engine off
• C1 / D1 columnar on, auto-populated (the engine picks columns from the workload)
• C2 columnar on, columns pinned by hand

C0-47g / C0-16g are the same run at two shared_buffers sizes (see caveats).

Parity is the whole point of a four-engine comparison, so the places it breaks are stated, not hidden:

• Postgres major. A, B, D are on 18; C (Omni) is on 17. The current Omni Docker image lagged a major behind managed AlloyDB at the time of the run. The columnar deltas (C0 vs C1) are internal to one engine and unaffected; only the absolute A-vs-C comparison carries the gap.
• Memory. Cloud SQL Enterprise caps at 6.5 GB/vCPU, so 8 vCPU tops out at 52 GB, not the 64 GB AlloyDB gives you at the same core count. vCPU parity was kept (parallelism matters more for TPC-H); the 52-vs-64 gap is immaterial here (see the buffer-size result below).
• work_mem and parallelism. Managed services ship conservative defaults (Cloud SQL: work_mem=4MB, 2 workers per gather). Left as-is those dominate the result. B and D are reported both as-shipped and with work_mem=256MB and 4 workers to match A, because otherwise you are benchmarking the default config, not the engine. Both rows are in the tables.
• Omni shared_buffers. Omni auto-tunes to ~47 GB on a 64 GB box. It was run at both 47 GB (its default) and 16 GB (matched to A). The two are within noise at SF10, so buffer size is not the story here. Might be at SF100.

Local smoke test (Docker, small scale, proves the pipeline; not a data point):

make up
make gen SCALE=1
make all

Real runs live on matched cloud instances. A and C share one VM; snapshot the loaded disk once and restore it for each run rather than re-loading. B and D go over their auth proxies / an authorized client. The PGURI env var points the harness at a remote target; unset, it talks to the local Docker container.

PGURI=postgresql://user@host:5432/tpch make schema load index analyze
PGURI=postgresql://user@host:5432/tpch make bench LABEL=B-par

For the columnar engine (Omni and managed), confirm it actually engaged before trusting a number. If the plan says Seq Scan instead of Custom Scan (columnar scan), the store wasn't populated for that query:

make imcs-status          # what's resident (g_columnar_columns)

Four-engine baseline plus the columnar sweep. B and D shown parity-matched (work_mem/workers = A); B-stock is Cloud SQL's out-of-box default for contrast.

* C0-47g Q14, from results/tables/summary-C0-47g.md. (Earlier revisions of this README printed 1596 here, a value in no results file; the C0 Q14 medians are 1004.595 at 47g and 980.897 at 16g.) Full per-run numbers and the 16g/47g and B-stock rows are in results/tables/.

Cloud SQL at its shipped defaults (B-stock), for the "what you get if you don't touch the flags" case: Q5 12436 ms, Q1 16506 ms. Seven times slower on the join, entirely from work_mem=4MB. That gap is a config default, not an engine.

• The columnar engine is real where the shape fits. Q6 (selective scan, few columns) drops ~38x, Q12 by 4.1x and Q14 by 1.8x. The "100x" headline is a best case for one query shape at larger scale, not a general multiplier. At SF100 that best case shows up: Q6 hits 311x (see "At scale" below).
• It is not magic on everything. Q1 sums four numeric(15,2) columns over all 60M rows and moves only ~1.4x; the numeric arithmetic dominates even in columnar. Q18 (an uncorrelated IN subquery) moves 1.14x, the smallest win in the set. Both are honest ceilings.
• Auto-population beat hand-pinning. On the join queries the engine's own column recommendation (C1) outperformed the columns we pinned by hand (C2), because it pulled in join keys the manual pin missed.
• Managed (D1) and self-run Omni (C1) columnar results track each other closely. The disaggregated storage doesn't change the columnar query profile at SF10.
• The point-lookup control moves nowhere with columnar on, as it should. If it had regressed, that would matter.

The four-engine matrix above is SF10. To see where the "100x" claim comes from, one config was rerun at SF100 (scale factor 100, ~600M lineitem rows, ~130 GB heap): AlloyDB Omni on the same 8 vCPU / 64 GB box, shared_buffers=16GB so the row store is genuinely I/O-bound, columnar store 32 GB. Row-store baseline (C0) vs columnar (C1), both gate-verified Custom Scan (columnar scan) on every query. Raw plans in results/raw/C0-sf100/ and results/raw/C1-sf100/.

q14 and q18 are absent from this table on purpose. Neither yields a defensible speedup at SF100, and earlier revisions of this README reported both as though it did:

• q14 has no row-store baseline. Both C0 attempts died with could not resize shared memory segment ... (errno=28) before producing a timing -- the harness under-provisioned /dev/shm, which is my fault and not either engine's. The columnar side ran fine (5,465 ms), but it is unpaired.
• q18 ran to completion on the row store, twice, at 655 s and 661 s. Every columnar attempt was cancelled by a statement timeout. So the two sides did not run under the same effective limit and the pair cannot be turned into a ratio. Note that no script in this repo sets statement_timeout: it came from the session/environment, and it was evidently not applied uniformly, since the C0 runs were allowed past 650 s.

Per-query detail, including how many runs completed, is in results/tables/summary-C0-sf100.md and summary-C1-sf100.md.

The mechanism is in the q6 plans. Row store: Buffers: shared read=11549743, I/O Timings: shared read=814349 ms, a parallel seq scan that reads 11.5M pages off disk. Columnar: Columnar cache search mode: native, no heap reads at all. That is why the SF10 gap (38x, mostly in RAM on both sides) widens to 311x at SF100: the row store spills to disk while the columnar store stays memory-resident. q1 (2.0x) is the same ceiling as at SF10, just larger.

One thing this pair is not: a single-variable experiment. To hold its columns, C1 had about 31 GB of columnar store on top of the 16 GB buffer pool, while C0 got the 16 GB and nothing else (results/tables/imcs-status-C1-sf100.txt). So it measures the engine and roughly 3x more memory over the hot data together. The two cannot be separated here, because making the hot set fit is the mechanism -- the heap is ~100 GB, which no buffer pool on a 64 GB box accommodates. The SF10 C0/C1 pair has no such confound.

One trap, documented because it silently produces wrong numbers: at SF100, google_columnar_engine_run_recommendation(mb, 'FIXED_SIZE', true) populated zero columns (no columnar workload history right after enabling the engine), so the store stayed empty and every plan fell back to a seq scan with C1 == C0. Explicit google_columnar_engine_add(table, cols) (schema/omni/imcs_pin.sql) is what populated it. Always confirm residency in g_columnar_columns and a columnar scan node in EXPLAIN before trusting a columnar number.

These ran on separate harnesses and their results are in this tree:

cost/ holds the break-even model behind the article's read-pool charts. It is list-price modelling, not measured spend -- see cost/README.md.

Stated so you don't have to find them:

• The orchestration scripts that drove the SF100, HTAP, vacuum, lag, crossover and vector runs are not published here; run/ holds the per-step scripts they called.
• run/vacuum.sh runs a pgbench TPC-B mix, but the committed vacuum data came from a 1M-row churn table with repeated full-table UPDATEs.
• run/crossover.sh counts every run including the cold one, unlike run/bench.sh.
• The p90 column is computed as int(0.9*n+0.999999), which at n=5 is the maximum.
• Nothing here verifies checksums-sf10.sha256 automatically.