Plain-language companion: v0.15.0.md

v0.15.0 — External Test Suites & Integration

Status: Released (2026-04-03). All 20 roadmap items complete.

Goal: Validate correctness against independent query corpora and ship the dbt integration as a formal release.

External Test Suite Integration

In plain terms: pg_trickle’s own tests were written by the pg_trickle team, which means they can have the same blind spots as the code. This adds validation against three independent public benchmarks: PostgreSQL’s own SQL conformance suite (sqllogictest), the Join Order Benchmark (a realistic analytical query workload), and Nexmark (a streaming data benchmark). If pg_trickle produces a different answer than PostgreSQL does on the same query, these external suites will catch it.

Validate correctness against independent query corpora beyond TPC-H.

➡️ TS1 and TS2 pulled forward to v0.11.0. Delivering one of TS1 or TS2 is an exit criterion for 0.11.0. TS3 (Nexmark) remains in 0.15.0. If TS1/TS2 slip from 0.11.0, they land here.

Item Description Effort Ref
TS1 sqllogictest: run PostgreSQL sqllogic suite through pg_trickle DIFFERENTIAL mode ➡️ Pulled to v0.11.0 2–3d PLAN_TESTING_GAPS.md §J
TS2 JOB (Join Order Benchmark): correctness baseline and refresh latency profiling ➡️ Pulled to v0.11.0 1–2d PLAN_TESTING_GAPS.md §J
TS3 Nexmark streaming benchmark: sustained high-frequency DML correctness 1–2d PLAN_TESTING_GAPS.md §J

External test suites subtotal: ~1–2 days (TS3 only; TS1/TS2 in v0.11.0) – ✅ TS3 complete

Documentation Review

In plain terms: A full documentation review polishes everything so the product is ready to be announced to the wider PostgreSQL community.

Item Description Effort Ref
I2 Complete documentation review & polish 4–6h docs/

Documentation subtotal: ✅ Done

Bulk Create API (G15-BC)

Item Description Effort Ref
G15-BC bulk_create(definitions JSONB) — create multiple stream tables and their CDC triggers in a single transaction. Useful for dbt/CI pipelines that manage many STs programmatically. ✅ Done ~2–3d plans/performance/REPORT_OVERALL_STATUS.md §15

G15-BC subtotal: ✅ Completed

Parser Modularization (G13-PRF) – ✅ Done

In plain terms: At ~21,000 lines, parser.rs was too large to maintain safely. Split into 5 sub-modules by concern – zero behavior change.

Item Description Effort Ref
G13-PRF Modularize src/dvm/parser.rs. ✅ Done. Split into mod.rs, types.rs, validation.rs, rewrites.rs, sublinks.rs. Added // SAFETY: comments to all ~750 unsafe blocks (~676 newly documented). ~3–4wk plans/performance/REPORT_OVERALL_STATUS.md §13

G13-PRF subtotal: ✅ Completed

Watermark Hold-Back Mode (WM-7) – ✅ Done

In plain terms: The watermark gating system (shipped in v0.7.0) lets ETL producers signal their progress. Hold-back mode adds stuck detection: when a watermark is not advanced within a configurable timeout, downstream stream tables are paused and operators are notified.

Item Description Effort Ref
WM-7 Watermark hold-back mode. watermark_holdback_timeout GUC detects stuck watermarks; pauses downstream gated STs; emits pgtrickle_alert NOTIFY with watermark_stuck event; auto-resumes with watermark_resumed event when watermark advances. ✅ Done PLAN_WATERMARK_GATING.md §4.1

WM-7 subtotal: ✅ Done

Delta Cost Estimation (PH-E1) — ✅ Done

In plain terms: Before executing the MERGE, runs a capped COUNT on the delta subquery to estimate output cardinality. If the count exceeds pg_trickle.max_delta_estimate_rows, emits a NOTICE and falls back to FULL refresh to prevent OOM or excessive temp-file spills.

Item Description Effort Ref
PH-E1 Delta cost estimation. Capped SELECT count(*) FROM (delta LIMIT N+1) before MERGE execution. max_delta_estimate_rows GUC (default: 0 = disabled). Falls back to FULL + NOTICE when exceeded. — PLAN_PERFORMANCE_PART_9.md §Phase E

PH-E1 subtotal: ✅ Complete

dbt Hub Publication (I3) — ✅ Done

