pg_trickle — Performance History

How has incremental (DIFFERENTIAL) refresh performance improved over the optimization sessions, and what is the current state?

Benchmark Setup

All benchmarks use the same full matrix: 5 scenarios × 2 table sizes × 3 change rates = 30 combinations, run inside isolated PostgreSQL 18.x Docker containers via Testcontainers. Each benchmark applies a realistic DML mix (INSERT/UPDATE/DELETE) per cycle and measures wall-clock refresh time.

Scenario Defining Query Operators Tested
scan SELECT id, region, category, amount, score FROM src Table scan only
filter SELECT id, region, amount FROM src WHERE amount > 5000 Scan + filter
aggregate SELECT region, SUM(amount), COUNT(*) FROM src GROUP BY region Scan + group-by aggregate
join SELECT s.id, s.region, s.amount, d.region_name FROM src s JOIN dim d ON ... Scan + inner join
join_agg SELECT d.region_name, SUM(s.amount), COUNT(*) FROM src s JOIN dim d ON ... GROUP BY ... Scan + join + aggregate

Table sizes: 10,000 and 100,000 rows. Change rates: 1%, 10%, 50%.

Snapshot Timeline

Four benchmark snapshots were captured across the optimization sessions:

Snapshot What Changed
Baseline No optimizations
After P1+P2 Phase A: to_jsonb() trigger serialization, drop unused columns, LSN bounds. Phase B: cached MERGE SQL + cached OpTree/delta template
After P6 + LATERAL VALUES aggregate rewrite, PK resolution, no-change short-circuit, EXISTS early-exit, prepared statements
After P7 + deferred cleanup, warm-cycle tuning

Optimization Details

Phase A — Quick Wins (P1)

  • Fix 3: Replaced row_to_json()::text with to_jsonb() in CDC trigger functions for faster serialization.
  • Fix 4: Dropped unused old_row_data column from change buffer tables, reducing I/O.
  • Fix 11: Added upper LSN bound to change buffer queries and cleanup, preventing unbounded scans.

Phase B — Caching (P2)

  • Fix 9: Cached compiled MERGE SQL across refresh cycles, eliminating repeated query planning (~30ms saved per refresh).
  • Fix 10: Cached parsed OpTree and delta SQL template, avoiding redundant parsing and differentiation (~15ms saved per refresh).

Phase C — Operator Improvements (P3–P6)

  • Fix 5: Replaced 4-branch UNION ALL in aggregate final delta CTE with single-pass LATERAL VALUES expansion — eliminates 3 redundant scans of the merge CTE.
  • Fix 6: Primary key columns resolved from pg_constraint at parse time and stored in OpTree::Scan, preferring real PKs over the non-nullable column heuristic.
  • No-change short-circuit: When the change buffer is empty, skip MERGE entirely and return in <2ms.
  • EXISTS early-exit: Before building the delta query, run a cheap SELECT EXISTS(...) on the change buffer to detect no-ops.
  • Prepared statements: Reuse prepared MERGE statements across cycles to avoid repeated planning.

Phase D — Cleanup & Tuning (P7)

  • Deferred cleanup: Change buffer cleanup moved to after the MERGE completes, reducing lock contention during the critical path.
  • Warm-cycle tuning: Improved steady-state performance for cycles 2+ after initial cold start.

Historical Speedup: INCREMENTAL vs FULL

At 1% Change Rate (Typical Production Workload)

Scenario Rows Baseline After P1+P2 After P6 After P7 (Current)
scan 10K 2.6× 2.6× 10.1× 4.4×
scan 100K 0.7× (slower!) 2.7× 41.7× 7.0×
filter 10K 1.9× 1.7× 5.4× 3.3×
filter 100K 2.5× 2.7× 26.3× 7.9×
aggregate 10K 1.4× 1.2× 1.7× 1.5×
aggregate 100K 1.3× 1.4× 4.4× 2.6×
join 10K 2.3× 1.6× 6.7× 2.9×
join 100K 2.6× 2.7× 30.4× 6.0×
join_agg 10K 1.0× 1.0× 0.9× 0.7×
join_agg 100K 1.3× 1.5× 4.9× 3.1×

