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

## v0.27.0 — Operability, Observability & DR

**Status: Planned.** Sourced from [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §4, §7, §9 — the remaining actionable items not addressed in v0.24.0–v0.26.0.

> **Release Theme**
> This release closes the final pre-1.0 operability gaps identified in the
> v0.23.0 deep-analysis report. Four complementary themes: (1) a **snapshot
> and PITR API** so fresh replicas can bootstrap from a point-in-time
> export rather than re-running the full defining query; (2) a
> **predictive maintenance window planner** that turns the v0.22 cost model
> into actionable schedule recommendations; (3) a **cluster-wide
> observability layer** exposing per-database worker allocation from the
> postmaster and adding per-DB Prometheus metric labels; and (4)
> **OpenMetrics conformance hardening** for the metrics endpoint, including
> cluster-wide aggregation and a conformance test. Together these items
> leave pg_trickle well-positioned for the v1.0 stable release.

### Stream-Table Snapshot & Point-in-Time Restore

> **In plain terms:** `snapshot_stream_table()` exports the current content
> of a stream table — its frontier, content hash, and all rows — into an
> archival companion table. `restore_from_snapshot()` rehydrates that state
> on a fresh replica in seconds, skipping the full defining-query
> re-execution. Aligns ST state with logical wall-clock for PITR workflows.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| SNAP-1 | **`snapshot_stream_table(name, target)` SQL function.** Exports `(pgt_id, frontier, content_hash, rows)` to an archival table `pgtrickle.snapshot_<name>_<timestamp>`. Creates the table if it does not exist; overwrites with `CREATE TABLE … AS SELECT`. Snapshot includes the frontier LSN and current `pgt_stream_tables` metadata row. `SnapshotAlreadyExists` error variant if `target` is given and already occupied. | 2d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §7 |
| SNAP-2 | **`restore_from_snapshot(name, source)` SQL function.** Rehydrates a stream table from a snapshot table created by SNAP-1. Replays the archived frontier into `pgt_stream_tables`, bulk-inserts rows, skips the initial full-refresh cycle. `SnapshotSourceNotFound`, `SnapshotSchemaVersionMismatch` error variants. | 2d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §7 |
| SNAP-3 | **`list_snapshots(name)` + `drop_snapshot(snapshot_table)`.** Monitoring function returning all snapshots for a given ST (name, creation time, row count, frontier, size_bytes). `drop_snapshot` drops the archival table and removes it from the metadata catalog. | 0.5d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §7 |
| SNAP-4 | **Tests.** Integration: snapshot → drop ST → restore → verify rows and frontier match; schema-version mismatch returns error; snapshot on IMMEDIATE-mode ST. E2E: fresh-replica bootstrap via snapshot completes in < 5 s for 1M-row ST. | 1d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §7 |
| SNAP-5 | **Documentation.** SQL_REFERENCE.md: snapshot/restore API. PATTERNS.md: "Replica Bootstrap & PITR Alignment" section. BACKUP_AND_RESTORE.md: updated to cover the snapshot path alongside `pg_dump`. | 0.5d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §7 |

> **Snapshot/PITR subtotal: ~6 days**

### Predictive Maintenance Window Planner

> **In plain terms:** `recommend_schedule(name)` analyses the per-ST
> cost-model history (accumulated since v0.22) and returns a recommended
> `refresh_interval`, peak-window `cron` expression, and confidence score.
> A longer-term extension can flag expected cost spikes in advance so
> operators can act before SLA breaches occur.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| PLAN-1 | **`pgtrickle.recommend_schedule(name)` SQL function.** Returns a single JSONB row with `recommended_interval_seconds`, `peak_window_cron`, `confidence` (0–1), and `reasoning` (text). Uses the per-ST `last_full_ms`/`last_diff_ms` history and the v0.25.0 median+MAD model. Confidence is `0.0` if fewer than `pg_trickle.schedule_recommendation_min_samples` (default 20) observations are available. | 2d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §7 |
| PLAN-2 | **`pgtrickle.schedule_recommendations()` set-returning function.** Returns one row per registered ST with `name`, `current_interval_seconds`, `recommended_interval_seconds`, `delta_pct`, `confidence`, `reasoning`. Sortable by `delta_pct DESC` so operators can quickly find the most mis-tuned STs. | 1d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §7 |
| PLAN-3 | **Spike-forecast alert.** Post-tick hook: if the cost model predicts the next refresh will exceed the ST's SLA by > 20 %, emit a `pg_trickle_alert` event `predicted_sla_breach` with `stream_table`, `predicted_ms`, and `sla_ms`. Alert is debounced — at most one per `pg_trickle.schedule_alert_cooldown_seconds` (default 300 s). | 1.5d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §7 |
| PLAN-4 | **Tests + documentation.** Unit: `recommend_schedule` returns `confidence = 0.0` before `min_samples`; returns non-trivial recommendation after synthetic history injection; spike-forecast alert fires exactly once per cooldown window. SQL_REFERENCE.md: `recommend_schedule` + `schedule_recommendations` API. CONFIGURATION.md: two new GUCs. | 1d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §7 |

> **Predictive planner subtotal: ~5.5 days**

### Cluster-Wide Observability

> **In plain terms:** The v0.25.0 `worker_allocation_status()` view covers
> per-database quota usage but only from within a single database connection.
> This adds a postmaster-level cluster summary visible from any database, tags
> every Prometheus metric with `db_oid` (enabling per-DB Grafana panels across
> a cluster), and publishes a multi-tenant deployment guide.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| CLUS-1 | **`pgtrickle.cluster_worker_summary()` SQL function.** Reads the shared-memory worker-pool shmem block (already populated by all DB bgworkers) and returns one row per database: `db_oid`, `db_name`, `workers_active`, `workers_queued`, `quota`, `quota_utilization_pct`. Accessible from any database in the cluster without cross-DB SPI. | 2d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §4 |
| CLUS-2 | **Per-DB Prometheus metric labels.** Tag all metrics emitted by `src/metrics_server.rs` with `db_oid=<oid>` and `db_name=<name>` labels. Enables per-DB Grafana panels and per-DB alerting rules without separate endpoints. | 1d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §4, §9 |
| CLUS-3 | **`docs/integrations/multi-tenant.md` (new page).** Documents recommended multi-DB deployment patterns: quota allocation formula (`ceil(total_workers / N_databases)`), GUC configuration, Grafana dashboard snippets using `db_name` labels, and `cluster_worker_summary()` usage. | 0.5d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §4 |
| CLUS-4 | **`docs/SCALING.md` update.** Add a "Cluster-wide worker fairness" section cross-referencing `cluster_worker_summary()`, the new quota GUC documentation, and the multi-tenant integration page. | 0.5d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §4 |

> **Cluster observability subtotal: ~4 days**

### OpenMetrics Conformance & Metrics Hardening

> **In plain terms:** The `src/metrics_server.rs` endpoint introduced in
> v0.20 has zero unit tests and no validation that its output conforms to
> the OpenMetrics text format. This item adds a conformance test, port-conflict
> and timeout handling, and a cluster-wide aggregation view.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| METR-1 | **OpenMetrics conformance test.** Parse the `/metrics` output with the `openmetrics_parser` crate (or equivalent) and assert no validation errors. Run as a unit test in `src/metrics_server.rs` using a mock request. | 1d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §9 |
| METR-2 | **Port-conflict and timeout unit tests.** Test that `metrics_server::start()` returns a typed `MetricsServerError::PortInUse` when the port is occupied, and `MetricsServerError::Timeout` when the request handler exceeds `pg_trickle.metrics_request_timeout_ms` (new GUC, default 5000 ms). | 1d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §9 |
| METR-3 | **`pgtrickle.metrics_summary()` cluster-wide aggregation view.** Set-returning function that aggregates key counters across all databases visible in `pg_stat_activity` (refresh count, error count, worker utilisation). Feeds the cluster-level Grafana overview dashboard. | 1.5d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §9 |
| METR-4 | **Malformed-HTTP handler.** Catch malformed HTTP requests to the metrics endpoint; return 400 Bad Request with a plain-text error body rather than panicking. Add unit test. | 0.5d | [PLAN_OVERALL_ASSESSMENT_2.md](plans/PLAN_OVERALL_ASSESSMENT_2.md) §9 |

> **Metrics hardening subtotal: ~4 days**

### Dependency Upgrades

> **In plain terms:** pgrx 0.18.0 updates the proc-macro and SPI interfaces.
> This item upgrades the dependency, audits all `pg_sys::*` call sites for
> breaking changes, and validates the full test suite under the new version.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| DEP-1 | **pgrx 0.17.0 → 0.18.0 upgrade.** Bump `pgrx` and `pgrx-tests` in `Cargo.toml`; run `cargo pgrx init` for the target PG 18 version; resolve any API breakage in proc-macro annotations, SPI call sites, memory-context helpers, and `pg_sys::*` usages. | 1–2d | — |
| DEP-2 | **Full test suite validation.** Run `just test-all` under pgrx 0.18.0; fix any regressions. Update `AGENTS.md` pgrx version reference. | 0.5d | — |

> **Dependency upgrades subtotal: ~1.5–2.5 days**

### Implementation Phases

| Phase | Description | Duration |
|-------|-------------|----------|
| Phase 1 | Snapshot/PITR: catalog, SQL functions, tests, documentation | Days 1–6 |
| Phase 2 | Predictive planner: `recommend_schedule`, `schedule_recommendations`, spike-forecast alert, tests | Days 6–11.5 |
| Phase 3 | Cluster observability: `cluster_worker_summary`, per-DB labels, multi-tenant docs, SCALING.md | Days 11.5–15.5 |
| Phase 4 | Metrics hardening: OpenMetrics conformance, port-conflict tests, aggregation view, malformed-HTTP handler | Days 15.5–19.5 |
| Phase 5 | Dependency upgrades: pgrx 0.18.0, full test-suite validation | Days 19.5–21.5 |
| Phase 6 | Integration testing, upgrade script, documentation review | Days 21.5–24 |

> **v0.27.0 total: ~3–4 weeks** (~24 person-days solo)

**Exit criteria:**
- [x] SNAP-1: `snapshot_stream_table()` creates archival table with correct frontier and row data
- [x] SNAP-2: `restore_from_snapshot()` rehydrates ST; first refresh cycle after restore is DIFFERENTIAL (not FULL)
- [x] SNAP-3: `list_snapshots()` lists all snapshots for a ST; `drop_snapshot()` removes archival table and catalog row
- [x] SNAP-4: Fresh-replica bootstrap via snapshot completes in < 5 s for 1M-row ST
- [x] SNAP-5: BACKUP_AND_RESTORE.md updated; PATTERNS.md "Replica Bootstrap & PITR Alignment" section added
- [x] PLAN-1: `recommend_schedule()` returns `confidence = 0.0` before `min_samples`; returns non-trivial recommendation with synthetic history
- [x] PLAN-2: `schedule_recommendations()` returns one row per ST; sortable by `delta_pct`
- [x] PLAN-3: `predicted_sla_breach` alert fires once per cooldown window; no duplicate alerts
- [x] PLAN-4: All unit tests for planner pass; two new GUCs documented
- [x] CLUS-1: `cluster_worker_summary()` returns accurate per-DB worker counts from any database in the cluster
- [x] CLUS-2: All Prometheus metrics carry `db_oid` and `db_name` labels; existing Grafana dashboard templates updated
- [x] CLUS-3: `docs/integrations/multi-tenant.md` published with quota formula and Grafana snippets
- [x] CLUS-4: `docs/SCALING.md` cluster-wide fairness section added
- [x] METR-1: OpenMetrics conformance test passes; zero parse errors on live `/metrics` output
- [x] METR-2: Port-conflict test returns `MetricsServerError::PortInUse`; timeout test returns `MetricsServerError::Timeout`
- [x] METR-3: `metrics_summary()` returns aggregated counters; Grafana cluster-overview query documented
- [x] METR-4: Malformed HTTP request returns 400 Bad Request; no panic
- [x] DEP-1: pgrx bumped to 0.18.0; all API breakage resolved; extension builds clean
- [x] DEP-2: `just test-all` passes under pgrx 0.18.0; `AGENTS.md` pgrx version reference updated
- [x] Extension upgrade path tested (`0.26.0 → 0.27.0`)
- [x] `just check-version-sync` passes


---