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

## v0.14.0 — Tiered Scheduling, UNLOGGED Buffers & Diagnostics

**Status: Released (2026-04-02).**

Tiered refresh scheduling, UNLOGGED change buffers, refresh mode diagnostics,
error-state circuit breaker, a full-featured TUI dashboard, security
hardening (SECURITY DEFINER triggers with explicit search_path), GHCR Docker
image, pre-deployment checklist, best-practice patterns guide, and
comprehensive E2E test coverage. See [CHANGELOG.md](CHANGELOG.md) for the
full feature list.

### Quick Polish & Error State Circuit Breaker (Phase 1 + 1b) — ✅ Done

- **C4:** `pg_trickle.planner_aggressive` GUC consolidates `merge_planner_hints` + `merge_work_mem_mb`. Old GUCs deprecated.
- **DIAG-2:** Creation-time WARNING for group-rescan and low-cardinality algebraic aggregates. `agg_diff_cardinality_threshold` GUC added.
- **DOC-OPM:** Operator support matrix summary table linked from `SQL_REFERENCE.md`.
- **ERR-1:** Permanent failures immediately set `ERROR` status with `last_error_message`/`last_error_at`. API calls clear error state. E2E test pending.

### Manual Tiered Scheduling (Phase 2 — C-1) — ✅ Done

Tiered scheduling infrastructure was already in place since v0.11/v0.12 (`refresh_tier` column, `RefreshTier` enum, `ALTER ... SET (tier=...)`, scheduler multipliers). Phase 2 verified completeness and added:

- **C-1b:** NOTICE on tier demotion from Hot to Cold/Frozen, alerting operators to the effective interval change.
- **C-1c:** Scheduler tier-aware multipliers confirmed: Hot ×1, Warm ×2, Cold ×10, Frozen = skip. Gated by `pg_trickle.tiered_scheduling` (default `true` since v0.12.0).

### UNLOGGED Change Buffers (Phase 3 — D-1) — ✅ Done

- **D-1a:** `pg_trickle.unlogged_buffers` GUC (default `false`). New change buffer tables created as `UNLOGGED` when enabled, reducing WAL amplification by ~30%.
- **D-1b:** Crash recovery detection — scheduler detects UNLOGGED buffers emptied by crash (postmaster restart after last refresh) and auto-enqueues FULL refresh.
- **D-1c:** `pgtrickle.convert_buffers_to_unlogged()` utility function for converting existing logged buffers. Documents lock-window warning.
- **D-1e:** Documentation in `CONFIGURATION.md` and `SQL_REFERENCE.md`.

### Documentation: Best-Practice Patterns Guide (G16-PAT) — ✅ Done

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| ~~G16-PAT~~ | ~~**Best-practice patterns guide.** `docs/PATTERNS.md`: 6 patterns (Bronze/Silver/Gold, event sourcing, SCD type-1/2, high-fan-out, real-time dashboards, tiered refresh) with SQL examples, anti-patterns, and refresh mode recommendations.~~ | — | ✅ Done |

> **Patterns guide subtotal: ✅ Done**

### Long-Running Stability & Multi-Database Testing (G17-SOAK, G17-MDB) — ✅ Done

> Soak test validates zero worker crashes, zero ERROR states, and stable RSS
> under sustained mixed DML. Multi-database test validates catalog isolation,
> shared-memory independence, and concurrent correctness.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| ~~G17-SOAK~~ | ~~**Long-running stability soak test.** `tests/e2e_soak_tests.rs` with configurable duration, 5 source tables, mixed DML, health checks, RSS monitoring, correctness verification. `just test-soak` / `just test-soak-short`. CI job: schedule + manual dispatch.~~ | — | ✅ Done |
| ~~G17-MDB~~ | ~~**Multi-database scheduler isolation test.** `tests/e2e_mdb_tests.rs` with two databases, catalog isolation assertion, concurrent mutation cycles, correctness verification per database. `just test-mdb`. CI job: schedule + manual dispatch.~~ | — | ✅ Done |

> **Stability & multi-database testing subtotal: ✅ Done**

### Container Infrastructure (INFRA-GHCR)

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| INFRA-GHCR | **GHCR Docker image.** `Dockerfile.ghcr` (pinned to `postgres:18.3-bookworm`) + `.github/workflows/ghcr.yml` workflow that builds a multi-arch (`linux/amd64` + `linux/arm64`) PostgreSQL 18.3 server image with pg_trickle pre-installed and all sensible GUC defaults baked in. Smoke-tests on amd64 before push. Published to `ghcr.io/grove/pg_trickle` on every `v*` tag with immutable (`<version>-pg18.3`), floating (`pg18`), and `latest` tags. Uses `GITHUB_TOKEN` — no extra secrets. | 4h | — | ✅ Done |

> **Container infrastructure subtotal: ✅ Done**

### Refresh Mode Diagnostics (DIAG-1) — ✅ Done

