# v0.23.0 — TPC-H DVM Scaling Performance

> **Full technical details:** [v0.23.0.md-full.md](v0.23.0.md-full.md)

**Status: ✅ Released** | **Scope: Large** (~4–5 weeks)

> Root-cause investigation and targeted fixes for three differential refresh
> failure modes discovered at scale — bringing complex analytical queries from
> thousands of times slower than full refresh down to the expected incremental
> performance.

---

## What problem does this solve?

Benchmarking the differential engine at a realistic data scale (TPC-H SF=1.0
— roughly 1 GB of data) revealed that 18 of 22 TPC-H queries had DIFFERENTIAL
refresh *slower* than FULL recomputation — in the worst case, 2,246× slower.
This is the opposite of the expected behaviour. The root causes were three
distinct failure modes, each requiring a different fix.

---

## The Three Failure Modes

### 1. Threshold Collapse in Multi-Join Queries

Queries joining four or more tables (TPC-H Q05, Q07, Q08, Q09) were producing
intermediate query results with O(n) rows — proportional to the total table
size — rather than O(Δ) rows proportional to the change batch. The delta SQL
was not effectively restricting which rows from unchanged tables needed to be
re-scanned.

The fix restructures the aggregate UPDATE handling to split UPDATE rows into
DELETE+INSERT pairs at the source, so the join delta never needs to re-scan
unchanged base tables. Multi-join differential refresh is now O(Δ).

*In plain terms:* changing 100 rows in a table with 1 million rows now refreshes
by processing 100 rows, not 1 million.

### 2. Early Collapse in EXISTS Anti-Join Queries

The EXISTS anti-join query (TPC-H Q04 — "orders with at least one late
lineitems") was 140× slower at 10× the data size because the key filter used
to restrict the "unchanged" portion of the right-side table was not applying
correctly when additional predicates were present.

The fix generates a key filter that restricts the right-side scan to only
the keys that appear in the delta: `WHERE l_orderkey IN (SELECT o_orderkey
FROM delta_orders)`. This turns an O(n) scan into O(Δ).

### 3. Structural Bug in Doubly-Nested EXISTS

TPC-H Q20 (which uses a doubly-nested correlated EXISTS subquery) was
re-materialising an expensive inner subquery for every row in the outer delta.
The fix hoists the inner subquery to a named CTE that is computed once and
shared, reducing Q20's refresh time from ~2 seconds to under 50 milliseconds.

---

## Debug and Tuning Tools

New operational tools introduced alongside the performance fixes:

- **`pgtrickle.log_delta_sql = on`** GUC — logs the generated delta SQL at
  DEBUG level, enabling `EXPLAIN ANALYZE` on the generated queries for
  diagnosis
- **`pgtrickle.delta_work_mem`** GUC — sets a higher `work_mem` for delta
  SQL execution to avoid hash/sort spills in complex joins
- **`pgtrickle.analyze_before_delta = on`** (default on) — runs `ANALYZE` on
  the change buffer tables before executing delta SQL, ensuring the query
  planner has accurate row count estimates

---

## DIFF Output Format Compatibility

The UPDATE-split fix changes the format of differential output for aggregate
stream tables: previously UPDATE events appeared as single rows, now they
appear as DELETE+INSERT pairs. A **`pgtrickle.diff_output_format`** GUC
(`split` or `merged`) allows users to opt into the new format on their own
schedule — set `merged` first, migrate application code, then switch to
`split`.

---

## Result: All 22 TPC-H Queries Under Target

After the three fixes, all 22 TPC-H queries pass differential correctness
validation at SF=1.0, and the previously-failing queries (Q04, Q05, Q07,
Q08, Q09, Q20, Q22) are all within their performance targets. AUTO mode now
correctly routes all 22 queries to DIFFERENTIAL rather than FULL at SF=1.0.

---

## Scope

v0.23.0 is a targeted performance investigation and fix release. The three
failure modes were addressed in strict priority order: confirm the hypothesis
before coding, apply the minimal fix, validate with regression tests. The
result is a differential engine that delivers its theoretical O(Δ) performance
on the full TPC-H benchmark suite at production scale.