In plain terms: dbt-pgtrickle is now prepared for dbt Hub publication. The dbt_project.yml is version-synced (0.15.0), README documents both git and Hub install methods, and a submission guide documents the hubcap PR process. Actual Hub listing requires creating a standalone grove/dbt-pgtrickle repository and submitting a PR to dbt-labs/hubcap.

Item Description Effort Ref
I3 Prepared dbt-pgtrickle for dbt Hub publication. Version synced to 0.15.0, README updated with Hub install snippet, submission guide written. Hub listing pending separate repo creation + hubcap PR. 2–4h dbt-pgtrickle/ · docs/integrations/dbt-hub-submission.md

I3 subtotal: ~2–4 hours — ✅ Complete

Hash-Join Planner Hints (PH-D2) — ✅ Done

In plain terms: Added pg_trickle.merge_join_strategy GUC that lets operators manually override the join strategy used during MERGE. Values: auto (default heuristic), hash_join, nested_loop, merge_join. The existing delta-size heuristics remain the default (auto).

Item Description Effort Ref
PH-D2 Hash-join planner hints. Added merge_join_strategy GUC with manual override for join strategy during MERGE. auto preserves existing delta-size heuristics; hash_join/nested_loop/merge_join force specific strategies. 3–5d PLAN_PERFORMANCE_PART_9.md §Phase D

PH-D2 subtotal: ~3–5 days — ✅ Complete

Shared-Memory Template Cache Research Spike (G14-SHC-SPIKE)

In plain terms: Every new database connection that triggers a refresh pays a 15–50ms cold-start cost to regenerate the MERGE SQL template. With PgBouncer in transaction mode, this happens on every refresh cycle. This milestone scopes a research spike only: write an RFC, build a prototype, measure whether DSM-based caching eliminates the cold-start. Full implementation stays in v0.16.0.

Item Description Effort Ref
G14-SHC-SPIKE Shared-memory template cache research spike. Write an RFC for DSM + lwlock-based MERGE SQL template caching. Build a prototype benchmark to validate cold-start elimination. Full implementation deferred to v0.16.0. 2–3d plans/performance/REPORT_OVERALL_STATUS.md §14

G14-SHC-SPIKE subtotal: ~2–3 days – ✅ RFC complete (plans/performance/RFC_SHARED_TEMPLATE_CACHE.md)

TRUNCATE Capture for Trigger-Mode CDC (TRUNC-1)

In plain terms: WAL-mode CDC detects TRUNCATE on source tables and marks downstream stream tables for reinitialization. But trigger-mode CDC has no TRUNCATE handler — a TRUNCATE silently leaves the stream table stale. Adding a DDL event trigger that catches TRUNCATE and flags affected STs closes this correctness gap.

Item Description Effort Ref
TRUNC-1 TRUNCATE capture for trigger-mode CDC. Add a DDL event trigger or statement-level trigger that detects TRUNCATE on source tables in trigger CDC mode and marks downstream STs for needs_reinit. ✅ Done — CDC TRUNCATE triggers write action='T' marker; refresh engine detects and falls back to FULL. 4–6h plans/adrs/PLAN_ADRS.md ADR-070

TRUNC-1 subtotal: ✅ Completed

Volatile Function Policy GUC (VOL-1)

In plain terms: Volatile functions (random(), clock_timestamp(), etc.) are correctly rejected at stream table creation time in DIFFERENTIAL and IMMEDIATE modes. But there’s no way for users to override this — some want volatile functions in FULL mode. Adding a volatile_function_policy GUC with reject/warn/allow modes gives operators control.

Item Description Effort Ref
VOL-1 pg_trickle.volatile_function_policy GUC. Add a GUC with values reject (default), warn, allow to control volatile function handling. reject preserves current behavior; warn emits WARNING but allows creation; allow silently permits (user accepts correctness risk). ✅ Done 3–5h plans/sql/PLAN_NON_DETERMINISM.md

VOL-1 subtotal: ✅ Completed

Spill-Aware Refresh (PH-E2)

In plain terms: After PH-E1 adds pre-flight cost estimation, PH-E2 adds post-flight monitoring: track temp_bytes from pg_stat_statements after each refresh cycle and auto-adjust if spill is excessive.

Item Description Effort Ref
PH-E2 Spill-aware refresh. Monitor temp_bytes from pg_stat_statements after each refresh cycle. If spill exceeds threshold 3 consecutive times, automatically increase per-ST work_mem override or switch to FULL. Expose in explain_st() as spill_history. ✅ Done 1–2 wk PLAN_PERFORMANCE_PART_9.md §Phase E

