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

## v0.39.0 — Hybrid Search & Sparse Vector Aggregates

**Status: Planned.** Derived from [plans/ecosystem/PLAN_PGVECTOR.md](plans/ecosystem/PLAN_PGVECTOR.md) §6.3 (VH-1 through VH-4).

> **Release Theme**
> v0.39.0 completes the vector-aggregate feature set with sparse and
> half-precision types, adds reactive vector-similarity subscriptions for
> real-time anomaly detection, patterns for hybrid-search (BM25 + vector +
> metadata) STs, and comprehensive performance benchmarks for differential
> vector workloads.

---

### Features

| ID | Title | Effort | Priority |
|----|-------|--------|----------|
| VH-1 | `sparsevec_avg`, `sparsevec_sum`, `halfvec_avg`, `halfvec_sum` aggregates | M | P1 |
| VH-2 | Reactive subscriptions over distance predicates (`embedding <=> q < ε`) | M | P1 |
| VH-3 | Hybrid-search corpus cookbook: BM25 + vector + metadata on one ST | S | P2 |
| VH-4 | Benchmark suite: differential refresh + HNSW throughput, aggregate cost | M | P2 |

**VH-1 — Sparse and half-precision aggregates.**
Extend the algebraic-aggregate framework to support pgvector 0.7+ type variants:

- `sparsevec_avg(sparsevec) → sparsevec`: element-wise mean over the union of
  sparse dimensions, treating missing entries as 0.
- `sparsevec_sum(sparsevec) → sparsevec`: element-wise sum.
- `halfvec_avg(halfvec) → halfvec`: upcast running state to `vector(d)` for
  numerical stability, return as `halfvec` on read.
- `halfvec_sum(halfvec) → halfvec`: upcast to vector, sum, return as halfvec.

State representation:
- `sparsevec_avg`: `(sum_sparsevec, count)` — store as JSON or binary tuple in
  aggregate buffer.
- `halfvec_avg`: `(sum_vector, count)` — upcast for stability.

All variants are differential and work in stream tables at any layer of a
multi-level materialization pipeline (raw embedding → halfvec → sparsevec
for progressive quantisation).

**VH-2 — Reactive distance subscriptions.**
Extend reactive subscriptions (v0.35) to support distance-predicate queries:

> **Prerequisite:** VH-2 depends on the v0.35.0 reactive subscription
> implementation being fully signed off (exit criteria passed, no open
> correctness issues). Confirm reactive subscription stability before building
> the distance-predicate extension on top.

```sql
LISTEN new_anomaly;

SELECT pgtrickle.create_reactive_subscription(
  'new_anomaly',
  $$
    SELECT t.id, t.embedding <=> $1::vector AS distance
    FROM transactions_embedded t
    WHERE t.embedding <=> $1::vector < 0.05
  $$,
  params => '{"1": "[0.1, 0.2, ...]"}'::jsonb
);
```

Implementation notes:
- The `$1` parameter is bound at subscription-creation time (not per-query).
- The subscription fires on INSERT/UPDATE/DELETE only if the new/old row
  satisfies the distance predicate.
- Useful for real-time alerts: "alert me when a new embedding enters this
  anomaly cluster."

**VH-3 — Hybrid-search cookbook.**
`docs/tutorials/HYBRID_SEARCH_PATTERNS.md`:

- Pattern 1: Flat denormalised corpus (documents + tags + metadata) with vector
  + BM25 + tag-facet indexes.
- Pattern 2: RLS-scoped corpus (per-tenant or per-org hybrid search).
- Pattern 3: Tiered search (broad vector → narrow BM25 → re-rank by cross-
  encoder).
- Pattern 4: Combining search results from multiple stream tables (e.g., docs
  + comments + metadata, each with their own vector corpus).
- Appendix: performance tuning (index hints, `work_mem`, parallel worker count).

Copy-paste examples for all patterns. Worked example: e-commerce product
search (products × categories × brands × reviews, hybrid corpus with 1M rows,
< 10 ms query latency target).

**VH-4 — Vector benchmark suite.**
Add `benches/pgvector_bench.rs` using the Criterion framework (matching the
TPC-H refresh benchmarks in v0.31):

- **Throughput: differential refresh + HNSW insert.** Measure rows/sec for
  incremental embedding corpus vs. bulk REINDEX over 10k rows.
- **Aggregate reducer cost.** Microseconds per vector for `vector_avg` vs. plain
  numeric `sum`.
- **Drift detection overhead.** Cost of tracking `rows_changed_since_last_reindex`
  per refresh (should be negligible).
- **Storage savings.** Compare table size under `vector` vs. `halfvec` vs.
  `sparsevec` for typical embeddings (1536-dim, 10M rows).

Integrate with the benchmark dashboard (v0.31) — publish results alongside
refresh latency and TPC-H metrics.

### Test Coverage

| ID | Title | Effort | Priority |
|----|-------|--------|----------|
| T-VH1 | Integration test: tiered storage (vector → halfvec → sparsevec) | M | P1 |
| T-VH2 | Reactive distance subscriptions: new anomaly alerts | M | P1 |
| T-VH3 | Hybrid-search corpus under Nexmark-style write load | M | P2 |

