Almost every RAG tutorial I read starts the same way. Take your documents, split them into chunks, embed them, and load the vectors into a vector database. Then keep your original data where it was, and write something that keeps the two in sync.
I was an Oracle DBA for a long time before I moved into architecture work, and that second step always makes me a little uneasy. Not because vector databases are bad. Because "add another database" is never just one decision. It is a second copy of the data, a sync job that can fall behind, a second set of permissions, backups that are not consistent with each other, and one more thing to patch, monitor and pay for.
So I wanted to see how far I could get with just Postgres. Specifically Azure Database for PostgreSQL Flexible Server, which gives you pgvector, a DiskANN index through the pg_diskann extension, and the azure_ai extension for calling Azure OpenAI from SQL. I wrote a small tool, pgrag, to try it properly, with tests and real measurements. This post is what I learned.
One table for text, vectors and keywords
The core of it is boring on purpose. Here is the chunks table, trimmed a little:
CREATE TABLE chunks (
id bigserial PRIMARY KEY,
document_id bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
content text NOT NULL,
metadata jsonb NOT NULL DEFAULT '{}',
embedding vector(1536),
content_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED
);
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON chunks USING gin (content_tsv);
CREATE INDEX ON chunks USING gin (metadata jsonb_path_ops);
The text, its embedding, a full-text search column and the metadata all sit in one row. The tsvector is a generated column, so Postgres keeps it current on every insert and update. There is no separate indexing pipeline.
As a DBA, this is the part I like most. Re-ingesting a document is a DELETE and an INSERT in one transaction, so nobody ever searches half an update. Point-in-time restore brings back the vectors with the rows they belong to. Row-level security and normal joins work on search results. When someone asks "where does our RAG data live?", there is one answer.
Hybrid search is one SQL function
Pure vector search is good at meaning. Ask "how do I pause the database to save money" and it can find a passage about stopping the server. It is weaker on exact tokens: parameter names like maintenance_work_mem, SKU names like Standard_B1ms, error codes. Full-text search is the other way round. You want both.
The usual way to combine them is Reciprocal Rank Fusion. Each result gets 1 / (60 + rank) from every list it appears in, and you add those up. A chunk that is 1st in the vector list and 3rd in the keyword list scores 1/61 + 1/63, about 0.0323. A chunk only one list found, at rank 1, scores about 0.0164. RRF only looks at ranks, so you never have to compare a cosine similarity with a ts_rank score.
In Postgres this is a single function: one CTE for the nearest vectors (ORDER BY embedding <=> $1 LIMIT 50, written so the planner uses the index), one CTE for the keyword matches, and a FULL OUTER JOIN that adds up the scores. The metadata filter is applied to both sides. I also wrote the same maths in plain Python and an integration test checks that the SQL returns exactly the same ids, ranks and scores, because I don't trust fusion code I can't check.
Two things surprised me while building it.
The keyword side was silently empty. I started with websearch_to_tsquery, which needs every word of the query to be in the chunk. That works for keyword searches like maintenance_work_mem. But people ask questions like "how do I stop paying for the server when I'm not using it?", and no chunk contains all of those words. The keyword list came back empty and hybrid search quietly became plain vector search. The fix was to turn the question into an OR of its words and let ts_rank_cd push chunks that match more of them to the top. With that change, the same question gets the costs page as 1st in both lists.
Filters can return nothing at all. An HNSW scan hands back its best ef_search candidates, and the WHERE clause filters afterwards. If your filter is selective, that can leave you with nothing. I wrote a test for it: 3,030 chunks, only 30 tagged rare, ask for the 10 nearest rare chunks with the HNSW index forced. Without iterative scans I got 0 rows back. With pgvector 0.8's hnsw.iterative_scan = relaxed_order I got all 10. pgrag now turns that on for filtered searches when pgvector is 0.8 or newer. If you are on an older pgvector, this is worth knowing about before your users find it.
Some numbers from a small box
I don't want to hand-wave about performance, so pgrag has a bench command. It generates clustered synthetic vectors, computes the exact top 10 for each query in NumPy, loads the vectors with binary COPY, and then measures recall@10 and latency for an exact scan and for HNSW at different ef_search values.
To be clear about where these came from: a sandbox Linux container with 2 vCPUs and 7.8 GiB of RAM, PostgreSQL 16.15 and pgvector 0.8.0, client on the same machine. Not Azure, and no DiskANN yet. That comes with the Azure run.
| data | index | setting | recall@10 | p50 ms |
|---|---|---|---|---|
| 20k x 384 | exact scan | - | 1.000 | 11.61 |
| 20k x 384 | HNSW | ef_search=40 | 0.990 | 0.67 |
| 100k x 1536 | exact scan | - | 1.000 | 628.53 |
| 100k x 1536 | HNSW | ef_search=40 | 0.823 | 3.23 |
| 100k x 1536 | HNSW | ef_search=160 | 0.964 | 5.94 |
The headline is the gap between the exact scan and the index: about 630 ms down to 3 to 6 ms at 100k vectors of 1536 dimensions. The second thing is that ef_search really is the recall dial. The default of 40 was plenty for the small set and not enough for the big synthetic one. My synthetic 1536-dim data is harder than real embeddings, so please don't quote 0.82 as "pgvector recall". Quote it as "measure with your own queries".
The index sizes were the most useful result for planning. The 100k x 1536 HNSW index was 781.3 MiB, which is exactly 8 KiB per row. pgvector keeps each element on a page without splitting it, and a 1536-dim element plus its neighbours is a bit over 6 KB, so it gets a page to itself. The 384-dim index came out at about 2 KiB per row, four to a page. That made it easy to put a page-aware size estimate into pgrag's cost command and pin it to these measurements with a unit test.
What it costs
The setup I use for this series is deliberately small: a Burstable B1ms Flexible Server (1 vCore, 2 GiB RAM), 32 GiB of storage, no high availability, pay-per-token Azure OpenAI with text-embedding-3-small and gpt-4.1-mini, and a budget on the resource group that e-mails me at 50, 80 and 100 % of 10 USD. It is all in one Bicep file with up.sh and down.sh scripts.
pgrag cost puts rough numbers on it. The prices in it are constants I typed in, so check the Azure pricing calculator before you trust them. Still, the shape of the answer is clear:
- Embedding is the cheap part. 10,000 documents of about 1,500 tokens is roughly 17.5 million tokens, about 35 cents with
text-embedding-3-smallat $0.02 per million. - Compute hours are the real cost. A stopped Flexible Server only bills storage, so
az postgres flexible-server stopis the habit to build. (Azure starts it again by itself after seven days.) - Memory is the real limit. B1ms is fine for a few thousand chunks. For 100k documents the estimated HNSW index is over 5 GiB, which will not stay in memory on a 2 GiB server. For my 100k benchmark run on Azure I'll rent a General Purpose server for a couple of hours and delete it right after.
My budget for all of this is under 100 USD of Azure spend. The Azure free account helps a lot: 750 hours a month of B1ms and 32 GiB of storage for the first year, which covers one small server running all month.
The Azure-only extras
Two things you only get on Azure are worth calling out, with the honest note that I have written and tested the code paths locally but not yet measured them on Azure.
DiskANN. pg_diskann adds a diskann index type on top of pgvector's vector column. It is the same CREATE INDEX ... USING diskann (embedding vector_cosine_ops) you would expect, with diskann.l_value_search as the query-time knob. pgrag's migration checks whether the extension exists and skips with a notice if it doesn't, and the benchmark runs it automatically when it is there.
In-database embeddings. With the azure_ai extension, azure_openai.create_embeddings('text-embedding-3-small', content) returns an array you can cast to vector. That turns a backfill into a plain UPDATE, and it lets a search function embed the question itself, so the app sends text and gets ranked chunks back in one round trip. Each call is a billable API request, so batch it.
When you do want a second database
I'm not arguing that nobody needs a vector database. If you have hundreds of millions of vectors, or a very high vector-only query rate that you want to scale separately from your transactional database, or your team simply doesn't run Postgres, a dedicated system can make sense. But I'd want to see the measurement that says so, the same way I'd want to see it before adding any new database to a stack.
For a team that already runs Postgres and has up to a few million chunks, my suggestion is to start with the database you already back up, secure and know how to operate.
Try it
The code is at github.com/sharath568/azure-data-pg-rag. The local quickstart needs Docker and Python and costs nothing: docker compose up -d, pgrag init, pgrag ingest examples/docs, pgrag search "...". The Azure quickstart is one script and one budget e-mail address.
If you run the benchmark on Azure, especially with DiskANN, I would really like to see your numbers. Open an issue or a PR with the SKU and region, and I'll add them to the repo.
Top comments (0)