# v0.40.0 — Embedding API & Advanced RAG Patterns

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

**Status: Planned** | **Scope: Large**

> Ergonomic `embedding_stream_table()` API for one-statement RAG corpus setup,
> materialised k-NN graph research for fixed-pivot retrieval, per-tenant ANN
> indexing patterns, joint case studies with pgvector team, and strategic
> ecosystem positioning for the v1.0 release.

---

## What is this?

v0.37–v0.39 build comprehensive pgvector support in pg_trickle. v0.40.0
consolidates and elevates that work into strategic, long-term patterns:
an ergonomic API so RAG developers can set up denormalised, indexed embedding
corpora in one function call; research into materialised k-NN graphs for
efficient approximate retrieval; production patterns for multi-tenant RAG; and
co-marketing with the pgvector and pgai teams to position pg_trickle as the
default incremental-view solution for AI on PostgreSQL.

---

## `embedding_stream_table()` ergonomic API

Today: `create_stream_table('name', $$SELECT ...$$)` — users must write the
full denormalization query. For RAG, we can offer a higher-level primitive:

```sql
SELECT pgtrickle.embedding_stream_table(
  name              => 'docs_embedded',
  source_table      => 'documents',
  embedding_columns => ARRAY['embedding'],
  denormalize_from  => ARRAY[
    ('doc_tags', 'JOIN doc_tags ON doc_tags.doc_id = documents.id', 'tags'),
    ('doc_metadata', 'LEFT JOIN doc_metadata ...', 'metadata')
  ],
  schedule          => '10 seconds',
  index_type        => 'hnsw',
  post_refresh_action => 'reindex_if_drift'
);
```

The function auto-generates the denormalization query, creates the stream table,
creates the indexes, sets up vector monitoring, and returns a summary. For 80%
of users, this eliminates boilerplate.

Key design requirements: `dry_run => true` returns the generated SQL without
executing it (essential for expert users); index-type inference defaults to
HNSW for `vector`/`halfvec` columns with explicit documentation of the
heuristic.

---

## Materialised k-NN graph (parallel research spike)

> **Reclassified (assessment 2026-04-27):** This is now a parallel research
> spike — not a v0.40.0 release gate. A dedicated tracking issue drives the
> investigation; findings may graduate to v0.41.0 or later.

For fixed retrieval pivots (e.g., a set of 100 centroids), pre-computing the
k-NN graph can accelerate approximate retrieval. Research direction:

- Define a set of pivot vectors (e.g., centroids of embedding clusters).
- Maintain a stream table of `(pivot_id, neighbor_id, distance)` tuples for
  each embedding's k nearest pivots.
- Application queries: `SELECT * FROM pivot_neighbours WHERE pivot_id = p LIMIT k`.

This trades storage (O(k·N) rows) for query latency. Feasible but requires
careful handling of clustering lifecycle. Likely a v0.40–v0.41 research task,
not production-ready in v0.40.0.

---

## Per-tenant ANN indexing patterns

For multi-tenant RAG (e.g., SaaS with per-org embeddings), use row-level
security (v0.5) to create tenant-scoped stream tables:

```sql
SELECT pgtrickle.create_stream_table(
  'tenant_embeddings',
  $$
    SELECT e.id, e.embedding, e.tenant_id
    FROM embeddings e
    WHERE e.tenant_id = current_setting('app.current_tenant_id')::int
  $$
);
```

Pattern documentation + security audit checklist.

---

## Joint case studies with pgvector / pgai *(best-effort)*

> **Best-effort (assessment 2026-04-27):** Co-marketing depends on third-party
> availability and timing. It must not block the release. If outreach has not
> resulted in commitments by feature-freeze, ship with a pg_trickle-solo blog
> post and the public `pg-trickle-rag-starter` example repo.

- Co-authored blog post: "PostgreSQL as a Vector Database" with pgvector and
  pgai maintainers.
- Case study: real production RAG system (anonymized if needed) using pg_trickle
  + pgvector + pgai for embedding pipeline, corpus maintenance, and search.
- Shared benchmark: TPC-H with hybrid search (vector similarity + SQL filters)
  on pg_trickle stream tables.

Strategic impact: positions pg_trickle as the infrastructure layer for
incremental AI/RAG on PostgreSQL, raising the profile of the entire PostgreSQL
ecosystem in the AI niche. Begin pgvector/pgai outreach during v0.38 timeframe.

---

## Also in v0.40.0

- Citus multi-database vector aggregation patterns (if v0.34 Citus support available)
- Research: LLM-generated stream-table definitions (e.g., "create a ST for
  customer churn prediction from my schema")

---

## Scope

v0.40.0 is a large release. The `embedding_stream_table()` API is primarily
syntactic sugar over existing primitives, but the query-generation logic and
index inference require careful implementation. The k-NN graph work is a
parallel research spike and does not gate the release. The per-tenant
patterns are documentation + examples. Co-marketing is best-effort.

---

*Previous: [v0.39.0 — Hybrid Search & Sparse Vector Aggregates](v0.39.0.md)*
*Next: [v1.0.0 — Stable Release](v1.0.0.md-full.md)*