peterbamuhigire/postgresql-ai-platform

PostgreSQL as an AI Data Platform

<!-- dual-compat-start -->

Use When

• PostgreSQL as an AI data platform, sourced from "Building a Data and AI Platform with PostgreSQL" (2025). Covers pgvector for embeddings storage and ANN search (IVFFlat/HNSW), Retrieval-Augmented Generation (RAG) pipeline design, AI application design patterns (schema-aware LLM, NL2SQL guardrails, chunking strategies), 16 critical AI build fault lines, pgai extension, and sovereign data platform principles. Companion to ai-rag-patterns, postgresql-fundamentals, and postgresql-advanced-sql.
• The task needs reusable judgment, domain constraints, or a proven workflow rather than ad hoc advice.

Do Not Use When

• The task is unrelated to postgresql-ai-platform or would be better handled by a more specific companion skill.
• The request only needs a trivial answer and none of this skill's constraints or references materially help.

Required Inputs

• Gather relevant project context, constraints, and the concrete problem to solve.
• Confirm the desired deliverable: design, code, review, migration plan, audit, or documentation.

Workflow

• Read this SKILL.md first, then load only the referenced deep-dive files that are necessary for the task.
• Apply the ordered guidance, checklists, and decision rules in this skill instead of cherry-picking isolated snippets.
• Produce the deliverable with assumptions, risks, and follow-up work made explicit when they matter.

Quality Standards

• Keep outputs execution-oriented, concise, and aligned with the repository's baseline engineering standards.
• Preserve compatibility with existing project conventions unless the skill explicitly requires a stronger standard.
• Prefer deterministic, reviewable steps over vague advice or tool-specific magic.

Anti-Patterns

• Treating examples as copy-paste truth without checking fit, constraints, or failure modes.
• Loading every reference file by default instead of using progressive disclosure.

Outputs

• A concrete result that fits the task: implementation guidance, review findings, architecture decisions, templates, or generated artifacts.
• Clear assumptions, tradeoffs, or unresolved gaps when the task cannot be completed from available context alone.
• References used, companion skills, or follow-up actions when they materially improve execution.

References

• Use the links and companion skills already referenced in this file when deeper context is needed.

<!-- dual-compat-end -->

Why PostgreSQL for AI

• Single source of truth — transactional data + vector embeddings in one database
• pgvector — native approximate nearest-neighbour search (ANN)
• ACID guarantees — embeddings stay consistent with the data they represent
• Rich SQL + AI — join embedding search results with structured filters in one query
• Sovereignty — your data stays in your infrastructure

pgvector — Embeddings in PostgreSQL

Setup

CREATE EXTENSION IF NOT EXISTS vector;

Storing Embeddings

