Lesson 1.3: OLTP vs OLAP vs AI Workloads

The Three Database Workload Patterns

OLTP (Online Transaction Processing)

-- Typical OLTP: Process a user action
BEGIN;
INSERT INTO orders (user_id, total) VALUES (123, 49.99);
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 456;
UPDATE users SET last_order = NOW() WHERE id = 123;
COMMIT;

Characteristics:

• Small, fast transactions
• High concurrency (thousands/second)
• Row-based storage
• Examples: E-commerce checkout, banking transfers

OLAP (Online Analytical Processing)

-- Typical OLAP: Generate a business report
SELECT
    DATE_TRUNC('month', created_at) as month,
    product_category,
    SUM(total) as revenue,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
WHERE created_at > NOW() - INTERVAL '2 years'
GROUP BY 1, 2
ORDER BY 1 DESC, revenue DESC;

Characteristics:

• Large scans across millions of rows
• Complex aggregations
• Column-based storage (ClickHouse, BigQuery)
• Examples: Business intelligence, data warehouses

AI Workloads (Hybrid)

-- Typical AI workload: RAG with hybrid search
SELECT
    documents.id,
    documents.content,
    documents.metadata,
    embeddings.vector <=> query_vector AS similarity
FROM documents
JOIN embeddings ON documents.id = embeddings.document_id
WHERE
    documents.user_id = 123  -- Permission filter
    AND documents.category = 'support'  -- Metadata filter
    AND embeddings.vector <=> query_vector < 0.7  -- Vector similarity
ORDER BY similarity
LIMIT 10;

Characteristics:

• Mix of OLTP (fast lookups) and OLAP (scans)
• Hybrid queries (vector + SQL)
• Massive batch writes (embedding uploads)
• Real-time + analytics
• Requires specialized indexes

Key insight: AI workloads don't fit cleanly into OLTP or OLAP—they're a hybrid that requires understanding both.

Key Takeaways

• OLTP handles small, fast transactions with high concurrency
• OLAP handles large analytical queries with aggregations
• AI workloads are hybrid: combining OLTP's speed with OLAP's scanning capabilities
• AI systems require specialized indexes to handle vector similarity + SQL filters efficiently
• Understanding all three workload types is essential for building performant AI systems