Plain-language companion: v0.12.0.md

v0.12.0 — Correctness, Reliability & Developer Tooling

Goal: Close the last known wrong-answer bugs in the incremental query engine, add SQL-callable diagnostic functions for observability, harden the scheduler against edge cases uncovered with deeper topologies, and back the whole release with thousands of automatically generated property and fuzz tests.

Phases 5–8 from the original v0.12.0 scope (Scalability Foundations, Partitioning Enhancements, MERGE Profiling, and dbt Macro Updates) have been moved to v0.13.0 to keep this release tightly focused on correctness and reliability. See §v0.13.0 for those items.

Status: Released (2026-03-28).

Anomalous Change Detection (Fuse)

In plain terms: Imagine a source table suddenly receives a million-row batch delete — a bug, runaway script, or intentional purge. Without a fuse, pg_trickle would try to process all of it and potentially overload the database. This adds a circuit breaker: you set a ceiling (e.g. “never process more than 50,000 changes at once”), and if that limit is hit the stream table pauses and sends a notification. You investigate, fix the root cause, then resume with reset_fuse() and choose how to recover (apply the changes, reinitialize from scratch, or skip them entirely).

Per-stream-table fuse that blows when the change buffer row count exceeds a configurable fixed ceiling or an adaptive μ+kσ threshold derived from pgt_refresh_history. A blown fuse halts refresh and emits a pgtrickle_alert NOTIFY; reset_fuse() resumes with a chosen recovery action.

Item Description Effort Ref
FUSE-1 Catalog: fuse state columns on pgt_stream_tables (fuse_mode, fuse_state, fuse_ceiling, fuse_sensitivity, blown_at, blow_reason) 1–2h PLAN_FUSE.md
FUSE-2 alter_stream_table() new params: fuse, fuse_ceiling, fuse_sensitivity 1h PLAN_FUSE.md
FUSE-3 reset_fuse(name, action => 'apply'|'reinitialize'|'skip_changes') SQL function 1h PLAN_FUSE.md
FUSE-4 fuse_status() introspection function 1h PLAN_FUSE.md
FUSE-5 Scheduler pre-check: count change buffer rows; evaluate threshold; blow fuse + NOTIFY if exceeded 2–3h PLAN_FUSE.md
FUSE-6 E2E tests: normal baseline, spike → blow, reset, diamond/DAG interaction 4–6h PLAN_FUSE.md

Anomalous change detection subtotal: ~10–14 hours

Correctness — EC-01 Deep Fix (≥3-Scan Join Right Subtrees)

In plain terms: The phantom-row-after-DELETE bug (EC-01) was fixed for join children with ≤2 scan nodes on the right side. Wider join chains — TPC-H Q7, Q8, Q9 all qualify — are still silently affected: when both sides of a join are deleted in the same batch, the DELETE can be silently dropped. The existing EXCEPT ALL snapshot strategy causes PostgreSQL to spill multi-GB temp files for deep join trees, which is why the threshold exists. This work designs a fundamentally different per-subtree snapshot strategy that removes the cap.

Item Description Effort Ref
EC01B-1 Design and implement a per-subtree CTE-based snapshot strategy to replace EXCEPT ALL for right-side join chains with ≥3 scan nodes; remove the join_scan_count(child) <= 2 threshold in use_pre_change_snapshot ✅ Done — src/dvm/operators/join_common.rs · plans/PLAN_EDGE_CASES.md §EC-01
EC01B-2 TPC-H Q7/Q8/Q9 regression tests: combined left-DELETE + right-DELETE in same cycle; assert no phantom-row drop ✅ Done — tests/e2e_tpch_tests.rs

EC-01 deep fix subtotal: ~3–4 weeks — ✅ Complete

CDC Write-Side Overhead Benchmark

In plain terms: Every INSERT/UPDATE/DELETE on a source table fires a PL/pgSQL trigger that writes to the change buffer. We have never measured how much write throughput this costs. These benchmarks quantify it across five scenarios (single-row, bulk INSERT, bulk UPDATE, bulk DELETE, concurrent writers) and gate the decision on whether to implement a change_buffer_unlogged GUC that could reduce WAL overhead by ~20–30%.

