Build1 publisher2 min readPublished
Co-locating embeddings with permissions collapses the RAG fetch into one SQL statement
A dev.to walkthrough builds the whole retrieval path on pgvector and the azure_ai extension inside Azure Database for PostgreSQL. The costs land in the DDL, the extension allowlist, and a transaction that waits on an HTTP call.
The Engineer · Build desk
What happened
- A dev.to walkthrough builds enabling vector search, embedding generation, semantic search, Azure OpenAI generation, a full RAG pipeline and a minimal generative agent against a single Postgres database.
- Its central claim is that finding similar documents a user is allowed to see becomes one SQL query with a similarity clause and a WHERE filter, instead of an application-layer join between two systems that can drift.
- Two extensions carry the work: vector, which is pgvector storing and indexing embeddings, and azure_ai, which calls Azure OpenAI and Azure AI services directly from SQL.
- For indexing, the walkthrough recommends HNSW as the better default for most workloads, with better recall and more memory, and treats IVFFlat as the faster build that needs tuning to table size.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- cost HNSW buys recall with memory, and in the single-instance design that memory competes with the transactional workload already running on the same server.
- exposure The Azure OpenAI endpoint and subscription key are stored in the server's azure_ai settings, so a deployment that cannot use the managed identity the author recommends is holding a long-lived secret in database configuration.
- decision The walkthrough publishes no latency or recall figures, so a team weighing one Postgres against a dedicated vector store has to run its own filtered-recall test before committing a schema that pins the embedding width.
The write path is where co-location costs something. In the walkthrough, `azure_openai.create_embeddings` sits inside the VALUES list of the INSERT, with no separate Python step [8], so Postgres calls the Azure OpenAI deployment named in `azure_ai.set_setting` [5] and holds the statement open until the model answers. For an existing table the same function goes into an `UPDATE ... SET embedding = ...` over all rows, batched to stay under the embedding model's rate limits [9]. A throttled model therefore fails a statement.
The column is declared `VECTOR(1536)` to match text-embedding-3-small's output dimension [7]. That width lives in the DDL. Switching to a model with a different output dimension is an ALTER TABLE plus a re-embed of every row, and the re-embed runs back through the same rate-limited call.
Past demo size the build needs an index. The walkthrough says a sequential scan comparing the query vector against every row works for a demo and falls over past a few thousand rows [10]. At 1,536 dimensions [7], and with a cosine comparison touching every dimension of every candidate, a scan over 5,000 rows costs 7.68 million element operations per query [17]. Its example index is HNSW over `vector_cosine_ops` with `m = 16` and `ef_construction = 64` [12]. Cosine is the choice for text embeddings because magnitude is not meaningful, only direction; `vector_l2_ops` is for cases with a specific reason to care about Euclidean distance [13].
`CREATE EXTENSION IF NOT EXISTS vector` runs only once VECTOR and AZURE_AI are added to the `azure.extensions` server parameter, which the walkthrough does through the Azure Portal under Server Parameters [4]. Step one of this build is a web form. The change happens at the server level, so the developer writing the SQL may not be the one who can make the first line succeed.
Only the retrieval half is portable pgvector. `azure_openai.create_embeddings` comes from `azure_ai`, the extension that calls Azure OpenAI and Azure AI services directly from SQL [3], so every statement containing that call has to move into application code if the database moves.
So the case for one database is structural. In my view it holds where the permission filter is the dominant query shape: a predicate the planner applies in the same statement as the similarity ordering cannot disagree with the rows it filtered, while two systems reconciled in application code can drift out of sync, which is the failure the walkthrough names [2]. Throughput under a selective WHERE clause is a different question, and the same-transaction, same-backup framing [1] leaves it open.
What to watch
- Whether azure_ai gains an async or queued mode so embedding generation stops blocking the writing statement.
- Published filtered-recall figures for HNSW under a selective WHERE clause, the measurement this build omits.
- Whether other managed Postgres services ship in-SQL model calls that make the same one-database pattern portable.