At 10% Change Rate

Scenario Rows Baseline After P1+P2 After P6 After P7 (Current)
scan 10K 2.1× 2.2× 6.4× 5.0×
scan 100K 0.8× 1.8× 4.5× 3.3×
filter 10K 1.4× 2.2× 3.3× 1.9×
filter 100K 1.6× 1.6× 3.5× 3.1×
aggregate 10K 0.9× 0.8× 1.2× 1.1×
aggregate 100K 0.6× 0.6× 1.1× 0.8×
join 10K 1.4× 1.4× 4.1× 1.8×
join 100K 1.5× 1.9× 3.1× 3.3×
join_agg 10K 0.8× 0.7× 1.8× 0.9×
join_agg 100K 0.7× 0.7× 0.9× 0.8×

At 50% Change Rate

Scenario Rows Baseline After P1+P2 After P6 After P7 (Current)
scan 10K 1.1× 1.1× 3.1× 2.5×
scan 100K 0.9× 0.9× 0.9× 0.8×
filter 10K 1.2× 1.2× 0.7× 0.9×
filter 100K 1.1× 1.8× 1.0× 1.1×
aggregate 10K 1.2× 1.7× 1.6× 1.0×
aggregate 100K 0.9× 0.9× 0.8× 0.6×
join 10K 1.1× 1.1× 1.1× 1.0×
join 100K 1.1× 1.1× 0.9× 0.9×
join_agg 10K 1.3× 1.1× 0.6× 1.4×
join_agg 100K 0.7× 0.9× 0.8× 0.7×

Current State Summary (After P7)

At 1% Change Rate (Typical Production Workload)

Scenario 10K rows 100K rows
scan 4.4× faster 7.0× faster
filter 3.3× faster 7.9× faster
aggregate 1.5× faster 2.6× faster
join 2.9× faster 6.0× faster
join+agg 0.7× (overhead) 3.1× faster

At 10% Change Rate

Scenario 10K rows 100K rows
scan 5.0× faster 3.3× faster
filter 1.9× faster 3.1× faster
aggregate 1.1× 0.8×
join 1.8× faster 3.3× faster
join+agg 0.9× 0.8×

At 50% Change Rate

At 50% churn, INCREMENTAL approaches or slightly exceeds FULL cost across most scenarios (0.6–2.5×). This is expected — when half the table changes, the delta is nearly as expensive as a full recompute.

Absolute Timings (Current, 100K Rows, 1% Changes)

Scenario FULL avg ms INCREMENTAL avg ms Speedup
scan 326 47 7.0×
filter 267 34 7.9×
aggregate 28 11 2.6×
join 384 64 6.0×
join_agg 67 22 3.1×

Interpretation

What the Numbers Show

  1. Baseline was rough — INCREMENTAL was actually slower than FULL for scan/100K (0.7×) and several other combos were near 1.0×. The overhead of delta computation, MERGE, and change buffering exceeded the cost of just re-running the query.

  2. P1+P2 (caching) fixed the worst regressions. Caching the compiled MERGE SQL and parsed OpTree eliminated ~45ms of repeated planning per cycle. Large-table scan went from 0.7× to 2.7×.

  3. P6 was the breakthrough — no-change short-circuit, EXISTS early-exit, and prepared statements produced the most dramatic gains. At 1% changes on 100K rows:

    • scan: 41.7× (346ms → 8.3ms)
    • filter: 26.3× (196ms → 7.4ms)
    • join: 30.4× (374ms → 12.3ms)

    These extreme multipliers reflect that in a 10-cycle run, most cycles have no changes (the DML happens once, then subsequent cycles find empty change buffers and short-circuit in <2ms).

  4. P7 (current) shows somewhat lower peak numbers than P6 due to benchmark variability (different run, cold-start cycle 1 weighing on averages) and the addition of deferred cleanup overhead. The steady-state “cycle 2+” numbers in the P6 detailed output are the most representative of real-world performance.

Theoretical Basis