PH-E2 subtotal: ✅ Completed

ORM Integration Guides (E5)

In plain terms: Documentation showing how popular ORMs (SQLAlchemy, Django, etc.) interact with stream tables — model definitions, migrations, and freshness checks. Documentation-only work.

Item Description Effort Ref
E5 ORM integrations guide (SQLAlchemy, Django, etc.) 8–12h PLAN_ECO_SYSTEM.md §5

E5 subtotal: ✅ Done

Flyway / Liquibase Migration Support (E4)

In plain terms: Documentation showing how standard migration frameworks interact with stream tables — CREATE/ALTER/DROP patterns, handling CDC triggers across schema migrations. Documentation-only work.

Item Description Effort Ref
E4 Flyway / Liquibase migration support 8–12h PLAN_ECO_SYSTEM.md §5

E4 subtotal: ✅ Done

JOIN Key Change + DELETE Correctness Fix (EC-01) — ✅ Done (pre-existing)

In plain terms: The phantom-row-after-DELETE bug was fixed in v0.14.0 via the R₀ pre-change snapshot strategy. Part 1 of the JOIN delta is split into 1a (inserts ⋈ R₁) + 1b (deletes ⋈ R₀), ensuring DELETE deltas always find the old join partner. The fix was extended to all join depths via the EC-01B-1 per-leaf CTE strategy, and regression tests (EC-01B-2) cover TPC-H Q07, Q08, Q09.

Item Description Effort Ref
EC-01 R₀ pre-change snapshot for JOIN key change + DELETE. Part 1 split into 1a (inserts ⋈ R₁) + 1b (deletes ⋈ R₀). Applied to INNER/LEFT/FULL JOIN. Closes G1.1. — GAP_SQL_PHASE_7.md §G1.1

EC-01 subtotal: ✅ Complete (implemented in v0.14.0)

Multi-Level ST-on-ST Testing (STST-3)

In plain terms: FIX-STST-DIFF (v0.14.0) fixed 2-level stream-table-on-stream-table DIFFERENTIAL refresh. Some 3-level cascade tests exist, but systematic coverage for 3+ level chains — including mixed refresh modes, concurrent DML at multiple levels, and DELETE/UPDATE propagation through deep chains — is missing. This adds a dedicated test matrix to prevent regressions as cascade depth increases.

Item Description Effort Ref
STST-3 Multi-level ST-on-ST test matrix (3+ levels). Systematic coverage: 3-level and 4-level chains, INSERT/UPDATE/DELETE propagation, mixed DIFFERENTIAL/FULL modes, concurrent DML at multiple levels, correctness comparison against materialized-view baseline. 3–5d e2e_cascade_regression_tests.rs

STST-3 subtotal: ✅ Done

Circular Dependencies + IMMEDIATE Mode (CIRC-IMM)

In plain terms: Circular dependencies are rejected at creation time (EC-30), but the interaction between near-circular topologies (e.g. diamond dependencies with IMMEDIATE triggers on both sides) and IMMEDIATE mode is untested territory. This adds targeted testing and, if needed, hardening to ensure IMMEDIATE mode doesn’t deadlock or produce incorrect results on complex dependency graphs. Conditional P1 — can slip to v0.16.0 if no issues surface during other IMMEDIATE-mode work.

Item Description Effort Ref
CIRC-IMM Circular-dependency + IMMEDIATE mode hardening. Test: diamond deps with IMMEDIATE triggers, near-circular topologies, lock ordering under concurrent DML. Add deadlock detection / timeout guard if issues found. 3–5d PLAN_EDGE_CASES.md §EC-30 · PLAN_CIRCULAR_REFERENCES.md

CIRC-IMM subtotal: ✅ Done

Cross-Session MERGE Cache Staleness Fix (G8.1)

In plain terms: When session A alters a stream table’s defining query, session B’s cached MERGE SQL template remains stale until B encounters a refresh error or reconnects. Adding a catalog version counter that is bumped on every ALTER QUERY and checked before each refresh closes this race window.

Item Description Effort Ref
G8.1 Cross-session MERGE cache invalidation. Add a catalog_version counter to pgt_stream_tables, bump on ALTER QUERY / DROP / reinit. Before each refresh, compare cached version to catalog; regenerate template on mismatch. ✅ Done — existing CACHE_GENERATION counter + defining_query_hash provides cross-session + per-ST invalidation without a schema change. 4–6h —