Item Description Effort Ref
BENCH-W1 Implement tests/e2e_cdc_write_overhead_tests.rs: compare source-only vs. source + stream table DML throughput across five scenarios; report write amplification factor ✅ Done — tests/e2e_cdc_write_overhead_tests.rs
BENCH-W2 Publish results in docs/BENCHMARK.md ✅ Done — docs/BENCHMARK.md

CDC write-side benchmark subtotal: ~3–5 days — ✅ Complete

DAG Topology Benchmark Suite (from PLAN_DAG_BENCHMARK.md)

In plain terms: Production deployments form DAGs with 10–500+ stream tables arranged in chains, fan-outs, diamonds, and mixed topologies. This benchmark suite measures end-to-end propagation latency and throughput through these DAG shapes, validates the theoretical latency formulas from PLAN_DAG_PERFORMANCE.md, and provides regression detection for DAG propagation overhead.

Item Description Effort Ref
DAG-B1 Session 1: Infrastructure, linear chain topology builder, latency + throughput measurement drivers, reporting (ASCII/JSON), 7 benchmark tests ✅ Done — PLAN_DAG_BENCHMARK.md §11.1
DAG-B2 Session 2: Wide DAG + fan-out tree topology builders; 9 latency + throughput tests (5 wide + 2 fan-out latency, 2 throughput) ✅ Done — PLAN_DAG_BENCHMARK.md §11.2
DAG-B3 Session 3: Diamond + mixed topology builders; 5 latency + throughput tests; per-level breakdown reporting ✅ Done — PLAN_DAG_BENCHMARK.md §11.3
DAG-B4 Session 4: Update docs/BENCHMARK.md, full suite validation run ✅ Done — PLAN_DAG_BENCHMARK.md §11.4

DAG topology benchmark subtotal: ~3–5 days — ✅ Complete

Developer Tooling & Observability Functions (from REPORT_OVERALL_STATUS.md §15) ✅ Complete

In plain terms: pg_trickle’s diagnostic toolbox today is limited to explain_st() and refresh_history(). Operators debugging unexpected mode changes, query rewrites, or error patterns must read source code or server logs. This section adds four SQL-callable diagnostic functions that surface internal state in a structured, queryable form.

Item Description Effort Status
DT-1 explain_query_rewrite(query TEXT) — parse a query through the DVM pipeline and return the rewritten SQL plus a list of passes applied (operator rewrites, delta-key injections, TopK detection, group-rescan classification). Useful for debugging unexpected refresh behavior without creating a stream table. ~1–2d ✅ Done in v0.12.0 Phase 2
DT-2 diagnose_errors(name TEXT) — return the last 5 error events for a stream table, classified by type (correctness, performance, config, infrastructure), with a suggested remediation for each class. ~2–3d ✅ Done in v0.12.0 Phase 2
DT-3 list_auxiliary_columns(name TEXT) — list all __pgt_* internal columns injected into the stream table’s query plan with their purpose (delta tracking, row identity, compaction key). Helps users understand unexpected columns in SELECT * output. ~1d ✅ Done in v0.12.0 Phase 2
DT-4 validate_query(query TEXT) — parse and run DVM validation on a query without creating a stream table; return the resolved refresh mode, detected SQL constructs (group-rescan aggregates, non-equijoins, multi-scan subtrees), and any warnings. ~1–2d ✅ Done in v0.12.0 Phase 2

Developer tooling subtotal: ~5–8 days

Parser Safety, Concurrency & Query Coverage (from REPORT_OVERALL_STATUS.md §13/§12/§17)

Additional correctness and robustness items from the deep gap analysis: a stack-overflow prevention guard for pathological queries, a concurrency stress test for IMMEDIATE mode, and two investigations into known under- documented query constructs.