The larger the table and the smaller the change rate, the bigger the INCREMENTAL win. Differential maintenance is O(Δ) while full refresh is O(N), so the ratio improves as N/Δ grows. At 100K rows / 1% changes the system processes ~1,000 changed rows instead of re-scanning 100,000, yielding 3–8× speedups in practice (and up to 30–40× when most cycles are no-ops).

When INCREMENTAL Doesn’t Help

  • 50% change rate on large tables: The delta is nearly as big as the table itself.
  • Small aggregates (join_agg at 10K): The FULL refresh is already ~12ms; the overhead of delta computation, MERGE template, and cleanup exceeds the savings.
  • Aggregate 100K at 10–50%: Group-rescan aggregates require reading the stream table to reconstruct counts, which at high change rates approaches full-table cost.

Running Benchmarks

# Build the E2E test image first
./tests/build_e2e_image.sh

# Quick spot check
cargo test --test e2e_bench_tests --features pg18 -- --ignored --nocapture bench_scan_10k_1pct

# Full 30-combination matrix (~15–30 minutes)
cargo test --test e2e_bench_tests --features pg18 -- --ignored --nocapture bench_full_matrix

See BENCHMARK.md for full details on the benchmark harness.

Design Decisions & Root Causes

This section preserves the key technical decisions and root-cause findings from the performance analysis phases (PLAN_PERFORMANCE through PLAN_PERFORMANCE_PART_7). These files were deleted after extracting the information below.

Original Root Cause (Phase 0)

The initial benchmark showed INCREMENTAL was slower than FULL for scan/100K (0.7×). Root cause: execute_manual_refresh() ignored the refresh_mode parameter and always performed TRUNCATE + INSERT — effectively comparing FULL vs FULL with extra overhead. This was the single most impactful bug discovered.

MERGE vs Temp Table (Phase A)

The original incremental apply used a 3-step SPI sequence: create temp table, DELETE matching rows, INSERT new rows. This was replaced with a single PostgreSQL MERGE statement that handles INSERT/UPDATE/DELETE in one operation. Result: 3 SPI calls → 1, ~30% less I/O, atomic application.

JSONB Serialization Round-Trip (Phases A–B)

JSONB serialization was identified as the dominant bottleneck — not query planning as initially suspected. The full path: row → JSON text → binary JSONB → store → read → typed row. Two key fixes:

  1. Trigger serialization: Replaced row_to_json()::jsonb with to_jsonb(NEW) — eliminates the text→binary conversion step.
  2. Row reconstruction: Replaced per-column ->>'col' extraction with jsonb_populate_record() — a single function call reconstructs the entire row instead of N separate field extractions.

SPI Call Reduction (Phases B–C)

The no-op refresh path originally executed 7+ SPI round-trips (13+ through the scheduler) even with zero pending changes. Key eliminations:

  • Redundant SELECT count(*): A full sequential scan of every change buffer was purely informational and served no purpose in the trigger-based CDC model. Replaced with SELECT EXISTS(... LIMIT 1).
  • Combined SPI queries: reltuples + capped-count queries merged via LATERAL. store_frontier + complete_refresh merged into single UPDATE.
  • No-op latency: 86ms → 14.8ms → sub-2ms.

Why PREPARE/EXECUTE Was Rejected (Phase D)

Prepared statements (PREPARE + EXECUTE) were implemented and then reverted. Root cause analysis:

  1. Custom plans (first ≤5 executions) have the same planning cost as direct SPI_execute(), plus the overhead of looking up the prepared statement and binding arguments. No benefit for short-cycle benchmarks (CYCLES ≤ 5).
  2. Generic plans (≥6 executions) skip re-planning but use wrong selectivity for LSN range predicates — without concrete LSN values, the planner uses average-case cardinality estimates, leading to wrong sort strategies for GROUP BY and join methods.
  3. String substitution with concrete parameter values provides correct plans cheaply on every call.

Scan Window Function Fast Path (Phase E)

