Plain-language companion: v0.31.0.md

v0.31.0 — Performance & Scheduler Intelligence

Status: Planned. Derived from plans/PLAN_OVERALL_ASSESSMENT_3.md §4, §5, §10.3, §10.4.

Release Theme v0.31.0 turns the scheduler from a round-robin dispatcher into an adaptive execution engine. Three orthogonal improvements land together: adaptive batching groups stream tables that share a source so the change-buffer scan is paid once per source per cycle; plan-aware delta routing selects merge_strategy per-refresh from EXPLAIN ANALYZE output instead of requiring per-stream-table tuning; and the L0 shared-shmem dshash cache (if not completed in v0.30.0) finishes the three-tier caching story by placing hot delta SQL in shared memory, making cold backends indistinguishable from warm ones. Each improvement is independently deployable and hidden behind a GUC, so operators can adopt them incrementally.

Performance

ID Title Effort Priority
PERF-1 Adaptive batching: coalesce STs sharing a source table L P1
PERF-2 Plan-aware delta routing: auto-select merge_strategy M P1
PERF-3 IVM lock-mode observability counter XS P1
PERF-4 Switch IVM transition tables to ENR (drop temp tables) M P1
PERF-5 L0 dshash shared-shmem cache (if deferred from v0.30.0) L P1

PERF-1 — Adaptive batching. The scheduler currently dispatches each ready stream table independently. An adaptive batcher notices when two or more stream tables share the same source table and refresh window, and coalesces their change-buffer scans: one SELECT * FROM pgtrickle_changes.changes_<oid> per source table per tick instead of one per downstream stream table. Expected win: 10–30% throughput improvement for multi-tenant deployments with shared sources. Controlled by pg_trickle.adaptive_batch_coalescing GUC (default true). Schema change: No. Dependencies: v0.25.0 scheduler snapshot cache.

PERF-2 — Plan-aware delta routing. Today merge_strategy is a per-stream-table setting requiring manual tuning. After each differential refresh, inspect the EXPLAIN (FORMAT JSON) output from the MERGE; if the estimated cost of delete_insert is lower than merge (e.g. because the index cardinality ratio exceeds a heuristic threshold), switch for the next cycle. The existing heuristic code in src/refresh/codegen.rs provides the skeleton. Controlled by pg_trickle.adaptive_merge_strategy GUC (default false initially). Schema change: No.

PERF-3 — IVM lock-mode counter. IvmLockMode::for_query (src/ivm.rs:48–91) silently falls back to Exclusive on any parse failure. Add a Prometheus counter pgtrickle_ivm_lock_mode_total{mode="exclusive_due_to_parse_error"} and expose it via metrics_summary(). Operators can then audit whether their IMMEDIATE-mode queries are taking unnecessarily broad locks. Schema change: No (new metric only).

PERF-4 — ENR-based IVM transition tables. The IVM trigger functions use PostgreSQL temporary tables to materialise NEW TABLE / OLD TABLE transition data (comment at src/ivm.rs:30–37). PostgreSQL 18 supports referencing ephemeral named relations (ENRs) directly inside trigger bodies, eliminating the intermediate temp-table overhead. Rewrite the trigger function builders to reference the ENR by name. Schema change: No.

Scalability

ID Title Effort Priority
SCAL-1 Back-pressure signal when change buffer exceeds threshold S P2
SCAL-2 Multi-DB singleton refresh broker concept (design only) S P2
SCAL-3 Shared catalog snapshot for metrics_summary() / cluster_worker_summary() (if deferred from v0.30.0) M P2

SCAL-1 — Expose a pgtrickle_alert change_buffer_backpressure event when a change buffer grows past pg_trickle.buffer_alert_threshold for more than N consecutive refresh cycles. This is a signal rather than a throttle — actual back-pressure requires application-level cooperation — but it gives operators the Prometheus alert hook they need.

SCAL-2 — Draft a design document for a “refresh broker” that de-duplicates expensive source-table scans across databases in the same cluster. A foreign- data-wrapper view shared across two databases today causes each scheduler to pay the full scan cost independently. The design doc need not ship code; it should inform v0.32.0+ or Post-1.0 architecture.

Test Coverage

ID Title Effort Priority
TEST-1 Benchmark regression gate for adaptive batching S P1
TEST-2 Benchmark regression gate for plan-aware routing S P1
TEST-3 Integration: ENR trigger vs temp-table parity S P1

Exit Criteria

  • [ ] PERF-1: Adaptive batching reduces change-buffer scan count to 1 per source per tick; benchmark shows ≥ 10% throughput improvement at 50+ STs sharing 5 sources
  • [ ] PERF-2: Plan-aware strategy flip logged; benchmark shows no regression; unit-tested with mock EXPLAIN output
  • [ ] PERF-3: pgtrickle_ivm_lock_mode_total{mode="exclusive_due_to_parse_error"} metric emitted; visible in metrics_summary()
  • [ ] PERF-4: IVM trigger functions reference ENR by name; temp tables eliminated; E2E parity tests pass
  • [ ] Extension upgrade path tested (1.6.0 → 0.31.0)
  • [ ] just check-version-sync passes