> Analyzes stream table workload characteristics and recommends the optimal
> refresh mode. Seven weighted signals (change ratio, empirical timing, query
> complexity, target size, index coverage, latency variance) produce a composite
> score with confidence level and human-readable explanation.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| ~~DIAG-1a~~ | ~~`src/diagnostics.rs` — pure signal-scoring functions + unit tests~~ | — | ✅ Done |
| ~~DIAG-1b~~ | ~~SPI data-gathering layer~~ | — | ✅ Done |
| ~~DIAG-1c~~ | ~~`pgtrickle.recommend_refresh_mode()` SQL function~~ | — | ✅ Done |
| ~~DIAG-1d~~ | ~~`pgtrickle.refresh_efficiency()` function~~ | — | ✅ Done |
| ~~DIAG-1e~~ | ~~E2E integration tests; upgrade migration~~ | — | ✅ Done |
| ~~DIAG-1f~~ | ~~Documentation: SQL_REFERENCE.md additions~~ | — | ✅ Done |

> The function synthesises 7 weighted signals (historical change ratio 0.30,
> empirical timing 0.35, current change ratio 0.25, query complexity 0.10,
> target size 0.10, index coverage 0.05, P95/P50 variance 0.05) into a
> composite score. Confidence degrades gracefully when history is sparse.

> **Diagnostics subtotal: ~3.5–7 days**

### Export Definition API (G15-EX) — ✅ Done

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| ~~G15-EX~~ | ~~**`export_definition(name TEXT)`** — export a stream table configuration as reproducible DDL~~ | — | ✅ Done |

> **G15-EX subtotal: ~1–2 days**

### TUI Tool (E3-TUI)

> **In plain terms:** A full-featured terminal user interface (TUI) for
> managing, monitoring, and diagnosing pg_trickle stream tables without
> touching SQL. Built with ratatui in Rust, it provides a real-time
> dashboard (think `htop` for stream tables), interactive dependency graph
> visualization, live refresh log, diagnostics with signal breakdown charts,
> CDC health monitoring, a GUC configuration editor, and a real-time alert
> feed — all navigable with keyboard shortcuts and a command palette.
> It also supports every original CLI command as one-shot subcommands for
> scripting and CI.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| E3-TUI | TUI tool (`pgtrickle`) for interactive management and monitoring | 8–10d | [PLAN_TUI.md](plans/ui/PLAN_TUI.md) |

> **E3-TUI subtotal: ~8–10 days** (T1–T8 implemented: CLI skeleton with 18 subcommands, interactive dashboard with 15 views, watch mode with `--filter`, LISTEN/NOTIFY alerts with JSON parsing, async polling with force-poll, cascade staleness detection, DAG issue detection, sparklines, fuse detail panel, trigger inventory, context-sensitive help, docs/TUI.md)

### GUC Surface Consolidation (C4)

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| C4 | **Consolidate `merge_planner_hints` + `merge_work_mem_mb` into single `planner_aggressive` boolean.** Reduces GUC surface area; existing two GUCs become aliases that emit a deprecation notice. | ~1–2h | [PLAN_FEATURE_CLEANUP.md §C4](plans/PLAN_FEATURE_CLEANUP.md) |

> **C4 subtotal: ~1–2 hours**

### Documentation: Pre-Deployment Checklist (DOC-PDC) — ✅ Done

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| ~~DOC-PDC~~ | ~~**Pre-deployment checklist page.** `docs/PRE_DEPLOYMENT.md`: 10-point checklist covering PG version, `shared_preload_libraries`, WAL configuration, PgBouncer compatibility, recommended GUCs, resource planning, monitoring, validation script. Cross-linked from GETTING_STARTED.md and INSTALL.md.~~ | — | ✅ Done |

> **DOC-PDC subtotal: ✅ Done**

### Documentation: Operator Mode Support Matrix Cross-Link (DOC-OPM)

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| DOC-OPM | **Cross-link operator support matrix from SQL_REFERENCE.md.** The 60+ operator × FULL/DIFFERENTIAL/IMMEDIATE matrix in DVM_OPERATORS.md is not discoverable from the page users actually read. Add a summary table and prominent link in SQL_REFERENCE.md §Supported SQL Constructs. | ~2–4h | [docs/DVM_OPERATORS.md](docs/DVM_OPERATORS.md) · [docs/SQL_REFERENCE.md](docs/SQL_REFERENCE.md) |

> **DOC-OPM subtotal: ~2–4 hours**

### Aggregate Mode Warning at Creation Time (DIAG-2)

> **In plain terms:** Queries with very few distinct GROUP BY groups (e.g. 5
> regions from 100K rows) are always faster with FULL refresh — differential
> overhead exceeds the cost of re-aggregating a tiny result set. Today users
> discover this only after benchmarking. A creation-time WARNING with an
> explicit recommendation prevents the surprise. The classification logic is
> already present in the DVM parser (aggregate strategy classification from
> `is_algebraically_invertible`, `is_group_rescan`); this item exposes it at
> the SQL boundary.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| DIAG-2 | **Aggregate mode warning at `create_stream_table` time.** After parsing the defining query, inspect the top-level operator: if it is an `Aggregate` node containing non-algebraic (group-rescan) functions such as `MIN`, `MAX`, `STRING_AGG`, `ARRAY_AGG`, `BOOL_AND/OR`, emit a `WARNING` recommending `refresh_mode='full'` or `'auto'` and citing the group-rescan cost. For algebraic aggregates (`SUM`/`COUNT`/`AVG`), emit the warning only when the estimated group cardinality (from `pg_stats.n_distinct` on the GROUP BY columns) is below `pg_trickle.agg_diff_cardinality_threshold` (default: 1000 distinct groups), since below this threshold FULL is reliably faster. No behavior change — warning only. | ~2–4h | [plans/performance/REPORT_OVERALL_STATUS.md §12.3](plans/performance/REPORT_OVERALL_STATUS.md) |

