> **Plain-language companion:** [v0.31.0.md](v0.31.0.md)

## v0.31.0 — Performance & Scheduler Intelligence

**Status: ✅ Released.** Derived from [plans/PLAN_OVERALL_ASSESSMENT_3.md](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

- [x] 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
- [x] PERF-2: Plan-aware strategy flip logged; benchmark shows no regression; unit-tested with mock EXPLAIN output
- [x] PERF-3: `pgtrickle_ivm_lock_mode_total{mode="exclusive_due_to_parse_error"}` metric emitted; visible in `metrics_summary()`
- [x] PERF-4: IVM trigger functions reference ENR by name; temp tables eliminated; E2E parity tests pass
- [x] Extension upgrade path tested (`0.30.0 → 0.31.0`)
- [x] `just check-version-sync` passes

---