# RAG and Semantic Search on Postgres | Volcano

URL: https://volcano.dev/solutions/rag-search

> Build retrieval on Volcano with pgvector: embeddings beside the rows they describe, cosine and L2 distance, IVFFlat indexes, and row-level security filtering similarity results per user.

## Retrieval that respects permissions

Embeddings live in Postgres next to your data, so a similarity search filters by who is asking in the same query that ranks by distance.

```console
$ volcano cloud databases migration up --all -d app
001_documents.sql   create extension vector
002_embeddings.sql  chunks.embedding vector(1536)
003_index.sql       ivfflat (embedding vector_cosine_ops)
✓ retrieval ready · policies apply to similarity queries too
# a search returns only chunks the caller may already read
```

## Two datastores, and nothing keeping them in step

The standard retrieval stack keeps documents in a database and vectors in a separate service. Now every write has to reach both, deletes have to be mirrored, and permissions have to be re-implemented in a system that only understands vectors and metadata blobs.

Keep the embeddings in the same Postgres table as the text. A search is a SQL query, so it joins, filters by tenant and obeys the row-level policies you already wrote. Deleting a document deletes its vectors, because they were columns on the row.

## The vector is a column

*Colocation*

Run CREATE EXTENSION vector and embeddings become part of the row they describe. There is nothing to synchronise, and a deleted document cannot leave orphaned vectors behind.

- vector(1536) or whatever your model emits
- Cascade deletes remove embeddings with the row
- One transaction covers text and vector

```console
$ \d chunks
 id         | uuid
 doc_id     | uuid
 tenant_id  | uuid
 content    | text
 embedding  | vector(1536)
one table · no sync job
```

## Similarity search obeys your policies

*Authorization*

Row-level security applies to a vector query the same way it applies to a SELECT. A user retrieves only the chunks they were already allowed to read, so retrieval cannot become a data leak.

- Filtered per caller through auth.uid()
- Tenant predicate and ranking in one query
- No permission model to duplicate

Image: Query statistics with a similarity search alongside ordinary reads.

## Three distance operators and a real index

*Ranking*

Cosine, L2 and inner product are all available, and an IVFFlat index keeps the search fast as the table grows. Because it is SQL, you can combine distance with recency or a text filter.

- \<=\> cosine, \<-\> L2, \<#\> inner product
- IVFFlat with vector\_cosine\_ops or vector\_l2\_ops
- Blend distance with ordinary WHERE clauses

```console
$ select content, embedding <=> $1 as distance
$   from chunks where tenant_id = $2
$   order by distance limit 5;
Refund policy for annual plans   0.118
Cancelling mid-term              0.204
5 rows · the policy already removed the rest
```

## How it works

1. **Turn on the extension**: CREATE EXTENSION vector in a migration, then add an embedding column to your chunk table.
2. **Embed on write**: A function calls your embedding provider and stores the vector with the text. A whole corpus runs as a durable function, one batch per step.
3. **Index once it matters**: Add an IVFFlat index on the distance operator you actually query with, after you have rows.
4. **Query with the caller**: Connect as the user so the policy decides which chunks can come back at all.

## Schema, ingest, retrieve, index

### Schema

```sql
create extension if not exists vector;

create table if not exists documents (
  id         uuid primary key default gen_random_uuid(),
  tenant_id  uuid not null,
  title      text not null,
  created_at timestamptz not null default now()
);

create table if not exists chunks (
  id         uuid primary key default gen_random_uuid(),
  doc_id     uuid not null references documents(id) on delete cascade,
  tenant_id  uuid not null,
  content    text not null,
  embedding  vector(1536)
);

alter table documents enable row level security;
alter table chunks    enable row level security;

-- Retrieval inherits this. A similarity search cannot return a chunk the
-- caller could not have selected directly.
drop policy if exists tenant_documents on documents;
create policy tenant_documents on documents
  for select to authenticated
  using (exists (
    select 1 from memberships m
    where m.tenant_id = documents.tenant_id and m.user_id = auth.uid()
  ));

drop policy if exists tenant_chunks on chunks;
create policy tenant_chunks on chunks
  for select to authenticated
  using (exists (
    select 1 from memberships m
    where m.tenant_id = chunks.tenant_id and m.user_id = auth.uid()
  ));

-- Members can add documents to their own tenant, and nowhere else.
drop policy if exists tenant_documents_insert on documents;
create policy tenant_documents_insert on documents
  for insert to authenticated
  with check (exists (
    select 1 from memberships m
    where m.tenant_id = documents.tenant_id and m.user_id = auth.uid()
  ));

drop policy if exists tenant_chunks_insert on chunks;
create policy tenant_chunks_insert on chunks
  for insert to authenticated
  with check (exists (
    select 1 from memberships m
    where m.tenant_id = chunks.tenant_id and m.user_id = auth.uid()
  ));
```

### Ingest

