Transactional vs Analytical systems

Definitions

• OLTP - Online Transaction Processing
• OLAP - Online Analytical Processing

---

Core Difference

Aspect	OLTP (Transactional)	OLAP (Analytical)
Purpose	Day-to-day operations	Data analysis & reporting
Query type	Simple, point queries	Complex, aggregations
Writes	Frequent, real-time	Mostly batch loads
Data focus	Current state	Historical snapshots
Response time	< 100ms	Seconds acceptable

---

Real-World Example: Trading System

OLTP - Live Trading Platform

• Placing an order
• Checking account balance
• Updating portfolio positions
• Verifying position limits
• Real-time trade execution

Query examples:

SELECT balance FROM accounts WHERE user_id = 123;
INSERT INTO orders (symbol, quantity, price) VALUES ('AAPL', 100, 150.50);

OLAP - Analytics Dashboard

• Monthly P&L reports
• Trader profitability analysis
• Average execution time by symbol
• Sector correlation analysis

Query examples:

SELECT DATE_TRUNC('month', execution_time), SUM(amount) as revenue
FROM orders GROUP BY DATE_TRUNC('month', execution_time);

---

Why Separate Databases?

1. Different access patterns conflict - Analytics queries scan millions of rows, lock tables, slow down transactional operations

2. Storage optimization differs

   • OLTP: Optimized for writes, point reads
   • OLAP: Optimized for full scans, compression

3. ACID vs Performance

   • OLTP: Needs strict consistency (money transfers must be all-or-nothing)
   • OLAP: Can relax consistency for faster batch loads

---

Data Structure: Normalized vs Denormalized

Normalized (OLTP) - Reduce Redundancy

• Each piece of data stored once
• Multiple related tables
• Updates happen in one place
• Example: users, orders, symbols, sectors tables joined together

Denormalized (OLAP) - Reduce Joins

• All data in single wide table
• Pre-aggregated values
• Avoids JOINs on millions of rows
• Example: daily_trade_summary with ticker, company, sector, volume all in one row

---

Query Strategies

OLTP - Do

• Use primary key lookups
• Query by indexed columns
• Fetch only columns you need
• Use batch inserts for bulk operations
• Keep transactions short

OLTP - Avoid

• Joining too many tables (common path: 1-2, rare: 4+)
• Unbounded queries (always use LIMIT)
• Functions on indexed columns
• SELECT FOR UPDATE without need

OLAP - Do

• Use columnar storage (select only needed columns)
• Partition by date/region
• Pre-aggregate data
• Use materialized views
• Batch ETL loads

OLAP - Avoid

• Real-time requirements
• Frequent small writes
• Complex joins in real-time

---

In Practice

┌─────────────────┐     ┌──────────────┐     ┌─────────────────┐
│   Trader App    │────▶│  OLTP DB     │────▶│   OLAP DB       │
│  (buy AAPL)     │     │ PostgreSQL   │     │ Snowflake       │
│                 │     │ (live data)  │     │ (analytics)     │
└─────────────────┘     └──────────────┘     └─────────────────┘
        │                        │
        │                        │ Nightly ETL
        │                        ▼
        │                 ┌──────────────┐
        │                 │  Copy data   │
        │                 └──────────────┘
        ▼
   Immediate
  confirmation

Common pattern: App writes to OLTP → ETL copies to OLAP nightly → Analysts query OLAP

---

Summary

• OLTP = Write-heavy, normalized, current data, fast individual reads
• OLAP = Read-heavy, denormalized, historical data, batch writes, complex aggregations
• Use OLTP for operational needs (trading, orders, user management)
• Use OLAP for business insights (reports, dashboards, trends)