**T-VH1.**
`tests/e2e_pgvector_tiered_tests.rs`:

1. Create embedding stream table on source documents.
2. Create halfvec stream table on the embedding table (cast to halfvec).
3. Create sparsevec stream table on the halfvec table (binary quantise).
4. Insert new rows at source; verify all three layers refresh correctly.
5. Query `pgtrickle.vector_status()` and verify appropriate aggregates on each
   layer.
6. Assert tiered indexes (HNSW on each) return consistent results (same nearest
   neighbours up to quantisation loss).

**T-VH2.**
`tests/e2e_reactive_distance_tests.rs`:

1. Create embedding stream table (e.g., fraud transactions).
2. Create reactive subscription with distance predicate:
   `embedding <=> known_fraud_vec < 0.05`.
3. Insert new transaction; verify NOTIFY fires.
4. Update the same transaction (move further away from fraud centroid);
   verify NOTIFY does not fire (no change in predicate truth).
5. Insert 1000 new transactions, half within ε; verify 500 NOTIFYs.

**T-VH3.**
`tests/e2e_hybrid_search_nexmark_tests.rs`:

Run Nexmark Stream using a hybrid-search corpus definition:

```sql
SELECT pgtrickle.create_stream_table(
  'nexmark_auction_corpus',
  $$
    SELECT a.id, a.title, a.description, a.embedding,
           array_agg(b.name) AS category_names,
           c.seller_name, c.seller_rating
    FROM nexmark_auctions a
    JOIN nexmark_categories b ON b.id = a.category
    JOIN nexmark_sellers c ON c.id = a.seller
    GROUP BY a.id, c.seller_name, c.seller_rating
  $$,
  refresh_mode => 'DIFFERENTIAL'
);
```

Monitor: refresh latency, corpus row count, drift %, HNSW index size.
Assert: corpus stays < 1s behind Nexmark event time; hybrid queries (BM25 +
vector) < 50 ms on 100k rows.

> **Latency target note:** The < 50 ms target must be validated against a
> baseline measurement on the CI hardware before the test is written.
> Establish this baseline in the first pass of T-VH3 and adjust the assertion
> if the CI hardware cannot sustain the target — do not hard-code an
> unmeasured number.

### Conflicts & Risks

- **VH-1** (sparse/halfvec aggregates) requires careful handling of dimension
  mismatches. `sparsevec_avg` may aggregate over vectors with different sparse
  dimension sets — define the result as the union. Test extensively over
  heterogeneous sparse patterns (all-zero sparse vectors, dimension-set
  cardinality mismatches between old and new rows, state serialization
  round-trips).
- **VH-1** (halfvec precision): the running state is upcast to `vector(d)` for
  numerical stability. Verify that the upcast does not introduce significant
  precision loss vs. full `vector_avg` on the same data. Add an explicit
  proptest that compares `halfvec_avg(cast(v as halfvec))` against
  `vector_avg(v)` and asserts relative error < 1e-3.
- **VH-2** (reactive distance) requires binding the query vector at
  subscription-creation time, not query-time. This is a trade-off: slightly
  less flexible than parameterised queries, but vastly more efficient for
  real-time alerts (no per-query distance recomputation).
- **VH-3** (hybrid-search patterns) may surface indexing challenges (GIN on
  array columns, HNSW on vector, B-tree on foreign keys). Document index
  creation order and maintenance.

### Exit Criteria

- [ ] VH-1: `sparsevec_avg`, `sparsevec_sum`, `halfvec_avg`, `halfvec_sum` aggregates implemented and tested
- [ ] VH-1: All four aggregates pass proptest equivalence check vs. non-incremental computation
- [ ] VH-1: `halfvec_avg` precision contract verified: relative error vs. `vector_avg` on same data < 1e-3
- [ ] VH-1: `sparsevec_avg` edge cases tested: all-zero sparse vectors, heterogeneous dimension sets, round-trip serialization
- [ ] VH-2: v0.35.0 reactive subscription correctness confirmed (no open issues) before VH-2 implementation begins
- [ ] VH-2: Reactive subscriptions accept distance predicates; firing logic verified over 10k inserts
- [ ] VH-2: No spurious re-fires on rows that do not change predicate truth
- [ ] VH-3: Hybrid-search cookbook published with 4+ worked examples
- [ ] VH-4: Benchmark suite runs in CI, results published to dashboard
- [ ] T-VH1: `tests/e2e_pgvector_tiered_tests.rs` passes (multi-layer tiered storage)
- [ ] T-VH2: `tests/e2e_reactive_distance_tests.rs` passes (reactive anomaly alerts)
- [ ] T-VH3: `tests/e2e_hybrid_search_nexmark_tests.rs` passes (hybrid-search corpus under write load)
- [ ] Extension upgrade path tested (`0.38.0 → 0.39.0`)
- [ ] `just check-version-sync` passes

---
