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
التالي · الدرس 5 من 45
Case Study - How TikTok Uses SQL
قراءة 2 دقيقة
أنهيت 4 من 45 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