Item Description Effort Ref
G13-SD Parser recursion depth limit. Add a recursion depth counter to all recursive parse-tree visitor functions in dvm/parser.rs. Return PgTrickleError::QueryTooComplex if depth exceeds pg_trickle.max_parse_depth (GUC, default 64). Prevents stack-overflow crashes on pathological queries. ✅ Done — src/dvm/parser.rs · src/config.rs · src/error.rs
G17-IMS IMMEDIATE mode concurrency stress test. 100+ concurrent DML transactions on the same source table in IMMEDIATE refresh mode; assert zero lost updates, zero phantom rows, and no deadlocks. ✅ Done — tests/e2e_immediate_concurrency_tests.rs
G12-SQL-IN Multi-column IN (subquery) correctness investigation. Determine behavior when DVM encounters EXPR IN (subquery returning multiple columns). Add a correctness test; if the construct is broken, fix it or document as unsupported with a structured error. ✅ Done — documented as unsupported — tests/e2e_multi_column_in_tests.rs · src/dvm/parser.rs
G14-MDED MERGE deduplication profiling. Profile how often concurrent-write scenarios produce duplicate key entries requiring pre-MERGE compaction. If ≥10% of refresh cycles need dedup, write an RFC for a two-pass MERGE strategy. ~3–5d plans/performance/REPORT_OVERALL_STATUS.md §14
G17-MERGEEX MERGE template EXPLAIN validation in E2E tests. Add EXPLAIN (COSTS OFF) dry-run checks for generated MERGE SQL templates at E2E test startup. Catches malformed templates before any data is processed. ✅ Done — tests/e2e_merge_template_tests.rs

Parser safety & coverage subtotal: ~9–15 days

Differential Fuzzing (SQLancer)

In plain terms: SQLancer is a SQL fuzzer that generates thousands of syntactically valid but structurally unusual queries and uses mathematical oracles (NoREC, TLP) to prove our DVM engine produces exactly the same results as PostgreSQL’s native executor. Unlike hand-written tests, it explores the long tail of NULL semantics, nested aggregations, and edge cases no human would write. Any backend crash or result mismatch becomes a permanent regression test seed.

Item Description Effort Ref
SQLANCER-1 Docker-based harness: just sqlancer spins up E2E container; crash-test oracle verifies that no SQLancer-generated create_stream_table call crashes the backend 3–4d PLAN_SQLANCER.md §Steps 1–2 | ✅ Done in v0.12.0 Phase 4
SQLANCER-2 Equivalence oracle: for each generated query Q, assert create_stream_table + refresh output equals native SELECT (multiset comparison); failures auto-committed as proptest regression seeds 3–4d PLAN_SQLANCER.md §Step 3 | ✅ Done in v0.12.0 Phase 4
SQLANCER-3 CI weekly-sqlancer job (daily schedule + manual dispatch); new proptest seed files committed on any detected correctness failure 1–2d PLAN_SQLANCER.md | ✅ Done in v0.12.0 Phase 4

SQLancer fuzzing subtotal: ~1–2 weeks

Property-Based Invariant Tests (Items 5 & 6)

In plain terms: Items 1–4 of the property test plan are done. These two remaining items add topology/scheduler stress tests (random DAG shapes with multi-source branch interactions) and pure Rust unit-level properties (ordering monotonicity, SCC bookkeeping correctness). Both slot into the existing proptest harness and provide coverage that example-based tests cannot exhaustively explore.

Item Description Effort Ref
PROP-5 Topology / scheduler stress: randomized DAG topologies with multi-source branch interactions; assert no incorrect refresh ordering or spurious suspension 4–6d PLAN_TEST_PROPERTY_BASED_INVARIANTS.md §Item 5 | ✅ Done in v0.12.0 Phase 4
PROP-6 Pure Rust DAG / scheduler helper properties: ordering invariants, monotonic metadata helpers, SCC bookkeeping edge-cases 2–4d PLAN_TEST_PROPERTY_BASED_INVARIANTS.md §Item 6 | ✅ Done in v0.12.0 Phase 4

Property testing subtotal: ~6–10 days

Async CDC — Research Spike (D-2)

In plain terms: A custom PostgreSQL logical decoding plugin could write changes directly to change buffers without the polling round-trip, cutting CDC latency by ~10× and WAL decoding CPU by 50–80%. This milestone scopes a research spike only — not a full implementation — to validate the key technical constraints.

Item Description Effort Ref
D2-R Research spike: prototype in-memory row buffering inside pg_trickle_decoder; validate SPI flush in commit callback; document memory-safety constraints and feasibility; produce a written RFC before any full implementation is started 2–3 wk PLAN_NEW_STUFF.md §D-2

⚠️ SPI writes inside logical decoding change callbacks are not supported. All row buffering must occur in-memory within the plugin’s memory context; flush only in the commit callback. In-memory buffers must handle arbitrarily large transactions. See PLAN_NEW_STUFF.md §D-2 risk analysis before writing any C code.

