The key distinction
Both OLTP and OLAP databases can be described as "read-heavy." The difference is in what kind of reads they're optimized for. This is the single most important thing to understand before choosing a database in a system design.
Online Transaction Processing
- Fetch one user's data right now
- Small result sets (1–100 rows)
- Latency must be <50ms
- Happens thousands of times per second
- Row-level operations: insert, update, delete
- ACID transactions often required
Online Analytical Processing
- Aggregate across millions of rows
- Large result sets or summarized output
- Latency of seconds to minutes is fine
- Runs infrequently (batch jobs, reports)
- Read-only scans of historical data
- No ACID requirement
OLTP = "give me this specific thing right now." OLAP = "summarize everything for me over time." If your query is about one user or one record, it's OLTP. If your query is about all users or all records, it's OLAP. The former needs a row-oriented database. The latter needs a columnar store.
OLTP — when and why
OLTP workloads are what most application code produces. Every time a user logs in, loads their profile, fetches their feed, or places an order — that's OLTP. The query fetches a small, specific set of records by a key, returns quickly, and happens thousands of times per second across your entire user base.
Row-oriented databases store all columns for a row together on disk. When you query by userId, the database reads one contiguous block off disk and returns the row. This is optimal for OLTP because you typically need all or most columns of a specific row. Reading the entire row in one disk seek is fast.
| Query | Pattern | Why OLTP |
|---|---|---|
| SELECT * FROM users WHERE id = 12345 | Point lookup | One row, fast, by key |
| SELECT postId FROM feed WHERE userId = 12345 ORDER BY createdAt DESC LIMIT 20 | Range scan by partition key | Small result set, per-user, must be fast |
| UPDATE users SET is_active = false WHERE id = 12345 | Single row update | Row-level mutation |
| INSERT INTO feed (userId, postId, createdAt) VALUES (...) | Single row insert | ACID write |
All of these queries operate on a small, specific subset of data identified by a key. They are time-sensitive — users are waiting for these responses. They may also involve writes and require consistency guarantees. Row-oriented databases (PostgreSQL, MySQL, Cassandra, DynamoDB) handle these optimally.
OLAP — when and why
OLAP workloads are what analytics dashboards, data scientists, and business intelligence systems produce. "How many posts were created in the last 30 days broken down by region?" requires scanning every post created in that period, grouping by region, and aggregating counts. That's potentially billions of rows.
Columnar databases store all values for a single column together on disk. When you run a SUM or COUNT across millions of rows, the database reads only the column(s) you're aggregating — not the entire row. This is far more efficient for analytics queries because you're typically interested in one or two columns across millions of rows, not all columns of specific rows.
| Query | Pattern | Why OLAP |
|---|---|---|
| SELECT region, COUNT(*) FROM posts WHERE createdAt > '2026-01-01' GROUP BY region | Full scan + group by | Millions of rows, aggregation, not time-sensitive |
| SELECT DAY(timestamp), COUNT(DISTINCT userId) FROM events WHERE eventType='FEED_LOAD' | Time-series aggregation | Batch report, runs nightly |
| SELECT AVG(session_duration) FROM sessions WHERE device='mobile' | Column aggregation | Only needs one column across all rows |
These queries don't need to return fast — analysts and dashboards can wait seconds or minutes. They also don't write data — they read historical event records. Columnar stores (Snowflake, BigQuery, OCI ADW, Redshift) are specifically optimized for this access pattern: scanning entire columns efficiently, compressing column data (similar values compress well), and executing aggregations in parallel across many nodes.
Row-oriented vs columnar storage
The performance difference comes from how data is physically stored on disk. Understanding this makes the OLTP/OLAP choice obvious rather than arbitrary.
| Dimension | Row-oriented (OLTP) | Columnar (OLAP) |
|---|---|---|
| Disk layout | All columns for row 1, then row 2, etc. | All values for col 1, then col 2, etc. |
| Best for | Fetch all columns of specific rows | Aggregate one column across all rows |
| Worst for | Aggregating one column across millions of rows | Fetching a single specific row quickly |
| Compression | Moderate (mixed values per block) | Excellent (similar values in same block) |
| Write speed | Fast (append a row) | Slow (must update column store) |
| Examples | PostgreSQL, MySQL, Cassandra | Snowflake, BigQuery, OCI ADW |
During the News Feed design, the feed table was initially proposed as a candidate for a data warehouse (ADW). The feed table is queried 5,800 times per second with "give me userId=X's last 20 posts." That is a point lookup — pure OLTP. A columnar data warehouse reconstructs rows by reading across multiple column stores — this is much slower than a row-oriented lookup. Using ADW for the feed table would produce a system where loading a user's feed takes seconds instead of milliseconds. Always identify the access pattern before choosing storage.
Decision table — which database, when
| Workload type | DB choice | News Feed example |
|---|---|---|
| Structured, ACID, low volume | SQL (PostgreSQL, MySQL) | User table |
| Semi-structured, variable schema | Document DB (MongoDB) | Post table |
| High frequency key lookups, scale | NoSQL (Cassandra, DynamoDB) | Feed table |
| Graph traversal — who follows whom | Graph DB (Neo4j) | Social graph |
| Batch analytics, aggregations, reporting | ADW / Columnar (Snowflake, BigQuery) | Analytics events |
| Hot key-value cache, sub-millisecond | Redis | Feed cache, post cache |