# v0.87.0 — Low-Impact Refresh

> **Status:** Planned
> **Scope:** Large
> **User promise:** *"Keeping views fresh won't hurt my application."*
> **Blocked by:** [v0.86.0](v0.86.0.md)
> **Renumbered:** previously planned as v0.83.0.

## Theme

The single most common reason a DBA rejects an IVM extension is fear of what it
does to the primary workload. This release makes that fear measurable and then
removes it: capture overhead on the write path, refresh spikes that steal CPU
from OLTP, long lock holds during MERGE, and unbounded memory on large deltas.

The goal is stated as a number, not a vibe:

> **Installing pg_trickle should have very little impact on normal
> INSERT/UPDATE/DELETE latency**, and that claim is enforced by a benchmark gate
> in CI rather than asserted in the README.

## Items

### LOW-1: Pipelined refresh execution

Replace the current pattern (materialize the full delta, then MERGE the entire
set) with a streaming pipeline:

1. Execute the delta SQL through a cursor/portal — no full materialization
2. Stream result rows in batches of `pipeline_batch_size` (default 4096)
3. Generate and execute a partial MERGE per batch
4. Commit incrementally (one sub-transaction per batch)

Benefits:

- peak memory drops from O(delta_size) to O(batch_size)
- lock hold time drops to one batch instead of one refresh
- first rows become visible sooner (progressive freshness)
- a large delta on one stream table no longer blocks every other refresh

New GUC: `pg_trickle.pipeline_batch_size = 4096`. Implemented with SPI cursors
in-process; falls back to the existing single-MERGE path when the delta is
smaller than one batch.

**This supersedes QW-9 (chunked MERGE) from v0.81.0.** QW-9 materialises the
full delta into a temp table and then batches the MERGE, which bounds lock hold
time but not peak memory. Shipping both would leave two batching mechanisms
with two GUCs and two failure modes. QW-9's `merge_batch_size` is retained as a
deprecated alias for one release and then removed; the migration path is
documented in `UPGRADING.md`.

### LOW-2: Cheaper capture on the write path

Reduce the per-row cost paid by the application's own transactions:

- **Statement-level batching.** Row triggers accumulate into a per-statement
  transition buffer and write to the change buffer once per statement instead
  of once per row, using transition tables where available.
- **Column pruning at capture time.** Only columns referenced by the defining
  query (plus key and CDC columns) are written to the change buffer. Wide tables
  stop paying for columns nobody reads.
- **Unlogged and shrinking change buffers.** Change buffers become candidates
  for reduced WAL volume where durability semantics allow, with the existing
  frontier/replay path unchanged.
- **Trigger short-circuit.** When every consumer of a source is paused or
  suspended, the trigger becomes a cheap no-op instead of a full capture.

### LOW-3: Shared-memory change buffer ring — **deferred past v1.0**

The original plan captured writes for hot sources into a fixed-size lock-free
shared-memory ring instead of a heap table. It is removed from the pre-1.0 path
for three reasons, and recorded in [v1.7.0](v1.7.0.md-full.md) alongside the
other write-path decoupling work:

1. **It was specified as single-producer/single-consumer.** A source doing more
   than 10,000 writes/s is being written by many concurrent backends. The
   producer side is inherently multi-writer, so the lock-free claim does not
   hold as written and the real design is a contended shared-memory allocator.
2. **Its crash story contradicts the CDC architecture.** "Cleared on crash
   recovery; the change buffer is rebuilt from the recorded WAL position" is
   only true in WAL/logical capture mode. pg_trickle's default capture is
   trigger-based (ADR-001/ADR-002) and has no WAL position to rebuild from, so
   a crash would silently lose committed changes — precisely what
   [v0.82.0](v0.82.0.md) exists to make impossible.
3. **The measured problem may not survive LOW-1 and LOW-2.** Statement-level
   batching plus capture-time column pruning attack the same cost. The ring is
   a large, unsafe optimisation to commit to before the cheap ones are measured.

If the published benchmark in LOW-6 still shows unacceptable capture overhead
after LOW-2 lands, the ring returns as a designed, opt-in feature with a
durability story that survives a crash — not as a default.

### LOW-4: Backpressure and yielding to the application

pg_trickle must be a well-behaved tenant of the database it lives in:

- **Load-aware deferral.** The scheduler samples backend count, lock waits and
  CPU pressure; when the instance is under load it lengthens intervals and
  defers non-urgent refreshes rather than competing with OLTP.
- **Refresh spike smoothing.** Stream tables due at the same instant are
  jittered and rate-limited so a fleet of 5-second tables does not produce a
  synchronized stampede every 5 seconds.
- **Bounded concurrency.** A global cap on concurrently refreshing stream
  tables, independent of worker count, with a fair queue so one expensive table
  cannot starve the rest.
- **Lock etiquette.** The bounded `lock_timeout` / `statement_timeout`
  machinery shipped as OPS-81-4 in [v0.85.0](v0.85.0.md) is extended, not
  re-implemented: deadlines become derived from the refresh interval rather
  than only from a configured maximum, and a refresh that cannot acquire its
  locks yields and retries instead of blocking application transactions.

### LOW-5: Bounded memory, end to end

Every unbounded accumulation point gets an explicit ceiling and an observable
counter: delta materialization (LOW-1), change buffer growth per source, the
invalidation ring, template/plan caches (bounded in v0.81.0), and the DAG
scheduling queue. A single GUC, `pg_trickle.memory_budget_mb`, sizes them
together, and `health_check()` reports which component is closest to its bound.

### LOW-6: The overhead benchmark, as a CI gate

A `pgbench`-based harness measures p50/p95/p99 latency and TPS on a standard
OLTP workload in three configurations: extension absent, extension installed
with no stream tables, and extension installed with a realistic set of stream
tables over the benchmarked tables.

Published, versioned results plus a regression gate:

| Metric | Gate |
|--------|------|
| TPS with extension installed, no stream tables | ≥ 99% of baseline |
| p99 write latency with stream tables over hot tables | within a documented, enforced budget |
| Refresh CPU share under sustained OLTP load | bounded and reported |

The exact budgets are set from the first measured run and then defended; the
release does not ship until the numbers are published in `docs/`.

## Exit criteria

- [ ] Pipelined refresh is the default path for deltas larger than one batch;
      peak RSS during a 10M-row delta is bounded by `pipeline_batch_size`
- [ ] QW-9 chunked MERGE removed or reduced to a deprecated alias; exactly one
      batching mechanism remains
- [ ] Statement-level batching and column pruning are on by default; write-path
      microbenchmark shows the improvement versus v0.86.0
- [ ] Scheduler defers under synthetic OLTP load and recovers when load drops
- [ ] `pg_trickle.memory_budget_mb` bounds all five accumulation points;
      `health_check()` names the closest one
- [ ] Overhead benchmark published in `docs/`, wired into CI as a blocking
      regression gate
- [ ] Benchmark result recorded as the input to the LOW-3 decision: ring buffer
      stays deferred unless capture overhead is still unacceptable