# v0.90.0 — Freshness Controller & Self-Tuning

> **Status:** Planned
> **Scope:** Large
> **User promise:** *"Tell it how fresh I need data; it figures out the rest."*
> **Blocked by:** [v0.89.0](v0.89.0.md)
> **Renumbered:** previously v0.85.0. The user-facing `target_freshness`
> control moved forward to [v0.86.0](v0.86.0.md) so the knob v1.0 freezes gets
> real usage before the freeze; this release makes it authoritative.

## Theme

Users do not want to reason about scheduler intervals, batch sizes, worker
counts and fallback thresholds. They want to say how stale the answer is
allowed to be. [v0.86.0](v0.86.0.md) started accepting that statement. This
release makes pg_trickle act on it continuously and demotes every other knob to
an override.

> **"Tell pg_trickle how fresh you need the answer, not how to schedule it."**

This mirrors where the surrounding ecosystem has landed — PostgreSQL's own
materialized views still require explicit `REFRESH MATERIALIZED VIEW`, while
Timescale exposes continuous-aggregate refresh policies and Materialize exposes
freshness and lag as first-class concepts. Freshness is the concept users
already have; pg_trickle should accept it directly.

## Items

### SLA-1: `target_freshness` becomes authoritative

```sql
SELECT pgtrickle.create_stream_table(
    'sales_by_region',
    $$SELECT region, SUM(amount) FROM orders GROUP BY region$$,
    target_freshness => '2 seconds'
);

SELECT pgtrickle.alter_stream_table(
    'sales_by_region', target_freshness => '1 minute'
);
```

`target_freshness` is defined as the p95 age of the data visible in the stream
table, measured commit-to-visible using the metric shipped in v0.81.0. In
v0.86.0 it was translated once into the existing settings. From this release the
scheduler owns every derived parameter continuously, and the explicit knobs
(`refresh_interval`, batch sizes, concurrency) become optional overrides that
are reported as such by `explain()`.

Special values `'on_commit'` and `'manual'` keep the meaning defined in
v0.86.0.

### SLA-2: The controller

A closed-loop controller per stream table decides, once per scheduler tick:

- **when** to refresh (interval derived from observed refresh cost and inflow
  rate, not a fixed timer)
- **DIFFERENTIAL vs FULL**, using measured cost rather than a static delta-ratio
  threshold
- **batch size** for pipelined refresh, from observed row width and memory
  budget
- **concurrency**, within the global bound from v0.87.0
- **priority**, so a 1-second SLA outranks a 1-hour SLA under contention
- **whether to defer**, when the instance is overloaded (v0.87.0 backpressure)

The controller uses hysteresis — a decision changes only after three consecutive
consistent measurements — and its inputs and last decision are exposed in
`explain()` so behaviour is never mysterious.

### SLA-3: Adaptive worker pool sizing

Replace the static worker count with automatic sizing driven by real signals:

- CPU utilisation > 70% → reduce workers by one (do not starve OLTP)
- refresh queue depth > 2× workers → add one worker
- all workers idle for three consecutive ticks → shrink to the configured floor
- an SLA at risk of breach → add workers up to the configured ceiling

New GUCs: `pg_trickle.adaptive_workers = true`,
`pg_trickle.adaptive_workers_min = 1`, `pg_trickle.adaptive_workers_max = 8`
(bounded by `max_worker_processes` and by the authoritative cluster limit from
[v0.85.0](v0.85.0.md) OPS-81-1).

> The names `pg_trickle.min_workers` / `pg_trickle.max_workers` are **not**
> available: `pg_trickle.max_workers` is a retired GUC that `just docs-lint`
> blocks in active documentation. Reusing a retired name with new semantics
> would break the lint and mislead anyone upgrading from a version that had it.

### SLA-4: SLA reporting and alerting

Freshness becomes a reported, checkable status rather than something users infer
from timestamps:

```
SELECT * FROM pgtrickle.freshness();
```

```
 stream_table      | target | p50    | p95    | p99    | status
-------------------+--------+--------+--------+--------+---------------
 sales_by_region   | 2s     | 310ms  | 730ms  | 1.4s   | meeting SLA
 customer_360      | 5s     | 4.1s   | 9.8s   | 21s    | BREACHING
```

- `pg_stat_pgtrickle` (shipped v0.86.0) gains `p95_freshness_ms` and
  `sla_status`; `target_freshness_ms` is already there
- `health_check()` reports sustained breaches with the controller's diagnosis
  (for example: *"refresh cost 8.2s exceeds 5s target; consider a coarser
  target or reducing join breadth"*)
- Prometheus/OTel metrics expose target, actual and breach duration

### SLA-5: Continuous infeasible-SLA detection

The creation-time check shipped in v0.86.0 (UX-7). This release adds the
continuous half: a stream table whose workload has grown past feasibility is
flagged in `health_check()` and by the controller instead of consuming
resources in a losing race.

```
WARNING:  target_freshness '500 ms' is not achievable for this query
DETAIL:   Minimum observed refresh cost is 3.4 s (dominated by orders → line_items join).
HINT:     Use target_freshness >= '5 s', or narrow the defining query.
```

### SLA-6: Retire the knobs users should not need

Every GUC and per-stream-table option is audited and classified as *user-facing*,
*advanced override*, or *internal*. Internal knobs are removed or hidden;
advanced overrides are documented as "only if the controller is wrong, and please
tell us why". The documented configuration surface shrinks measurably, and the
config advisor from v0.81.0 becomes a translator from workload description to
`target_freshness` rather than a list of settings to hand-tune.

## Exit criteria

- [ ] The controller owns every derived parameter for stream tables with a
      `target_freshness`; explicit knobs are reported as overrides by
      `explain()`
- [ ] Controller meets p95 targets for a mixed workload of 100 stream tables
      across 1s/10s/1m targets in a soak test
- [ ] Adaptive worker sizing demonstrated to shrink under OLTP load and grow
      under SLA pressure, without oscillation, and never above the
      authoritative cluster worker limit from v0.85.0
- [ ] `pgtrickle.freshness()` and `sla_status` shipped, documented and exported
      to Prometheus/OTel
- [ ] Infeasible targets flagged continuously, not only at creation
- [ ] Documented configuration surface reduced; every remaining user-facing GUC
      justified in `docs/CONFIGURATION.md`
- [ ] No GUC removed in SLA-6 without a deprecation entry in `UPGRADING.md`;
      removals land here rather than after the v0.93.0 feature freeze