At 1% changes, ~95%+ of PKs have exactly one change per refresh cycle. The expensive FIRST_VALUE/LAST_VALUE window functions (requiring full partition sort) are unnecessary for these single-change PKs. A 4-CTE split pipeline (pk_stats → single → multi_raw → scan_raw) routes single-change PKs through a direct emit path, skipping window functions entirely. Impact: scan 100K/1% went from 46.5ms → 8.3ms (5.6×).

Aggregate Dedup Elimination (Phase E)

Aggregates originally emitted D+I (delete+insert) row pairs per updated group, requiring a DISTINCT ON sort in the MERGE wrapper. A CASE-based single-row emit marks each group with the correct action directly ('I' for new groups, 'D' for deleted groups, 'I' for updated groups), making aggregate output inherently deduplicated. Eliminates the O(n log n) sort.

Join Hash Simplification (Phase E)

Original join row IDs used nested hash calls: pg_trickle_hash_multi(ARRAY[dl.__pgt_row_id::TEXT, pg_trickle_hash_multi(ARRAY[r."id"::TEXT])::TEXT]) — 2 Rust FFI crossings + 3 TEXT casts per row. Flattened to a single pg_trickle_hash_multi(ARRAY[...]) call, reducing from 2 hash calls to 1 per row.

Where Time is Spent (Current)

With pipeline overhead reduced to sub-2ms on cache hits, MERGE execution dominates all scenarios (69–93% of total refresh time). The remaining performance is determined by PostgreSQL’s execution of the delta CTE chain and MERGE statement — not by the extension’s orchestration layer.

Phase 100K/1% 100K/10%
Decision <1ms <2ms
Gen+Build <1ms <1ms
MERGE 3–8ms 13–98ms
Cleanup <1ms 3–5ms

Warm-Cycle Performance (INCR cycles 2+, excludes cold-start)

Scenario 10K/1% 100K/1% 100K/10%
scan 6.9ms 12.8ms 107.8ms
filter 7.8ms 17.0ms 54.3ms
join 10.0ms 29.9ms 111.7ms
aggregate 6.0ms 8.3ms 40.7ms
join_agg 17.5ms 18.5ms 52.9ms

Full Evolution (scan 100K/1% — flagship scenario)

Phase INCR ms INCR/FULL Ratio Key Change
Baseline 572.4 0.7× Bug: manual refresh ignored mode
After P1+P2 ~135 2.7× Caching + JSONB fixes
After P3 ~135 2.5× SPI reduction (count→EXISTS)
After P5 (P7) 46.5 7.0× LATERAL VALUES, deferred cleanup
After P6 (Part 6) 8.3 41.7× Scan fast path, join hash, aggregate dedup

Adaptive Threshold

The system automatically falls back from INCREMENTAL to FULL refresh when the change rate exceeds a configurable threshold (default: 15%). This prevents the pathological case where delta processing on large change sets is slower than full recomputation. The threshold can be tuned per stream table via the pg_trickle.differential_max_change_ratio GUC.

Part 9 — Regression Fixes & Benchmark Infrastructure (Sessions 1–2)

Date: 2026-03-15

Rust-Side Micro-Optimizations

Function Before After Change Root Cause
prefixed_col_list/20 ~4.0 µs Expected ~2.8 µs ~30% improvement Eliminated intermediate Vec allocation; stream directly to String
lsn_gt("0/0", "0/1") ~79 ns Expected ~55 ns ~30% improvement Replaced split('/').collect::<Vec<_>>() with split_once('/')
col_list/10 ~1.37 µs Expected ~1.1 µs ~20% improvement Same optimization as prefixed_col_list

Benchmark Infrastructure Improvements

ID Improvement Impact
I-1c just bench-docker target Run Criterion inside Docker when local pg_stub fails
I-2 [BENCH_CYCLE] parseable output Enables histogram analysis, trend detection
I-3 PGS_BENCH_EXPLAIN=true Captures EXPLAIN (ANALYZE, BUFFERS) for delta queries
I-6 1M-row benchmark tier Tests production-scale tables (10K changes at 1%)
I-8 sample_size(200), measurement_time(10s) Reduces outlier rate from 10–19% to expected <5%