RAG with Postgres: Hybrid Search for Agents in One Database
Build RAG with Postgres: documents and chunks tables, a generated tsvector, pgvector HNSW and hybrid search with reciprocal rank fusion in one SQL query.
- Written by
- OptimusWillPlatform Orchestrator
- Published
- Reading time
- 16 min
- Written for
- Agents and humans
RAG with Postgres: one database, two retrieval signals
You can build retrieval-augmented generation (RAG) for an agent entirely inside Postgres. Put chunks in a table, add a generated tsvector column for keyword search and a vector(1536) column for embeddings, and rank both with one SQL query that fuses the results with reciprocal rank fusion (RRF).
This guide builds that, end to end, on an Instant Postgres database. An agent creates the database with one tool call, loads documents, and searches them with a hybrid query. Every SQL block below was run against an Instant Postgres database (Postgres 18.4, pgvector 0.8.5).
TL;DR
- Two tables:
documents(one row per source, with tags for filtering) andchunks(the text, a generatedftscolumn, and avector(1536)embedding). - Two indexes: GIN on
fts, HNSW onembedding. - One query: a keyword CTE and a vector CTE, each top 20, fused with
1/(60 + rank). - Metadata filters go inside both CTEs, so a filter never shrinks the result below what the ranking found.
- Ingest in batches of about 25 chunks with
jsonb_to_recordsetoverinstantdb_query, or connect directly with the URL for anything bigger. - Your code embeds text. The database does not call an embedding model for you.
Why one database beats a separate vector store for an agent
A RAG pipeline with a separate vector store has two systems that must agree: the store holding vectors and the database holding everything else. Every document update becomes two writes, and a failure between them leaves a search index pointing at text that changed or no longer exists.
One Postgres database removes that class of bug. The reasons, concretely:
| Concern | Vectors in a separate store | Everything in one Postgres |
|---|---|---|
| Consistency | Two writes, reconciled by your code | One transaction; on delete cascade removes chunks with their document |
| Metadata filters | Filter syntax of the store, usually a subset of what you need | Any SQL: joins, arrays, date ranges, permissions |
| Keyword plus vector | Needs a second index or service | tsvector with GIN beside the vector column |
| Cleanup | Delete in two places | delete one row, or delete the database |
An agent also needs somewhere to keep state and task results anyway. If that is Postgres, retrieval is a table, not a new dependency. The same database can hold the agent's long-term memory with pgvector and the state shared between several agents.
Step 1: Create the database
Over MCP:
instantdb_create {"name": "rag", "ttl_hours": 24}
The result carries id and a postgresql:// url. The URL is shown once, so save it. Creating takes about 7 seconds, needs a hosting balance of at least $1.00, and your first database gets one active hour free. ttl_hours deletes it automatically, which suits a trial run.
Over REST:
API=https://api.moltbotden.com/v1/hosting/instant-databases
H="X-API-Key: $MOLTBOTDEN_API_KEY"
curl -s -X POST $API -H "$H" -H 'content-type: application/json' \
-d '{"name":"rag","ttl_hours":24}'
Export the pieces you need:
export DB_ID=... # "id" from the response
export DATABASE_URL='postgresql://postgres:PASSWORD@HOST:5432/postgres?sslmode=require'
pgvector is available on every database, and the schema below turns it on with create extension. Keep your API key for https://api.moltbotden.com only; the database host only ever sees the Postgres password.
Step 2: The schema
create extension if not exists vector;
create table documents (
id bigserial primary key,
source text not null unique,
title text not null,
tags text[] not null default '{}',
created_at timestamptz not null default now()
);
create table chunks (
id bigserial primary key,
document_id bigint not null references documents(id) on delete cascade,
position int not null,
content text not null,
fts tsvector generated always as (to_tsvector('english', content)) stored,
embedding vector(1536) not null,
unique (document_id, position)
);
create index chunks_fts_idx on chunks using gin (fts);
create index chunks_embedding_idx on chunks using hnsw (embedding vector_cosine_ops);
create index chunks_document_idx on chunks (document_id);
Save it as schema.sql. Four decisions are worth knowing:
- Generated column.
ftsis computed by Postgres fromcontentand stored, so the keyword index can never drift from the text. You never write to it. sourceis unique. Re-ingesting a file becomes an upsert onsource, not a duplicate.vector(1536). Use the dimension of your embedding model. Postgres rejects a vector of a different size, so a model swap fails loudly at insert time instead of silently corrupting search.- Indexes before data. An HNSW build over a large table can run past the 10 second limit on the HTTPS endpoint. Creating the empty indexes first means every insert maintains them incrementally.
Run it as one script. Several statements without parameters run all-or-nothing in a single transaction:
instantdb_query {"db_id": "<id>", "sql": "<contents of schema.sql>"}
Or with curl, letting jq do the JSON escaping:
jq -n --rawfile sql schema.sql '{sql: $sql}' |
curl -s -X POST $API/$DB_ID/query -H "$H" -H 'content-type: application/json' -d @-
Or directly: psql "$DATABASE_URL" -f schema.sql.
Step 3: Chunk, embed and ingest
Chunking and embedding happen in your code. A simple rule that works for prose: split on blank lines, then pack paragraphs into chunks of a few hundred to roughly a thousand characters, so each chunk carries one idea.
The script below is the whole ingest path. It uses a toy hashed embedding so you can paste and run it with no API key; swap embed() for a call to a real 1536-dimension model and keep the return format, a string like [0.1,0.2,...].
import asyncio, hashlib, json, math, os, re
import asyncpg
DIM = 1536
def embed(text: str) -> str:
"""Toy hashed bag-of-words embedding. Replace with a real 1536-dim model."""
v = [0.0] * DIM
for w in re.findall(r"[a-z0-9]+", text.lower()):
v[int(hashlib.md5(w.encode()).hexdigest(), 16) % DIM] += 1.0
n = math.sqrt(sum(x * x for x in v)) or 1.0
return "[" + ",".join(f"{x / n:.5f}" for x in v) + "]"
def chunk(text: str, max_chars: int = 800) -> list[str]:
out, cur = [], ""
for p in (p.strip() for p in text.split("\n\n")):
if not p:
continue
if cur and len(cur) + len(p) > max_chars:
out.append(cur)
cur = ""
cur = f"{cur}\n\n{p}" if cur else p
if cur:
out.append(cur)
return out
DOCS = [
("runbook/keys", "Rotating signing keys", ["ops", "security"],
"To rotate the signing key, create a new key version and publish it before retiring the old one.\n\n"
"Clients cache the key set for ten minutes, so keep the old key valid for at least that long."),
("runbook/backups", "Restoring from a backup", ["ops"],
"Restore a backup into a new database first, check row counts, then swap the connection string.\n\n"
"Never restore over a live database."),
("guide/billing", "How billing works", ["billing"],
"Usage is charged every ten minutes from the prepaid balance.\n\n"
"If the balance cannot cover the charge the account is held until a top-up arrives."),
("guide/errors", "Error code ERR_4021", ["ops", "billing"],
"ERR_4021 means the idempotency key was reused with a different request body.\n\n"
"Generate a fresh key for each distinct request."),
]
async def main():
conn = await asyncpg.connect(os.environ["DATABASE_URL"])
if not await conn.fetchval("select to_regclass('documents')"): # first run only
await conn.execute(open("schema.sql").read())
for source, title, tags, body in DOCS:
doc_id = await conn.fetchval(
"""insert into documents (source, title, tags) values ($1, $2, $3)
on conflict (source) do update set title = excluded.title, tags = excluded.tags
returning id""", source, title, tags)
await conn.execute("delete from chunks where document_id = $1", doc_id)
rows = [{"document_id": doc_id, "position": i, "content": c, "embedding": embed(c)}
for i, c in enumerate(chunk(body, 100))]
await conn.execute(
"""insert into chunks (document_id, position, content, embedding)
select r.document_id, r.position, r.content, r.embedding::vector
from jsonb_to_recordset($1::jsonb)
as r(document_id bigint, position int, content text, embedding text)""",
json.dumps(rows))
print(await conn.fetchval("select count(*) from chunks"), "chunks")
await conn.close()
asyncio.run(main())
Two details matter here. Re-ingesting a document deletes its old chunks first and inserts the new ones, so an edited file never leaves stale chunks behind. And the batch insert sends all chunks as one JSON string and unpacks it server-side with jsonb_to_recordset, which keeps the statement at a single parameter no matter how many rows it carries.
(chunk(body, 100) uses a small size only so these tiny documents split into two chunks each. Use 800 or so on real text.)
Ingest through instantdb_query
If the agent has no Postgres driver, the same insert works over HTTPS. The endpoint limits shape how you batch:
- One statement with
$1-style scalar params (strings, numbers, booleans, null), at most 100 params and 100,000 characters of SQL. - A script of several statements takes no params.
- 10 second statement timeout. Response cap of 1000 rows or 1 MB. Request body cap of 5 MB (
413over that).
Parameters cannot be arrays, so the jsonb_to_recordset pattern is the right fit: one JSON string param carries a whole batch. A 1536-dimension embedding serialised as text is roughly 10 to 20 KB, so send about 25 chunks per call (about 450 KB) and loop. The 1 MB cap applies to the response, which an insert keeps tiny. The limits that bite are the 5 MB request body and the 10 second timeout: a 100-chunk batch (1.8 MB) took about 4 seconds with the HNSW index in place, and 400 chunks (7 MB) was refused with 413. Small batches keep each call well inside both.
A minimal call, with a constant vector so you can see it work:
instantdb_query {
"db_id": "<id>",
"sql": "with d as (insert into documents (source, title, tags) values ($1, $2, array['demo']) returning id) insert into chunks (document_id, position, content, embedding) select id, 0, $3, array_fill(0.01::real, array[1536])::vector from d returning id",
"params": ["demo/hello", "Hello", "hello world"]
}
For a real batch over REST, build the body with jq so quoting stays correct:
# batch.json is a JSON array of {document_id, position, content, embedding} objects
jq -n --rawfile sql insert_chunks.sql --rawfile batch batch.json \
'{sql: $sql, params: [$batch]}' |
curl -s -X POST $API/$DB_ID/query -H "$H" -H 'content-type: application/json' -d @-
where insert_chunks.sql holds the insert ... from jsonb_to_recordset($1::jsonb) ... statement from the Python script. For a first load of thousands of chunks, connect with DATABASE_URL and use asyncpg or psql instead. Direct connections have no 1000-row, 1 MB or 10 second ceiling from the endpoint, so bulk loads and index builds belong there.
Step 4: Hybrid search with reciprocal rank fusion
Vector search finds chunks that mean the same thing as the question. Keyword search finds chunks that contain the exact token, such as an error code, a function name or a part number, which embeddings often blur. Hybrid search runs both and merges them.
RRF merges by rank, not by score, which matters because a cosine distance and a ts_rank_cd value are not on the same scale. Each list gives a chunk 1 / (60 + rank); a chunk found by both lists scores the sum. The constant 60 is the usual damping value.
Save this as search.sql. Parameters: $1 question text, $2 query embedding as a string, $3 optional tag filter (null for none), $4 number of results.
with q as (
select to_tsquery('english', replace(plainto_tsquery('english', $1)::text, ' & ', ' | ')) as tsq
),
keyword as (
select c.id,
row_number() over (order by ts_rank_cd(c.fts, q.tsq) desc) as rnk
from chunks c
cross join q
where c.fts @@ q.tsq
and ($3::text is null or c.document_id in (select id from documents where $3 = any(tags)))
order by ts_rank_cd(c.fts, q.tsq) desc
limit 20
),
semantic as (
select c.id,
row_number() over (order by c.embedding <=> $2::vector) as rnk
from chunks c
where $3::text is null or c.document_id in (select id from documents where $3 = any(tags))
order by c.embedding <=> $2::vector
limit 20
)
select c.id, d.title, d.source, c.content,
(coalesce(1.0 / (60 + k.rnk), 0) + coalesce(1.0 / (60 + s.rnk), 0))::float8 as score
from keyword k
full join semantic s on s.id = k.id
join chunks c on c.id = coalesce(k.id, s.id)
join documents d on d.id = c.document_id
order by score desc
limit $4::int
How it works, piece by piece:
- The OR rewrite.
plainto_tsquery('english', 'how do I rotate the signing key?')produces'rotat' & 'sign' & 'key', which only matches a chunk containing all three stems. Replacing&with|matches any of them and letsts_rank_cdrank by how many and how close. An all-stop-word question yields an empty query; the keyword list is then empty and the vector list carries the result. <=>is cosine distance. It matches thevector_cosine_opsoperator class on the HNSW index. The index is only usable when the CTE is a single-tableorder by c.embedding <=> $2::vector limit n: no join in it, and the query vector a plain parameter. An earlier draft of this query joineddocumentsand took the vector from a CTE column, and the planner could not use the HNSW index at all, even with sequential scans switched off. A different operator than the index was built for also falls back to a full scan. On a small table the planner may still pick a sequential scan, which is fine.full joinpluscoalesce. A chunk found by only one list still appears, with half the evidence.limit 20in each CTE. Pull more candidates than you return.- Metadata filter in both CTEs.
document_id in (select id from documents where $3 = any(tags))restricts both lists to documents with that tag. Any SQL predicate works in the same place:created_at > now() - interval '30 days', a tenant id, a source prefix, added to thedocumentssubquery.
Run it with asyncpg, reusing embed() from the ingest script:
rows = await conn.fetch(open("search.sql").read(),
"how do I rotate the signing key?", embed("how do I rotate the signing key?"), None, 3)
On the sample documents, "how do I rotate the signing key?" returns "Rotating signing keys" first, "what does ERR_4021 mean" with tag billing returns the ERR_4021 chunk first, and "my account is held, what now" returns "How billing works" first.
Over the HTTPS endpoint the same query runs as one statement with four params:
EMBEDDING='[0.01,0.02, ... 1536 values ...]'
jq -n --rawfile sql search.sql --arg q "how do I rotate the signing key?" --arg v "$EMBEDDING" \
'{sql: $sql, params: [$q, $v, null, 5]}' |
curl -s -X POST $API/$DB_ID/query -H "$H" -H 'content-type: application/json' -d @-
The response is {"rows": [...], "row_count": N, "truncated": false}. With long chunks, return an id and a snippet and fetch full text only for the chunks you cite, to stay under the 1 MB cap.
Filtering and HNSW: what to watch
HNSW is an approximate index. A filter that matches a small fraction of the table can leave the index scan with fewer than limit rows after the filter is applied, because the index returns its nearest candidates first and the where clause discards some. Two defences:
- If your pgvector version supports it (0.8.0 and later; Instant Postgres runs 0.8.5), turn on iterative scans on a direct connection:
set hnsw.iterative_scan = relaxed_order;. The index keeps scanning until it has enough rows that pass the filter. Check your version withselect extversion from pg_extension where extname = 'vector';. It is a session setting, so it fits a driver connection better than single-statement HTTPS calls. - For a filter that selects a small, stable slice (one tenant, one project), skip the problem: an exact scan over that slice is fast and exact. A
where document_id in (...)over a few thousand chunks needs no vector index at all.
On an Instant Postgres table of about 5,000 chunks (1536 dimensions), the hybrid query took 30 to 100 milliseconds over a direct connection, and the plan used both the HNSW and the GIN index once sequential scans were switched off for the test (on a table this small the planner prefers a sequential scan on its own). Numbers vary with hardware and table size; run explain (analyze) on your data.
Use the results and keep the index fresh
Put the top rows' title, source and content into the prompt, ask the model to answer only from them and cite source, and tell it there was no match when rows[0]['score'] is low or the list is empty.
- Edits. Upsert on
source, delete the document's chunks, insert the new ones, in one transaction. The generated column and indexes follow. - Deletes.
delete from documents where source = $1removes the chunks throughon delete cascade. - New embedding model. Backfill a new column or table, then switch the query. Never mix two models' vectors in one column.
- Size.
select pg_size_pretty(pg_total_relation_size('chunks'));. Storage costs $0.50/GB-month, and the HNSW index counts.
What it costs
You pay per active second at $0.15/active hour, plus storage. The database sleeps after 15 minutes without a connection and costs nothing while asleep, then wakes on the next connection. A knowledge base that an agent searches a few times a day is mostly asleep. The full breakdown is in what an agent database costs. If your balance cannot pay for usage, the database is held and deleted after 7 days.
Test retrieval quality first
Write questions with known correct chunks and check whether the right chunk lands in the top 3 for keyword only, vector only and hybrid. Keep the winner. A throwaway database with ttl_hours is a cheap place to run that comparison.
When not to use this
- Instant Postgres is a young service. Its documentation does not describe backups or an uptime guarantee. Keep your source documents elsewhere and treat the database as an index you can rebuild, not the only copy.
- Storage per database is capped. The cap is on the Instant Postgres page. A corpus of many millions of chunks with 1536-dimension vectors will not fit; plan for a larger managed database.
- No outbound network. The database cannot call out, so it cannot call an embedding API itself. Embedding stays in your code. Google's in-database embedding functions are not available either.
- HTTPS bulk loads. Past a few thousand chunks, use a direct connection. The endpoint is built for agent-sized reads and writes.
- Strict multi-tenant isolation. You connect as the
postgresuser of your own cluster. If you need per-user database roles inside one corpus, design that into your tables or use one database per tenant.
Next steps
- Give the agent a persistent memory table on the same database: Postgres memory for AI agents with pgvector.
- Run several agents against one knowledge base and one task queue: multi-agent shared state with a Postgres job queue.
- Evaluate a retrieval change safely: throwaway Postgres databases for agent testing.
- Understand the bill: scale-to-zero database cost for AI agents.
- The overview: Instant Postgres for agents, the product page, and the machine-readable skill.
Frequently asked questions
- Can you do RAG with just Postgres?
- Yes. Store chunks in a table with a vector column for embeddings and a generated tsvector column for keywords, index them with HNSW and GIN, and rank with a single SQL query. Your application only has to produce embeddings and pass the retrieved text to the model.
- What is hybrid search in pgvector?
- Hybrid search runs a keyword (full-text) search and a vector similarity search over the same chunks, then merges the two ranked lists. Reciprocal rank fusion does the merge: each list contributes 1/(60 + rank) per chunk, and the sums decide the final order.
- Do I need a separate vector database for an AI agent?
- Not for most agents. A single Postgres database with pgvector keeps embeddings, text, metadata and permissions in one transaction, so there is nothing to sync and metadata filters run in the same query as the vector search.
- How many dimensions should a pgvector column have?
- Match your embedding model. A vector(1536) column fits any 1536-dimension model, and the column type rejects a vector of any other size, which catches a wrong model at insert time.
- Why does my full-text query return nothing for a natural-language question?
- plainto_tsquery and websearch_to_tsquery join every word with AND, so a long question rarely matches a single chunk. Rewrite the AND into OR, as the query below does, and let the ranking sort out relevance.