Retraction candidate (D-2): Even as a research spike, this item introduces C-level complexity (custom output plugin memory management, commit-callback SPI failure handling, arbitrarily large transaction buffering) that substantially exceeds the stated 2–3 week estimate once the architectural constraints are respected. The risk rating is Very High and the SPI-in-change-callback infeasibility makes the originally proposed design non-functional. Recommend moving D-2 to a post-1.0 research backlog entirely; do not include it in a numbered milestone until a separate feasibility study (outside the release cycle) produces a concrete RFC.

D-2 research spike subtotal: ~2–3 weeks

Scalability Foundations (pulled forward from v0.13.0)

In plain terms: These items directly serve the project’s primary goal of world-class performance and scalability. Columnar change tracking eliminates wasted delta processing for wide tables, and shared change buffers reduce I/O multiplication in deployments with many stream tables reading from the same source.

Item Description Effort Ref
A-2 Columnar Change Tracking. Per-column bitmask in CDC triggers; skip rows where no referenced column changed; lightweight UPDATE-only path when only projected columns changed; 50–90% delta-volume reduction for wide-table UPDATE workloads. 3–4 wk PLAN_NEW_STUFF.md §A-2 | ✅ Done
D-4 Shared Change Buffers. Single buffer per source shared across all dependent STs; multi-frontier cleanup coordination; static-superset column mode for initial implementation. 3–4 wk PLAN_NEW_STUFF.md §D-4 | ✅ Done

Scalability foundations subtotal: ~6–8 weeks

Partitioning Enhancements (A1 follow-ons from v0.11.0 spike)

In plain terms: The v0.11.0 spike delivered RANGE partitioning end-to-end. These follow-on items extend coverage to the use cases deliberately deferred from A1: multi-column keys, retrofitting existing stream tables, LIST-based partitions, HASH partitions (which need a different strategy than predicate injection), and operational quality-of-life improvements.

Item Description Effort Ref
A1-1b Multi-column partition keys. Comma-separated partition_by; PARTITION BY RANGE (col_a, col_b); multi-column MIN/MAX extraction; ROW() comparison predicates for partition pruning. ✅ Done — parse_partition_key_columns(), composite extract_partition_range(), ROW comparison in inject_partition_predicate(); 5 unit tests + 3 E2E tests — src/api.rs, src/refresh.rs
A1-1c alter_stream_table(partition_by => …) support. Add/change/remove partition key on existing stream tables; alter_stream_table_partition_key() handles DROP + recreate + full refresh; update_partition_key() in catalog; SQL migration adds parameter; also fixed alter_stream_table_query to preserve partition key. ✅ Done — 4 E2E tests — src/api.rs, src/catalog.rs
A1-1d LIST partitioning support. partition_by => 'LIST:col' creates PARTITION BY LIST storage; PartitionMethod enum dispatches LIST vs RANGE; extract_partition_bounds() uses SELECT DISTINCT for LIST; inject_partition_predicate() emits IN (…) predicate; single-column-only validation. ✅ Done — 16 unit tests + 4 E2E tests — src/api.rs, src/refresh.rs
A1-3b HASH partitioning via per-partition MERGE loop. partition_by => 'HASH:col[:N]' creates PARTITION BY HASH storage with N auto-created child partitions; execute_hash_partitioned_merge() materializes delta → discovers children via pg_inherits → per-child MERGE filtered through satisfies_hash_partition(); build_hash_child_merge() rewrites MERGE targeting ONLY child_partition. ✅ Done — 22 unit tests + 6 E2E tests — src/api.rs, src/refresh.rs
PART-WARN Default-partition growth warning. warn_default_partition_growth() emits pgrx::warning!() after FULL and DIFFERENTIAL refresh when the default partition has rows; includes example DDL. ✅ Done — 2 E2E tests — src/refresh.rs

Auto-partition creation (TimescaleDB-style automatic chunk management) remains a post-1.0 item as stated in PLAN_PARTITIONING_SPIKE.md §10.

Partitioning enhancements subtotal: ~5–8 weeks

Performance Defaults (from REPORT_OVERALL_STATUS.md)

Targeted improvements identified in the overall status report. None require large design changes; all build on existing infrastructure.