> **DIAG-2 subtotal: ~2–4 hours**

### DIFFERENTIAL Refresh for Manual ST-on-ST Path (FIX-STST-DIFF)

> **Background:** When a stream table reads from another stream table
> (`calculated` schedule), the scheduler propagates changes via a per-ST
> change buffer (`pgtrickle_changes.changes_pgt_{id}`) and performs a true
> DIFFERENTIAL DVM refresh against that buffer. The manual
> `pgtrickle.refresh_stream_table()` path does not: it currently falls back
> to an unconditional `TRUNCATE + INSERT` (FULL refresh) for every call.
>
> This was introduced as a correctness fix in v0.13.0 (PR #371) to close a
> scheduler race where the previous no-op guard could leave stale data in
> place. The FULL fallback is correct but inefficient — it pays a full table
> scan of all upstream STs even when only a small delta is present.
>
> **What needs to happen:** Wire `execute_manual_differential_refresh` to
> use the same `changes_pgt_` change buffers the scheduler already writes.
> When a manual refresh is requested for a `calculated` ST that has a stored
> frontier, check each upstream ST's change buffer for rows with
> `lsn > frontier.get_st_lsn(upstream_pgt_id)`. If new rows exist, apply
> the DVM delta SQL (same as `execute_differential_refresh`). If no rows
> exist beyond the frontier, return a true no-op. This also fixes the
> pre-existing `test_st_on_st_uses_differential_not_full` E2E failure.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| ~~FIX-STST-DIFF~~ | ~~**DIFFERENTIAL manual refresh for ST-on-ST.** In `execute_manual_differential_refresh` (`src/api.rs`), replace the unconditional FULL fallback for `has_st_source` with a proper change-buffer delta path: read rows from `changes_pgt_{upstream_pgt_id}` beyond the stored frontier LSN, run DVM differential SQL, advance the frontier. Matches the scheduler path exactly. Fixes `test_st_on_st_uses_differential_not_full`.~~ | — | ✅ Done |

> **FIX-STST-DIFF subtotal: ~1–2 days**

> **v0.14.0 total: ~2–6 weeks + ~1wk patterns guide + ~2–4 days stability tests + ~3.5–7 days diagnostics + ~1–2d export API + ~8–10d TUI + ~0.5d docs + ~2–4h aggregate warning + ~1–2d ST-on-ST diff manual path**

**Exit criteria:**
- [x] C-1: Tier classification with manual assignment; Cold STs skip refresh correctly; E2E tested ✅ Done
- [x] D-1: UNLOGGED change buffers opt-in (`unlogged_buffers = false` by default); crash-recovery FULL-refresh path tested; E2E tested ✅ Done
- [x] G16-PAT: Patterns guide published in `docs/PATTERNS.md` covering 6 patterns ✅ Done
- [x] G17-SOAK: Soak test passes with zero worker crashes, zero zombie stream tables, stable memory ✅ Done
- [x] G17-MDB: Multi-database scheduler isolation verified ✅ Done
- [x] DIAG-1: `recommend_refresh_mode()` + `refresh_efficiency()` implemented with 7 signals; E2E tested; tutorial published ✅ Done
- [x] DIAG-2: WARNING emitted at creation time for group-rescan and low-cardinality aggregates; threshold configurable ✅ Done
- [x] G15-EX: `export_definition(name TEXT)` returns valid reproducible DDL; round-trip tested ✅ Done
- [x] E3-TUI: `pgtrickle` TUI binary builds as workspace member; one-shot CLI commands functional with `--format json`; interactive dashboard launches with no subcommand; 15 views with cascade staleness, issue detection, sparklines, force-poll, NOTIFY, and context-sensitive help; documented in `docs/TUI.md` ✅ Done
- [x] C4: `merge_planner_hints` and `merge_work_mem_mb` consolidated into `planner_aggressive` ✅ Done
- [x] DOC-PDC: Pre-deployment checklist published in `docs/PRE_DEPLOYMENT.md` ✅ Done
- [x] DOC-OPM: Operator mode support matrix summary and link added to SQL_REFERENCE.md ✅ Done
- [x] FIX-STST-DIFF: Manual DIFFERENTIAL refresh for ST-on-ST path ✅ Done
- [x] INFRA-GHCR: `ghcr.io/grove/pg_trickle` multi-arch image builds, smoke-tests, and pushes on `v*` tags ✅ Done
- [x] ERR-1: Error-state circuit breaker with E2E test coverage ✅ Done
- [x] Extension upgrade path tested (`0.13.0 → 0.14.0`) ✅ Done


---