G8.1 subtotal: ✅ Completed

explain_st() Enhancements (EXPL-ENH) — ✅ Done

In plain terms: Small quality-of-life improvements to the diagnostic function: refresh timing statistics, partition source info, and a dependency-graph visualization snippet in DOT format.

Item Description Effort Ref
EXPL-ENH explain_st() enhancements. Added: (a) refresh timing stats (min/max/avg/latest duration from last 20 refreshes), (b) source partition info for partitioned tables, © dependency sub-graph visualization in DOT format. 4–8h PLAN_FEATURE_CLEANUP.md

EXPL-ENH subtotal: ~4–8 hours — ✅ Complete

CNPG Operator Hardening (R4)

In plain terms: Kubernetes-native improvements for the CloudNativePG integration: adopt K8s 1.33+ native ImageVolume (replacing the init-container workaround), add liveness/readiness probe integration for pg_trickle health, and test failover behavior with stream tables.

Item Description Effort Ref
R4 CNPG operator hardening. Adopt K8s 1.33+ native ImageVolume, add pg_trickle health to CNPG liveness/readiness probes, test primary→replica failover with active stream tables. 4–6h PLAN_CLOUDNATIVEPG.md

R4 subtotal: ~4–6 hours – ✅ Complete

v0.15.0 total: ~52–90h + ~2–3d bulk create + ~3–5d planner hints + ~2–3d cache spike + ~3–4wk parser + ~1–2wk watermark + ~2–4wk delta cost/spill + ~2–3d EC-01 + ~3–5d ST-on-ST + ~3–5d CIRC-IMM

Exit criteria: - [x] At least one external test corpus (sqllogictest, JOB, or Nexmark) passes - [x] Complete documentation review done - [x] G15-BC: pgtrickle.bulk_create(definitions JSONB) creates all STs and CDC triggers atomically; tested with 10+ definitions in a single call - [x] G13-PRF: parser.rs split into 5 sub-modules; zero behavior change; all existing tests pass - [x] WM-7: Stuck watermarks detected and downstream STs paused; watermark_stuck alert emitted; auto-resume on watermark advance - [x] PH-E1: Delta cost estimation via capped COUNT on delta subquery; max_delta_estimate_rows GUC; FULL downgrade + NOTICE when threshold exceeded - [x] PH-E2: Spill-aware auto-adjustment triggers after 3 consecutive spills; spill_info exposed in explain_st() - [x] PH-D2: merge_join_strategy GUC with manual override (auto/hash_join/nested_loop/merge_join) - [x] G14-SHC-SPIKE: RFC written; prototype benchmark validates or invalidates DSM-based approach - [x] I2: Complete documentation review done – CONFIGURATION.md GUCs documented (40+), SQL_REFERENCE.md gaps filled, FAQ refs fixed - [x] TRUNC-1: TRUNCATE on trigger-mode CDC source marks downstream STs for reinit; tested end-to-end - [x] VOL-1: volatile_function_policy GUC controls volatile function handling; reject/warn/allow modes tested - [x] I3: dbt-pgtrickle prepared for dbt Hub; submission guide written; Hub listing pending separate repo + hubcap PR - [x] E4: Flyway / Liquibase integration guide published in docs/integrations/flyway-liquibase.md - [x] E5: ORM integration guides (SQLAlchemy, Django) published in docs/integrations/orm.md - [x] EC-01: R₀ pre-change snapshot ensures DELETE deltas find old join partners; unit + TPC-H regression tests confirm correctness - [x] STST-3: 3-level and 4-level ST-on-ST chains tested with INSERT/UPDATE/DELETE propagation; mixed modes covered - [x] CIRC-IMM: Diamond + near-circular IMMEDIATE topologies tested; no deadlocks or incorrect results - [x] G8.1: Cross-session MERGE cache invalidation via catalog version counter; tested with concurrent ALTER QUERY + refresh - [x] EXPL-ENH: explain_st() shows refresh timing stats, source partition info, and dependency sub-graph (DOT format) - [x] R4: CNPG operator hardening — ImageVolume, health probes, failover tested - [x] G13-PRF: parser.rs split into 5 sub-modules; all ~750 unsafe blocks have // SAFETY: comments; zero behavior change; all existing tests pass - [x] Extension upgrade path tested (0.14.0 → 0.15.0) - [x] just check-version-sync passes