Item Description Effort Ref
PERF-2 Auto-enable buffer_partitioning for high-throughput sources. ✅ Done — should_promote_inner() throughput-based heuristic; convert_buffer_to_partitioned() runtime migration; auto-promote hook in execute_differential_refresh(); docs/CONFIGURATION.md updated; 10 unit tests + 3 E2E tests — REPORT_OVERALL_STATUS.md §R7
PERF-3 Flip tiered_scheduling default to true. The feature is implemented and tested since v0.10.0. ✅ Done — default flipped; CONFIGURATION.md updated with tier thresholds section — src/config.rs · docs/CONFIGURATION.md
PERF-1 Adaptive scheduler wake interval. ➡️ Pulled forward to v0.11.0 as WAKE-1. — REPORT_OVERALL_STATUS.md §R3/R16
PERF-4 Flip block_source_ddl default to true. ➡️ Pulled forward to v0.11.0 as DEF-5. — REPORT_OVERALL_STATUS.md §R12
PERF-5 Wider changed-column bitmask (>63 columns). ➡️ Pulled forward to v0.11.0 as WB-1/WB-2. — REPORT_OVERALL_STATUS.md §R13

Performance defaults subtotal: ~1–3 weeks

DAG Refresh Performance Improvements (from PLAN_DAG_PERFORMANCE.md §8)

➡️ Moved to v0.11.0 — these items build directly on the ST-to-ST differential infrastructure shipped in v0.11.0 Phase 8 and are most impactful while that work is fresh.

v0.12.0 total: ~18–27 weeks + ~6–8 weeks scalability + ~5–8 weeks partitioning enhancements + ~1–3 weeks defaults + ~3–5 weeks developer tooling & observability

Priority tiers: P0 = Phases 1–3 (must ship); P1 = Phases 4 + 7 (target); P2 = Phases 5, 6, 8 (can defer to v0.13.0 as a unit — never partially ship Phase 5/6).

dbt Macro Updates (Phase 8)

Priority P2 — Expose the v0.11.0 SQL API additions (partition_by, fuse, fuse_ceiling, fuse_sensitivity) in the dbt materialization macros so dbt users can configure them via config(...). No catalog changes; pure Jinja/SQL. Can defer to v0.13.0 as a unit.

Item Description Effort
DBT-1 partition_by config option wired through stream_table.sql, create_stream_table.sql, and alter_stream_table.sql ~1d
DBT-2 fuse, fuse_ceiling, fuse_sensitivity config options wired through the materialization and alter macro with change-detection logic ~1–2d
DBT-3 dbt docs update: README and SQL_REFERENCE.md dbt section ~0.5d

dbt macro updates subtotal: ~2–3.5 days

Exit criteria — all met (v0.12.0 Released 2026-03-28): - [x] EC01B-½: No phantom-row drop for ≥3-scan right-subtree joins; TPC-H Q7/Q8/Q9 DELETE regression tests pass ✅ - [x] BENCH-W: Write-side overhead benchmarks published in docs/BENCHMARK.md ✅ - [x] DAG-B1–B4: DAG topology benchmark suite complete ✅ - [x] SQLANCER-½/3: Crash-test + equivalence oracles in weekly CI job; zero mismatches ✅ - [x] PROP-5+6: Topology stress and DAG/scheduler helper property tests pass ✅ - [x] DT-1–4: explain_query_rewrite(), diagnose_errors(), list_auxiliary_columns(), validate_query() callable from SQL ✅ - [x] G13-SD: max_parse_depth guard active; pathological query returns QueryTooComplex ✅ - [x] G17-IMS: IMMEDIATE mode concurrency stress test (5 scenarios × 100+ concurrent DML) passes ✅ - [x] G12-SQL-IN: Multi-column IN subquery documented as unsupported with structured error + EXISTS hint ✅ - [x] G17-MERGEEX: MERGE template EXPLAIN validation at E2E test startup ✅ - [x] PERF-3: tiered_scheduling default is true; CONFIGURATION.md updated ✅ - [x] ST-ST-9: Content-hash pk_hash in ST change buffers; stale-row-after-UPDATE bug fixed ✅ - [x] DAG-4 bypass column types fixed; parallel worker tests complete without timeout ✅ - [x] docs/UPGRADING.md updated with v0.11.0→v0.12.0 migration notes ✅ - [x] scripts/check_upgrade_completeness.sh passes ✅ - [x] Extension upgrade path tested (0.11.0 → 0.12.0) ✅