Data & Databases
SQL, schemas, indexing and the data layer every AI system depends on.
Databases for AI
Every AI system depends on a database: conversation history, user data, documents, embeddings (in a vector DB), audit logs, agent state. SQL (PostgreSQL) is the production default — it handles relational data, JSON, full-text search (tsvector) AND vector search (pgvector) in one database.
Why databases matter
AI engineers who skip SQL end up with data in memory (lost on restart), in flat files (slow, no concurrency), or scattered across services. A real database gives you: persistence, concurrency, transactions, indexing, and query power. For RAG, pgvector puts your vectors next to your metadata — no separate vector DB.
Data architecture
PostgreSQL with: tables for relational data (users, conversations, audit), JSONB for flexible fields, tsvector + GIN index for full-text search, pgvector + HNSW index for embeddings. One database, multiple access patterns. Add Redis for caching and queues.
SQL schema for an AI app
-- Users
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT UNIQUE NOT NULL,
name TEXT,
permission_groups TEXT[] DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Conversations with messages as JSONB
CREATE TABLE conversations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id),
title TEXT,
messages JSONB DEFAULT '[]',
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_conversations_user ON conversations(user_id);
CREATE INDEX idx_conversations_messages_gin ON conversations USING GIN (messages);
-- Documents with full-text search
CREATE TABLE documents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
source TEXT NOT NULL,
title TEXT,
content TEXT NOT NULL,
permission_group TEXT NOT NULL,
tsv TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_documents_tsv ON documents USING GIN (tsv);
CREATE INDEX idx_documents_group ON documents(permission_group);
-- Chunks with embeddings (pgvector)
CREATE TABLE chunks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
text TEXT NOT NULL,
section TEXT,
position INT,
permission_group TEXT NOT NULL,
embedding VECTOR(1024),
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_chunks_embedding ON chunks USING HNSW (embedding vector_cosine_ops);
CREATE INDEX idx_chunks_group ON chunks(permission_group);
CREATE INDEX idx_chunks_doc ON chunks(document_id);
-- Audit log (append-only)
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
user_id UUID,
action TEXT NOT NULL,
query TEXT,
response TEXT,
model TEXT,
cost_usd NUMERIC(10,6),
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_audit_user_time ON audit_log(user_id, created_at DESC);Experiment: indexing
See how indexes affect query performance.
What to observe
Indexes are not interchangeable. B-tree for equality/range, GIN for full-text and JSONB containment, HNSW for vector similarity. The wrong index = no speedup. Always EXPLAIN your queries in production to verify the index is used.
Production databases
Production databases: index per access pattern, EXPLAIN ANALYZE on slow queries, connection pooling (PgBouncer), read replicas for analytics, backups (PITR), migrations as code (Prisma/Alembic), and monitoring (slow query log, index usage, table bloat).
Challenge
Your RAG query is slow (3.2s). EXPLAIN shows a sequential scan on chunks. Design the right index and verify it's used.
Production checklist
Production checklist
0 of 8 checked
Knowledge check
Your vector search is slow despite an index. EXPLAIN shows a sequential scan. What's wrong?
Complete
You can now design production database schemas. Next: Docker and deployment — containerising and shipping.
Mark this chapter as complete
Track your progress and unlock the next chapter.