```python
import json
import os
from urllib.parse import parse_qsl, urlencode, urlparse, urlunparse

import psycopg


def embed(texts):
    """Call whichever embedding provider you use. Volcano does not generate
    embeddings; the key is a project variable."""
    ...


def chunk(text, size=1200, overlap=150):
    step = size - overlap
    return [text[i:i + size] for i in range(0, len(text), step)]


def handler(event, context):
    auth = event.get("__volcano_auth")
    if not auth:
        return {"statusCode": 401, "body": json.dumps({"error": "sign in required"})}

    pieces = chunk(event["text"])
    vectors = embed(pieces)

    # Connect as the caller, so the insert policy refuses a tenant they do not
    # belong to. Text and vectors are written in one transaction, so there is
    # no window where a document exists without its embeddings.
    parts = urlparse(os.environ["DATABASE_URL"])
    params = dict(parse_qsl(parts.query))
    params["application_name"] = f"volcano_user_access:{auth['user_id']}"
    dsn = urlunparse(parts._replace(query=urlencode(params)))

    with psycopg.connect(dsn) as db, db.cursor() as cur:
        cur.execute(
            "insert into documents (tenant_id, title) values (%s, %s) returning id",
            (event["tenant_id"], event["title"]),
        )
        doc_id = cur.fetchone()[0]

        cur.executemany(
            "insert into chunks (doc_id, tenant_id, content, embedding) values (%s, %s, %s, %s)",
            [(doc_id, event["tenant_id"], piece, vec) for piece, vec in zip(pieces, vectors)],
        )

    return {"statusCode": 201, "body": json.dumps({"doc_id": str(doc_id), "chunks": len(pieces)})}
```

### Retrieve

```python
import json
import os
from urllib.parse import parse_qsl, urlencode, urlparse, urlunparse

import psycopg

# The same embedding helper the ingest function uses, shared from
# volcano/functions/_shared.
from _shared.embeddings import embed


def handler(event, context):
    auth = event.get("__volcano_auth")
    if not auth:
        return {"statusCode": 401, "body": json.dumps({"error": "sign in required"})}

    question = event["question"]
    [vector] = embed([question])

    # Connecting as the caller is what makes retrieval safe: the policy filters
    # out every chunk they cannot read, so only their rows can come back.
    parts = urlparse(os.environ["DATABASE_URL"])
    params = dict(parse_qsl(parts.query))
    params["application_name"] = f"volcano_user_access:{auth['user_id']}"
    dsn = urlunparse(parts._replace(query=urlencode(params)))

    with psycopg.connect(dsn) as db, db.cursor() as cur:
        cur.execute(
            """
            select c.content, d.title, c.embedding <=> %s::vector as distance
            from chunks c
            join documents d on d.id = c.doc_id
            order by distance
            limit 8
            """,
            (vector,),
        )
        rows = cur.fetchall()

    return {
        "statusCode": 200,
        "body": json.dumps([
            {"content": r[0], "title": r[1], "distance": float(r[2])} for r in rows
        ]),
    }
```

### Index

```sql
-- Match the operator class to the operator you query with. Cosine distance
-- (<=>) needs vector_cosine_ops; L2 (<->) needs vector_l2_ops.
create index if not exists chunks_embedding_cosine_idx
  on chunks using ivfflat (embedding vector_cosine_ops)
  with (lists = 100);

-- IVFFlat clusters the rows it can see at build time, so create it after an
-- initial load rather than on an empty table.
analyze chunks;

-- Tenant lookups stay a normal btree index.
create index if not exists chunks_tenant_idx on chunks (tenant_id);

-- Probing more lists trades latency for recall. Set it per session. Policies
-- filter rows after the index scan, so raise it if a tenant's results come
-- back short of the limit.
--   set ivfflat.probes = 10;
```

## What one datastore saves

- **One write instead of two**: Text and embedding land in the same transaction, so they cannot drift apart.
- **0 permission models to mirror**: The policy guarding your rows already guards what retrieval can return.
- **1 query to filter and rank**: Tenant predicate, recency and similarity resolve in a single statement.

## Outcomes

- **No sync job**: Deleting a document removes its vectors with it.
- **Retrieval that cannot leak**: Results are bounded by the reader's existing permissions.
- **Ordinary SQL**: Filter, join and rank with the tools you already know.

## Built on Volcano

- [pgvector in your database](https://volcano.dev/features/postgres): Vector columns, cosine, L2 and inner-product operators, and IVFFlat indexes inside PostgreSQL 16 through 18.
- [Embedding and retrieval](https://volcano.dev/features/edge-functions): Functions in Python, Node or Ruby that call your embedding provider and run the search.
- [Who may retrieve what](https://volcano.dev/features/authentication): Sessions that reach the database as auth.uid(), so policies filter results per reader.
- [Ingestion that finishes](https://volcano.dev/features/durable-functions): A durable function embeds a corpus batch by batch, so a provider rate limit retries one batch, not the whole load.

## Frequently asked questions

### Does Volcano create the embeddings?

No. You call your own embedding provider from a function and store what it returns. Volcano provides the vector column, the distance operators and the index.

### Which distance should I use?

Cosine (\<=\>) suits most text embedding models. L2 (\<-\>) and inner product (\<#\>) are also available, and your index operator class has to match the operator you query with.

### Can a search return documents the user cannot read?

No. Row-level security applies to vector queries, so the policy keeps those rows out of the results. Connect as the caller rather than with the service key for this to hold.

### When should I add an index?

Once you have a representative amount of data. IVFFlat clusters the rows present at build time, so an index created on an empty table will not help you.

### How do I embed a large corpus?

Run the ingestion as a durable function and make each batch of documents a step. A rate limit or a timeout from the embedding provider retries that batch with backoff, a crash resumes at the next batch, and the batches already stored are never embedded again. Declare the vector dimension on the column, for example vector(1536), to match your model.

### Do I need a separate vector database?

Not for retrieval that has to respect permissions and stay consistent with your rows. Keeping vectors in Postgres removes the sync path and the second authorization model entirely.

## Get started

Ready to build retrieval? Create a free account at https://volcano.dev/signup, or have your coding agent follow the setup playbook at https://volcano.dev/setup.md.