PostgreSQL AI Edition (pg_ai)
AI inside PostgreSQL: embeddings, semantic search, RAG and agents callable from SQL, with local inference and no API key. Your embeddings live next to your data (ACID, JOINs, Row-Level Security); there's no second vector database to sync. It's a pure extension on PostgreSQL 16/17, not a fork.
Open source (PostgreSQL License) → github.com/devjamez/postgresql-ai-edition
What it is
A thin layer (`pg_ai`) that orchestrates a local model via PL/Python, plus `pg_ai_core`, a native C core that integrates with the engine: it installs a `planner_hook` and a Custom Scan (`pg_ai_fusion`) for relational + vector plan fusion. Everything runs inside the database.
From SQL
- `ai.embed(text)` / `ai.embed_batch(text[])` — embeddings (Ollama `nomic-embed-text`, 768d).
- `ai.rag(question, table, content_col, emb_col)` — retrieval-augmented answer grounded in your tables.
- `ai.filtered_search` / `ai.filter_ann` — filtered ANN (safe equality filters + vector order + correct top-k under selective filters).
- `ai.search_mmr` (diversity) and `ai.rerank` (listwise LLM reranker).
- `ai.create_agent` / `ai.call_agent` — agents with memory; `ai.call_agent_tools` (ReAct loop) with `ai.register_tool` / `ai.run_tool`.
- `ai.register_workflow` / `ai.run_workflow` — multi-step pipelines (tool / complete / rag) chained together.
- `ai.submit_task` + `ai.schedule` — async and recurring (cron-like) runs drained by a background worker in `pg_ai_core`.
Why in-database
- One source of truth (ACID): the embedding is a `vector` column next to its row, updated in the same transaction. No dual-write, no re-sync jobs.
- Semantic search composes with SQL: JOIN + relational filter + vector ordering + transaction in a single query.
- Security for free: roles, `GRANT` and Row-Level Security apply to `ai.rag` / `ai.filtered_search` — multi-tenant isolation with no authorization logic duplicated elsewhere.
- AI-aware engine: `pg_ai_core` (C) hooks the planner and adds a custom scan; integration at the engine level, not a wrapper.
- Agent runtime in the database: agents, memory, tools, workflows and a queue are transactional, backed by `pg_dump`/PITR and replication. Fewer moving parts.
Security
- No API key by default (local Ollama); any optional key (Anthropic) lives only in the server environment, never in SQL or the repo.
- Injection-safe filters (`jsonb` of equalities, escaped via `%I/%L`); raw SQL predicates are isolated in `ai.filter_ann_raw` (trusted input only).
- Deny-by-default: `EXECUTE` on the network/raw functions is revoked from `PUBLIC`; grant to trusted roles deliberately.
How to run it
`docker compose up -d --build`; extensions auto-install (`CREATE EXTENSION pg_ai CASCADE` + `pg_ai_core`). Local models via Ollama (`nomic-embed-text` + `llama3.1:8b`); prebuilt image on GHCR. No GPU or API key required.
Stack
PostgreSQL 16/17 · pgvector (HNSW) · PL/Python · C (PGXS, planner_hook + custom scan) · Ollama · Docker.