-- Documents with embeddings (1536 dims = OpenAI text-embedding-3-small)
CREATE TABLE documents (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    content     TEXT NOT NULL,
    metadata    JSONB,
    embedding   vector(1536),
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- 3072 dims for text-embedding-3-large
-- 768 dims for nomic-embed-text
-- 384 dims for all-MiniLM-L6-v2

Inserting Embeddings

-- Insert with embedding (from application layer)
INSERT INTO documents (content, metadata, embedding)
VALUES (
    'PostgreSQL supports full-text search natively.',
    '{"source": "docs", "section": "fts"}',
    '[0.021, -0.034, ...]'::vector   -- array from embedding API
);

Similarity Search

-- Cosine distance (recommended for text embeddings — angle, not magnitude)
SELECT id, content, metadata,
       1 - (embedding <=> '[0.021, -0.034, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.021, -0.034, ...]'::vector
LIMIT 10;

-- L2 distance (Euclidean — good for image embeddings)
SELECT id, content, embedding <-> query_vec AS distance
FROM documents
ORDER BY embedding <-> '[...]'::vector LIMIT 10;

-- Inner product (for normalized vectors)
SELECT id, content, (embedding <#> query_vec) * -1 AS score
FROM documents ORDER BY embedding <#> '[...]'::vector LIMIT 10;

Hybrid Search: Vector + Metadata Filter

-- Combine semantic search with structured filter (critical for multi-tenant)
SELECT id, content,
       1 - (embedding <=> $1::vector) AS score
FROM documents
WHERE metadata->>'tenant_id' = $2       -- hard filter
  AND metadata->>'section' = 'faq'      -- structured filter
ORDER BY embedding <=> $1::vector
LIMIT 10;

Index Types for ANN

-- IVFFlat: faster build, lower memory, slightly less accurate
-- lists: sqrt(row_count) is a good starting point
CREATE INDEX documents_embedding_ivf ON documents
    USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);

-- HNSW: faster queries, higher recall, more memory
-- Recommended for production: better accuracy/speed tradeoff
CREATE INDEX documents_embedding_hnsw ON documents
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Adjust search quality at query time (HNSW)
SET hnsw.ef_search = 100;   -- higher = more accurate, slower

-- Adjust probe count at query time (IVFFlat)
SET ivfflat.probes = 10;

Choosing Index Type

| | IVFFlat | HNSW | |---|---|---| | Build speed | Fast | Slower | | Memory | Low | Higher | | Query recall | Good | Excellent | | Recommended | Dev / low-memory | Production |

RAG Pipeline Design

Architecture

User Query
    │
    ▼
Embed Query (LLM API)
    │
    ▼
Vector Search (pgvector) + Metadata Filters
    │
    ▼
Retrieve Top-K Chunks
    │
    ▼
Build Prompt (system + chunks + user query)
    │
    ▼
LLM Generation
    │
    ▼
Response to User

Chunking Strategy

Chunk quality determines retrieval quality. Rules from the book:

| Strategy | When to Use | |---|---| | Fixed-size with overlap | Homogeneous text (logs, support tickets) | | Sentence / paragraph | Articles, documentation — preserve semantic units | | Section-based | Structured docs (manuals, legal) — chunk at headings | | Semantic chunking | Use embedding similarity to find natural break points |

-- Store chunk metadata for context reconstruction
CREATE TABLE chunks (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    document_id   BIGINT REFERENCES documents(id) ON DELETE CASCADE,
    chunk_index   INT NOT NULL,
    content       TEXT NOT NULL,
    token_count   INT,
    embedding     vector(1536),
    metadata      JSONB,   -- {section, page, heading, source_url}
    UNIQUE (document_id, chunk_index)
);

CREATE INDEX chunks_embedding_hnsw ON chunks
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

Keeping Embeddings Fresh

-- Track embedding generation status
ALTER TABLE chunks ADD COLUMN embedded_at TIMESTAMPTZ;
ALTER TABLE chunks ADD COLUMN embedding_model TEXT;

-- Queue-based re-embedding on content change
CREATE FUNCTION queue_reembedding() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.content IS DISTINCT FROM OLD.content THEN
        NEW.embedding := NULL;
        NEW.embedded_at := NULL;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER chunks_reembed
    BEFORE UPDATE ON chunks
    FOR EACH ROW EXECUTE FUNCTION queue_reembedding();

-- Application worker polls:
SELECT id, content FROM chunks WHERE embedding IS NULL LIMIT 100;

Metadata-Rich Retrieval

-- Retrieve with full context for prompt building
SELECT
    c.content,
    c.metadata->>'heading' AS section,
    c.metadata->>'source_url' AS source,
    d.metadata->>'document_title' AS doc_title,
    1 - (c.embedding <=> $1::vector) AS score
FROM chunks c
JOIN documents d ON d.id = c.document_id
WHERE d.metadata->>'tenant_id' = $2
ORDER BY c.embedding <=> $1::vector
LIMIT 5;

AI Application Design Patterns

Schema-Aware LLM (NL2SQL)

Provide schema context to the LLM so it generates valid SQL:

-- Store schema snapshots for LLM context
CREATE TABLE schema_context (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name  TEXT NOT NULL,
    description TEXT NOT NULL,   -- business-level description
    columns     JSONB NOT NULL,  -- [{name, type, description}]
    sample_data JSONB,           -- 2-3 representative rows
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

NL2SQL guardrails (mandatory):

-- Validate generated SQL before execution
-- 1. Only allow SELECT — reject DML
-- 2. Enforce row limit
-- 3. Timeout guard
-- 4. Schema-bound: only allow known tables

SET statement_timeout = '5s';
SET row_security = on;   -- RLS as second safety layer

Inserting AI Outputs Safely

-- Never pass LLM output directly to SQL — always parameterised
-- Application must extract structured fields, not interpolate raw text

-- Structured AI output schema
CREATE TABLE ai_insights (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    source_query TEXT NOT NULL,
    model_used   TEXT NOT NULL,
    insight      TEXT NOT NULL,
    confidence   NUMERIC(3,2),
    raw_response JSONB,         -- full API response for debugging
    created_at   TIMESTAMPTZ DEFAULT NOW()
);

pgai Extension

CREATE EXTENSION IF NOT EXISTS ai;

-- Generate embeddings via pgai (calls OpenAI internally)
SELECT ai.openai_embed('text-embedding-3-small', content) AS embedding
FROM documents;

-- Generate text
SELECT ai.openai_chat_complete(
    'gpt-4o',
    jsonb_build_array(
        jsonb_build_object('role', 'user', 'content', 'Summarise: ' || content)
    )
)->>'content' AS summary
FROM documents WHERE id = 1;

16 Critical AI Build Fault Lines

From "Building a Data and AI Platform with PostgreSQL":

Business Fault Lines

1. Team misalignment — data engineers, ML engineers, and product must agree on data definitions before building
2. GenUX too early — validate core data quality before adding AI UI features
3. Unscalable feedback loops — build structured human feedback from day one (ai_feedback table)
4. Undefined ROI / success metrics — define evaluation metrics before shipping any AI feature
5. AI ethics and data privacy oversights — classify data sensitivity before sending to LLM APIs
6. Insufficient data product thinking — treat data as a product with SLAs, not a by-product

Technical Fault Lines

7. Schema drift — generated SQL breaks when columns are renamed; use schema versioning
8. Latent infrastructure debt — connection pooling, index maintenance, VACUUM — all affect AI query latency
9. Lack of embedding/retrieval optimisation — wrong chunking kills retrieval quality
10. Overdependence on a single AI provider — always abstract the LLM client layer
11. Missing real-world use case playbooks — test with real user queries, not synthetic ones
12. SQL generation without guardrails — LLM-generated SQL must be validated before execution
13. Lack of multi-tiered architecture — separate OLTP (transactions), OLAP (analytics), and vector (RAG)
14. Inadequate prompt engineering lifecycle — version and evaluate prompts like code
15. Lack of real-time schema awareness — LLM context must reflect current schema, not a stale snapshot
16. Lack of observability — log every LLM call: model, tokens, latency, cost, success/failure

Observability Schema

CREATE TABLE llm_call_log (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    feature         TEXT NOT NULL,         -- 'rag_qa', 'nl2sql', 'summarise'
    model           TEXT NOT NULL,
    prompt_tokens   INT,
    completion_tokens INT,
    total_tokens    INT,
    latency_ms      INT,
    cost_usd        NUMERIC(10,6),
    success         BOOLEAN NOT NULL,
    error_message   TEXT,
    user_id         BIGINT,
    tenant_id       BIGINT,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX llm_log_tenant_feature ON llm_call_log (tenant_id, feature, created_at DESC);

Sovereign AI Data Platform Principles

1. Data stays in your infrastructure — use local models (Ollama + pgvector) or private cloud when data is sensitive
2. Transactional data is the competitive moat — your proprietary data is what makes AI valuable
3. Platform over silos — one PostgreSQL cluster with pgvector + PostGIS + JSONB beats five specialised databases
4. Build on open standards — PostgreSQL extensions, not vendor-locked vector databases
5. Compliance by design — data classification, RLS, and audit triggers before AI features

AI Extension Stack

CREATE EXTENSION vector;         -- pgvector: ANN search
CREATE EXTENSION ai;             -- pgai: LLM calls from SQL
CREATE EXTENSION pg_trgm;        -- fuzzy text search (hybrid retrieval)
CREATE EXTENSION pg_stat_statements; -- query observability
CREATE EXTENSION pg_cron;        -- schedule embedding jobs

-- Optional
CREATE EXTENSION postgis;        -- geospatial AI (location-aware RAG)
CREATE EXTENSION timescaledb;    -- time-series AI (sensor + event data)

Anti-Patterns

• Creating a vector database silo separate from your OLTP database — sync complexity + consistency issues
• Storing raw LLM output in TEXT without structured fields — unqueryable, unauditable
• IVFFlat index built before data is loaded — lists parameter becomes wrong
• Querying embeddings without metadata pre-filters in multi-tenant systems — returns other tenants' data
• Sending PII to external embedding APIs without privacy review
• No re-embedding pipeline when source content changes — stale embeddings = wrong answers
• Missing statement_timeout on NL2SQL execution — runaway queries possible