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

## v0.38.0 — Embedding Pipeline Infrastructure & ANN Maintenance

**Status: Planned.** Derived from [plans/ecosystem/PLAN_PGVECTOR.md](plans/ecosystem/PLAN_PGVECTOR.md) §6.2 (VP-1 through VP-5).

> **Release Theme**
> v0.38.0 completes the embedding-pipeline feature set by adding post-refresh
> action hooks (REINDEX, ANALYZE, drift-based re-clustering), vector-aggregate
> monitoring and diagnostics, full pgai integration tutorial, and a Docker
> image combining pg_trickle + pgvector + pgai for AI/RAG deployments.

> **Hard prerequisite:** v0.38.0 development must not start until F4 (v0.37.0
> `vector_avg` / `vector_sum`) has landed with `tests/e2e_pgvector_tests.rs`
> green and pgvector present in `tests/Dockerfile.e2e`. All VP items depend
> on the pgvector CI infrastructure established in v0.37.0.

---

### Features

| ID | Title | Effort | Priority |
|----|-------|--------|----------|
| VP-1 | `post_refresh_action` ST option (`none`/`analyze`/`reindex`/`reindex_if_drift`) | M | P1 |
| VP-2 | `reindex_drift_threshold` ST option + drift counter in catalog | M | P1 |
| VP-3 | `pgtrickle.vector_status()` view: embedding lag, ANN age, drift % | S | P1 |
| VP-4 | Cookbook: "Always-fresh RAG with pg_trickle + pgvector + pgai" | S | P2 |
| VP-5 | Docker image variant: `pg_trickle + pgvector + pgai` for one-command RAG | S | P2 |
| VP-6 | `docs/CONFIGURATION.md` overhaul: enumerate all GUCs with defaults | M | P1 |

**VP-1 — Post-refresh actions.**
Add a `post_refresh_action` option to `pgtrickle.create_stream_table()`:

```sql
SELECT pgtrickle.create_stream_table(
  name => 'embeddings_st',
  query => $$SELECT id, embedding FROM docs$$,
  post_refresh_action => 'reindex_if_drift'
);
```

Valid values: `none` (default), `analyze`, `reindex`, `reindex_if_drift`.
- `none`: no post-action.
- `analyze`: run `ANALYZE` on the stream table after refresh commits.
- `reindex`: run `REINDEX` on all indexes for the stream table.
- `reindex_if_drift`: conditional reindex based on VP-2 threshold (see below).

Runs in a separate transaction *after* the refresh commits (not in the refresh
transaction itself), so it does not lengthen the refresh window. Goes into the
lower-priority tier of the SLA scheduler (v0.22) so it never blocks
refresh-cadence SLAs.

**VP-2 — Drift detection.**
Catalog extension: add `reindex_drift_threshold FLOAT` and
`rows_changed_since_last_reindex INT8` columns to `pgtrickle.pgt_stream_tables`.

After each refresh, increment `rows_changed_since_last_reindex` by the delta row
count. When `post_refresh_action = 'reindex_if_drift'`, check if
`rows_changed_since_last_reindex >= table_size * reindex_drift_threshold`.
If so, queue an `REINDEX` action and reset the counter to 0.

Default threshold: 0.10 (reindex after 10% of rows have changed since last
reindex). Users can tune per-table with `ALTER STREAM TABLE … SET
(reindex_drift_threshold = 0.05)`.

**VP-3 — Vector status view.**
Add a monitoring view `pgtrickle.vector_status()` that shows:

```
stream_table | last_refresh_at | embedding_lag_s | agg_count | last_reindex_at | rows_since_reindex | drift_pct | vector_agg_type
```

Columns:
- `stream_table` — ST name
- `last_refresh_at` — timestamp of last refresh (from `pgt_stream_tables.last_refresh_lsn` timestamp)
- `embedding_lag_s` — seconds since last refresh (for SLA monitoring)
- `agg_count` — number of groups if the ST uses a vector aggregate
- `last_reindex_at` — timestamp of last successful REINDEX
- `rows_since_reindex` — count since last reindex (absolute)
- `drift_pct` — `(rows_since_reindex / table_size) * 100`
- `vector_agg_type` — `vector_avg`, `vector_sum`, or `null` if no vector aggregate

Used with the v0.20+ self-monitoring system to detect and alert on:
- Embedding staleness (lag > SLA)
- Excessive drift (drift_pct approaching threshold)
- Reindex overhead (frequency vs. drift_pct trade-off)

> **Known limitation (document proactively):** `vector_status()` joins across
> multiple catalog tables with per-ST cost. It is not designed for deployments
> with more than ~100k stream tables; use the Prometheus scrape endpoint for
> fleet-scale monitoring. This must be documented in the view's SQL comment
> and in `docs/CONFIGURATION.md`.

**VP-6 — `docs/CONFIGURATION.md` overhaul.**
The extension has grown to 111+ GUC definitions across `src/config.rs` (as of
v0.34.0 assessment). `docs/CONFIGURATION.md` does not enumerate all of them;
operators cannot tune without reading source. v0.37.0 adds 3 new GUCs
(`enable_vector_agg`, `otel_endpoint`, `enable_trace_propagation`); v0.38.0
adds per-table options (`post_refresh_action`, `reindex_drift_threshold`).

