Building RAG Inside PostgreSQL: How pg_ai Hooks the Query Planner for Vector Search
Hook
What if your database could generate embeddings, retrieve semantically similar rows, and call an LLM—all in a single SQL transaction without a separate vector store?
Context
Modern RAG architectures force you to maintain two sources of truth: a relational database for structured data and a vector database like Pinecone or Weaviate for embeddings. You write to Postgres, fire off an async job to chunk and embed the content, then push vectors to your vector store. When a user queries, you search the vector DB for similar embeddings, fetch the corresponding IDs, join back to Postgres for the actual data, and send everything to an LLM API. This dual-write pattern introduces eventual consistency bugs—your vector index lags behind your data, invalidation is manual, and access control splits across two systems. Row-level security policies in Postgres don't transfer to your vector store, so you replicate permission logic in application code.
The pg_ai extension collapses this into one system by embedding Ollama integration and vector operations directly into PostgreSQL. It's built as two loadable extensions: a PL/Python layer that wraps HTTP calls to Ollama for embeddings and completions, and a native C extension that installs hooks into PostgreSQL's query planner and executor. You store vectors as columns alongside your data, generate embeddings in the same transaction as inserts, and run semantic search with plain SQL. The ACID guarantees, replication, and permissions model you already rely on now apply to your AI workloads. Instead of maintaining sync jobs between databases, you write one INSERT with a trigger that calls ai.embed()—the vector column updates atomically, and if the transaction rolls back, neither the row nor its embedding persists.
Technical Insight
The core engineering trick is PostgreSQL's hook system. Postgres exposes function pointers—planner_hook, ExecutorStart_hook, set_rel_pathlist_hook—that extensions can override to intercept and modify query execution. The pg_ai_core C extension installs a planner_hook that fires before Postgres generates a query plan, inspecting the parse tree for vector similarity queries. When it detects a pgvector distance operator (e.g., embedding <-> ai.embed('query')), it logs telemetry and delegates to a custom scan node called pg_ai_fusion. The custom scan is currently a placeholder—it passes execution back to standard nodes—but the architecture is there for future work that would push relational filters directly into HNSW graph traversal, avoiding the two-phase retrieve-then-filter pattern that kills recall.
The real optimization happens in the ExecutorStart hook, which rewrites certain queries to use adaptive over-fetching. Here's the filtered ANN problem: you want the 10 most similar documents where category = 'engineering', but HNSW indexes don't natively handle predicates. If you filter before searching (pre-filter), you lose the index and scan all engineering docs. If you search then filter (post-filter), the top-10 results might not satisfy the predicate, so you return fewer than 10 rows. The pg_ai solution is ai.filter_ann(), which over-fetches—retrieve 100 candidates from the HNSW index, apply the SQL predicate in Postgres, then return the true top-10:
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT,
category TEXT,
embedding vector(768)
);
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
-- Naive post-filter: might return < 10 rows
SELECT id, content FROM documents
WHERE category = 'engineering'
ORDER BY embedding <-> ai.embed('database optimization')
LIMIT 10;
-- Adaptive over-fetch: guarantees 10 results if they exist
SELECT * FROM ai.filter_ann(
'documents',
'embedding',
ai.embed('database optimization'),
10,
'category = ''engineering''',
over_fetch_factor => 10
);
The over_fetch_factor parameter controls the trade-off: higher values waste I/O but improve recall under selective predicates. Internally, ai.filter_ann() calls pgvector to retrieve N × k candidates, then applies the WHERE clause as a table scan over that subset. It's not as efficient as pushing the predicate into the index, but it's correct and works without forking pgvector.
The RAG workflow is pure SQL. The ai.rag() function embeds your question, retrieves similar rows from a vector column, concatenates their content into a prompt template, and sends it to Ollama via ai.complete():
-- One-line RAG: embed question, retrieve context, generate answer
SELECT ai.rag(
table_name => 'documents',
content_column => 'content',
embedding_column => 'embedding',
question => 'How do I optimize pgvector queries?',
top_k => 5,
model => 'llama3'
);
Under the hood, ai.rag() is PL/Python calling the Python requests library to POST to Ollama's HTTP API. The embed endpoint returns a JSON array of floats, which gets cast to PostgreSQL's vector type. The completions endpoint streams tokens, but the extension blocks until the full response arrives—no async I/O, so concurrent queries pile up waiting for Ollama. Each call spawns a new HTTP connection; there's no pooling or keep-alive.
Agent workflows leverage a background worker and task queue. Agents are rows in ai.agents with a system prompt and memory retention policy. You call ai.submit_task() to enqueue work, and a Postgres background worker (BGW) drains the queue asynchronously. Tasks and memory are stored in tables (ai.tasks, ai.agent_memory), so they replicate via WAL and survive crashes. This makes agent state transactional—you can INSERT an agent definition, submit tasks, and ROLLBACK to undo everything. Row-level security works: if user A can't SELECT an agent, they can't invoke it or see its memory. The downside is memory retrieval is a linear scan—ai.call_agent() does SELECT * FROM ai.agent_memory WHERE agent_id = $1 ORDER BY created_at, with no vector similarity or summarization. Long conversations load the entire history into the LLM context window until you hit token limits.
The planner_hook architecture is forward-looking. Right now, the pg_ai_fusion custom scan node logs which queries could benefit from fused execution but doesn't change the plan. The vision is to eventually modify the planner's cost model: when it sees a filtered vector query, inject a scan path that evaluates predicates during HNSW traversal instead of afterward. This requires deep integration with pgvector's internals—tracking distance bounds while navigating the graph, pruning branches where relational filters fail. The hooks are installed, the scan node is registered, but the logic isn't implemented. It's scaffolding for a future where Postgres natively optimizes hybrid queries.
Gotcha
The plpython3u dependency is a non-starter for most production deployments. AWS RDS, Google Cloud SQL, and Azure Database for PostgreSQL don't allow untrusted procedural languages—they only support plpythonu in restricted configurations, and pg_ai needs the untrusted variant to make HTTP calls and import arbitrary Python libraries like requests. This means you must self-host Postgres or use a provider like Crunchy Data or Aiven that permits plpython3u. If your org mandates managed Postgres, this extension is off the table.
The synchronous HTTP model blocks backends during inference. Every ai.embed() or ai.complete() call holds a Postgres connection while waiting for Ollama to respond. If Ollama is running locally on the same machine, latency might be tolerable—50-200ms for embeddings, 1-5 seconds for completions depending on model size. But under concurrent load, you exhaust max_connections as backends pile up waiting for LLM responses. There's no timeout handling, so a hung Ollama process deadlocks your queries. The agent background worker mitigates this for async tasks, but synchronous RAG queries (SELECT ai.rag(...)) tie up connection slots. You'd need pgBouncer in transaction pooling mode and very aggressive statement timeouts to prevent cascading failures.
Agent memory grows unbounded and has no retrieval strategy. The ai.agent_memory table is append-only with no cleanup policy. After thousands of interactions, ai.call_agent() scans gigabytes of history to build context, and you're sending 100k+ tokens to Ollama, which either truncates or fails. There's no embedding of past exchanges for semantic retrieval (e.g., 'find similar conversations'), no summarization to compress history, and no sliding window logic. You'd have to write your own memory eviction triggers or periodic VACUUM jobs. The RLS integration is elegant—tenant isolation comes for free—but the lack of memory management makes multi-turn agents a timebomb.
Verdict
Use if: You're self-hosting Postgres, already running Ollama locally, and want to eliminate a separate vector database from your architecture. This extension shines when you have relational data you want to semantically search without ETL jobs—documents, support tickets, product descriptions—and you value ACID consistency and unified access control over raw performance. If your queries involve selective filters on metadata (category, date ranges, user permissions) combined with vector similarity, the adaptive over-fetch pattern handles it correctly where naive pgvector fails. The transactional agent state is compelling for multi-tenant SaaS where RLS and pg_dump already solve your isolation and backup needs. Skip if: You're on managed Postgres (RDS, Cloud SQL), need high-concurrency LLM inference, or require production-grade agent orchestration. The plpython3u dependency is a deployment dealbreaker, the synchronous HTTP calls don't scale under load, and agent memory management is too primitive for long-running workflows. If you're building a customer-facing RAG product, stick with pgvector for storage and handle embeddings/completions in application code with proper async clients, retries, and observability. This is a prototype-to-production tool for self-hosters, not a drop-in replacement for LangChain or a managed vector DB.