Plain-language companion: v0.33.0.md

v0.33.0 — Reactive Subscriptions & Zero-Downtime Operations

Status: Planned. Derived from plans/PLAN_OVERALL_ASSESSMENT_3.md §10.1, §10.8.

Release Theme v0.33.0 delivers two long-requested operational capabilities. Reactive subscriptions expose stream-table changes as PostgreSQL NOTIFY events, enabling browser-side reactive UIs and event-driven microservices with nothing but a standard PG connection — no Kafka, no Debezium, no Hasura. Zero-downtime view evolution adds a shadow-ST mode to ALTER QUERY that builds the new query’s materialisation side-by-side with the live table, then swaps atomically — eliminating the operational risk of running ALTER QUERY on a large production stream table.

Correctness

ID Title Effort Priority
CORR-1 Reactive subscription: coalesce NOTIFY storms S P0

CORR-1 — The subscribe API must coalesce rapid successive changes into a single NOTIFY payload (or a “changes pending” signal) when the refresh interval is shorter than the LISTEN client’s poll loop. Implement a pg_trickle.notify_coalesce_ms GUC (default 250 ms) and a per-stream-table flag that debounces NOTIFY calls.

Ease of Use

ID Title Effort Priority
UX-1 pgtrickle.subscribe(name, channel) and unsubscribe() SQL functions M P0
UX-2 pg_trickle.notify_coalesce_ms GUC XS P0
UX-3 ALTER QUERY shadow-ST mode (shadow_build := true parameter) L P1
UX-4 pgtrickle.view_evolution_status(name) monitoring function S P1
UX-5 Documentation: subscribe() quick-start + shadow-ST runbook M P1

UX-1 — Reactive subscription API. pgtrickle.subscribe(stream_table TEXT, channel TEXT) registers a per-stream- table listener: after every successful differential or full refresh, if the delta is non-empty, the refresh path emits pg_notify(channel, payload_jsonb::text) within the same transaction. The payload carries {"name": …, "refresh_id": …, "inserted_count": N, "deleted_count": N}. Build on the pg_notify infrastructure already wired by the v0.28.0 outbox. pgtrickle.unsubscribe(name, channel) removes the registration; pgtrickle.list_subscriptions() returns all active registrations. Schema change: Yes — new pgtrickle.pgt_subscriptions catalog table.

UX-3 — Shadow-ST for zero-downtime ALTER QUERY. Today ALTER QUERY triggers a full refresh of the stream table. For tables with millions of rows, this causes a multi-minute outage during which the stream table is locked. A shadow_build := true parameter to alter_query() creates a parallel stream table __pgt_shadow_<name> built from the new query, refreshes it to convergence in the background without locking the live table, then atomically swaps the storage tables and drops the shadow. The live table is readable and writable throughout. The new query goes live at the next refresh cycle after the swap. Schema change: Yes — add in_shadow_build BOOLEAN and shadow_table_name TEXT columns to pgtrickle.pgt_stream_tables.

Test Coverage

ID Title Effort Priority
TEST-1 E2E: subscribe() receives NOTIFY on every non-empty refresh M P0
TEST-2 E2E: NOTIFY coalescing under high-frequency refresh S P1
TEST-3 E2E: shadow-ST ALTER QUERY while reads/writes are in flight L P1
TEST-4 E2E: shadow-ST rollback if new query fails to converge M P1

Conflicts & Risks

  • UX-3 (shadow-ST) touches the refresh orchestrator and catalog — the highest-change-risk modules. Must ship behind a feature flag, stabilised with the full TPC-H test suite before removing the flag.
  • Shadow-ST competes with the live stream table’s change buffer, potentially doubling CDC write overhead during the build window. Add a shadow_refresh_throttle_ms GUC to rate-limit background refreshes.

Exit Criteria

  • [ ] UX-1: subscribe() / unsubscribe() / list_subscriptions() registered; NOTIFY emitted on non-empty refresh; pgt_subscriptions catalog table in migration script
  • [ ] CORR-1: NOTIFY coalescing tested at 10 Hz refresh; client receives ≤ 1 NOTIFY per notify_coalesce_ms window
  • [ ] UX-3: Shadow-ST builds in background; swap is atomic; live table readable throughout; rollback on convergence failure documented
  • [ ] UX-4: view_evolution_status() returns in_progress | converged | failed with row counts
  • [ ] Extension upgrade path tested (0.31.0 → 0.32.0)
  • [ ] just check-version-sync passes