This overhaul:
- Adds an auto-generated GUC reference table to `docs/CONFIGURATION.md`
  (driven by a script that greps `GucRegistry::define_*_guc` call sites).
- Adds a per-table stream-table options reference alongside.
- Marks each GUC/option with: default value, valid range, scope
  (per-session / per-table / server), and which release added it.

This addresses the operational ergonomics gap identified in OA7 §5 before it
compounds further.

**VP-4 — pgai cookbook.**
`docs/tutorials/PGVECTOR_RAG_COOKBOOK.md`:

- Pattern 1: Pre-compute embeddings into a column with pgai, then stream table
  maintains denormalised corpus
- Pattern 2: Always-fresh document corpus with metadata (docs + tags + ACL joins)
  indexed with HNSW for similarity search
- Pattern 3: Embedding refresh on source change (using pgai or external service)
- Pattern 4: Hybrid search (BM25 + vector + metadata-filter on a single flat
  stream table)
- Appendix: resource sizing (memory, disk, `max_parallel_workers_per_gather`)

Copy-paste examples for all patterns.

**VP-5 — Docker image.**
Add `Dockerfile.pgai-bundle` that extends `postgres:18-bookworm` with:
- pg_trickle (built from current release)
- pgvector (0.8+)
- pgai (Timescale's pgai extension)
- pgml (if license allows)
- dbt-postgres (for dbt projects)

Published to Docker Hub as `grove/pg-trickle:0.38.0-pgai`.

### Test Coverage

| ID | Title | Effort | Priority |
|----|-------|--------|----------|
| T-VP1 | Integration test: drift-based reindex on HNSW stream table | M | P1 |
| T-VP2 | Citus integration: `vector_avg` shard-additive property verified | M | P2 |

**T-VP1.**
Test in `tests/e2e_pgvector_tests.rs`:

1. Create embedding stream table with 1000 rows, `post_refresh_action = 'reindex_if_drift'`, `reindex_drift_threshold = 0.05`.
2. Create HNSW index on the embedding column.
3. Insert 100 new rows (10% change), trigger refresh.
4. Assert REINDEX was queued and completed (observable from v0.20+ self-monitoring).
5. Query `pgtrickle.vector_status()` and verify `drift_pct < 1%` (reset after reindex).

**T-VP2.**
For distributed Citus deployments, verify that `vector_avg` is shard-additive:
- Compute `vector_avg` per shard on a partitioned stream table.
- Verify final result == `avg(vector)` computed across all shards.

> **Assessment note (2026-04-27):** The exit criteria below listed T-VP2 with
> a soft "if v0.33+ Citus support available" gate. This has been hardened:
> T-VP2 is a **required** exit criterion. If the Citus test infrastructure is
> not available at the time of the release, explicitly defer T-VP2 to v0.39.0
> with a tracked issue — do not silently skip it.

### Conflicts & Risks

- **VP-1/VP-2** (post-refresh actions) require careful queuing to avoid
  scheduler queue overflow. Approach: store action in catalog and check queue
  depth before enqueueing; drop with warning if queue full. Document backlog
  visibility in `pgtrickle.vector_status()`.
- **VP-3** (vector_status view) joins across multiple catalogs; performance
  with millions of STs not yet characterized. Add a note: "Not for queries
  over 100k STs; use Prometheus scrape if needed."
- **VP-5** (Docker image) must pin specific versions of pgvector, pgai, pgml to
  avoid breakage on minor updates. Add version documentation.

### Exit Criteria

- [ ] **Prerequisite:** F4 (v0.37.0 `vector_avg`) merged and `tests/e2e_pgvector_tests.rs` green
- [ ] VP-1: `post_refresh_action` option stored in catalog, scheduler correctly dequeues actions
- [ ] VP-2: `rows_changed_since_last_reindex` counter incremented per refresh; drift threshold comparison logic tested over 1000 refresh cycles
- [ ] VP-3: `pgtrickle.vector_status()` view returns correct lag, drift_pct for all vector ST types; query plan optimized; scale limitation documented in SQL comment and `docs/CONFIGURATION.md`
- [ ] VP-4: Cookbook published with 4+ worked examples; copy-paste code tested
- [ ] VP-5: Docker image builds and passes smoke test (pg_trickle + pgvector + pgai extensions load, `pg_extension_info()` lists all three)
- [ ] VP-6: `docs/CONFIGURATION.md` overhaul complete; auto-generated GUC table covers all 111+ GUCs; per-table options reference added
- [ ] T-VP1: `tests/e2e_pgvector_tests.rs::test_drift_reindex_e2e` passes
- [ ] T-VP2: Citus `vector_avg` shard-additive integration tests pass (or deferred with tracked issue if Citus infra unavailable)
- [ ] Extension upgrade path tested (`0.37.0 → 0.38.0`)
- [ ] `just check-version-sync` passes

---
