# v0.20.0 — Self-Monitoring (pg_trickle Monitors Itself)

> **Full technical details:** [v0.20.0.md-full.md](v0.20.0.md-full.md)

**Status: ✅ Released** | **Scope: Large** (~6 weeks)

> pg_trickle uses its own stream table technology to maintain reactive
> analytics about its own behaviour — detecting anomalies, tuning thresholds
> automatically, and demonstrating incremental view maintenance on a
> non-trivial real workload.

---

## What problem does this solve?

Operators running pg_trickle in production needed answers to questions like:
"Is my differential refresh consistently faster than a full refresh?" and
"Is my change buffer growing unexpectedly?" Answering these required running
diagnostic queries that scanned potentially large history tables on demand.

The self-monitoring release turns these diagnostics into continuously-maintained
stream tables — pg_trickle watches its own behaviour incrementally, the same
way it watches your application data. Answers are always available
instantaneously, without re-scanning history.

---

## Five Self-Monitoring Stream Tables

`pgtrickle.setup_self_monitoring()` creates five stream tables that monitor
pg_trickle's own internal catalog and refresh history:

**`df_efficiency_rolling`** — Rolling averages of differential and full
refresh times per stream table. Replaces expensive full-scan calls to
`refresh_efficiency()` with a continuously-maintained result.

*In plain terms:* "How fast are my stream tables refreshing, on average over
the last hour?" — answered instantly.

**`df_anomaly_signals`** — Detects duration spikes (a refresh that took
3× longer than average), error bursts, and mode oscillation (rapid switching
between DIFFERENTIAL and FULL).

*In plain terms:* "Did anything unusual happen in the last refresh cycle?"

**`df_threshold_advice`** — Multi-cycle threshold recommendations for AUTO
mode. Rather than computing a recommendation from a single data point, this
analyses trends across many cycles and assigns a confidence level.

*In plain terms:* "Should I adjust the AUTO mode threshold for this stream
table?" — with a specific recommendation and confidence rating.

**`df_cdc_buffer_trends`** — Tracks how fast the change buffer is growing
for each source table. Alerts when the buffer is growing toward a size that
will cause a slow refresh.

*In plain terms:* "Is any source table accumulating changes faster than
pg_trickle can process them?"

**`df_scheduling_interference`** — Detects when multiple stream table
refreshes are competing with each other, causing latency spikes due to
contention.

*In plain terms:* "Are my stream tables interfering with each other's
refresh schedules?"

---

## Auto-Apply Threshold Tuning

The `pg_trickle.self_monitoring_auto_apply` configuration option (when set
to `threshold_only`) allows pg_trickle to automatically adjust the AUTO mode
cost threshold for each stream table based on the recommendations from
`df_threshold_advice`, when the confidence is HIGH and the change is
significant.

Changes are rate-limited (at most once per stream table per 10 minutes) and
recorded in the refresh history with the reason `initiated_by = 'SELF_MONITOR'`
for auditability.

*In plain terms:* pg_trickle can tune itself — finding the optimal balance
between differential and full refresh for your specific workload, automatically.

---

## Grafana Self-Monitoring Dashboard

A new Grafana dashboard in `monitoring/grafana/` visualises all five
self-monitoring stream tables with live panels:

- Refresh throughput timeline
- Anomaly heatmap by stream table
- Threshold calibration scatter plot
- CDC buffer growth sparklines
- Scheduling interference matrix

---

## dbt Integration

A new `pgtrickle_enable_monitoring` dbt post-hook macro calls
`setup_self_monitoring()` automatically after a successful `dbt run`,
so dbt users get self-monitoring activated as part of their normal workflow.

---

## Operational Helpers

- **`pgtrickle.recommend_refresh_mode(name)`** — returns an instant
  recommendation based on `df_threshold_advice`, rather than computing
  one on demand
- **`pgtrickle.explain_dag()`** — returns the full stream table dependency
  graph as a Mermaid diagram, with colour-coding for user vs self-monitoring
  tables
- **`pgtrickle.scheduler_overhead()`** — shows the fraction of scheduler
  time spent on self-monitoring tables vs user tables (target: < 1%)

---

## Scope

v0.20.0 demonstrates pg_trickle's own value proposition on itself — a
non-trivial incremental analytics workload maintained continuously with
negligible overhead. The five self-monitoring stream tables replace on-demand
diagnostic queries with always-current reactive analytics, and the auto-apply
feature closes the feedback loop from monitoring to optimisation.