OLTP vs OLAP

OLTP (online transaction processing) and OLAP (online analytical processing) are the two workload categories every database is built around. OLTP optimises for high-frequency single-row reads and writes with sub-50ms latency; OLAP optimises for aggregations across millions to billions of rows in sub-second time. Postgres, MySQL, and Oracle are OLTP; ClickHouse is OLAP, alongside batch warehouses like Snowflake and BigQuery.

TL;DR #

What is OLTP? #

OLTP is the database category that handles operational, transaction-shaped work like a checkout, a balance check, or a profile update. OLTP systems use row-oriented storage and B-tree indexes to make single-row reads and writes return in milliseconds, and they enforce ACID transactions so partial failures cannot leave inconsistent state.

Postgres, MySQL, SQL Server, Oracle, CockroachDB, and AWS Aurora are the canonical OLTP databases. Workloads in this category are write-heavy at the per-row level. A typical e-commerce platform sees thousands of order inserts, inventory decrements, and session updates per second, each touching a single row. OLTP databases handle this profile by keeping a hot working set in memory, fronting it with B-tree indexes, and serialising concurrent writes through multi-version concurrency control (MVCC). The Postgres documentation describes this transaction model in detail; the engine has been the reference OLTP design since the 1990s.

What is OLAP? #

OLAP is the database category that handles analytical, aggregation-shaped work like a revenue dashboard, an ad-hoc cohort query, or a billion-row time-series scan. OLAP systems use column-oriented storage, vectorised execution, and data skipping to scan millions to billions of rows and return aggregates in sub-second time.

ClickHouse, Snowflake, BigQuery, Amazon Redshift, Apache Druid, and DuckDB are the canonical OLAP databases. Workloads are read-heavy and touch a small subset of columns across a large subset of rows: a query that sums order_value and groups by country over a year of orders reads two columns out of fifty, scans hundreds of millions of rows, and returns a result of a few hundred rows. Columnar storage makes this efficient. Only the relevant columns are read from disk, compression ratios on column data run 5-20×, and SIMD-accelerated vectorised execution processes tens of millions of rows per second per core. See the overview of columnar databases for the storage mechanics in depth.

"Our experience with Postgres identified a requirement for high-throughput ingest, coupled with a need for low-latency analytical queries originating from charts and statistics presented to the user. This naturally led us to believe we needed an OLAP/real-time analytical database."

Ankush Gola, co-founder of LangChain

How they differ on storage layout #

The storage layout is the architectural choice every other axis follows from. OLTP databases store rows together on disk because operational queries access most of a row's columns at once (read a user, update a session, insert an order line). OLAP databases store columns together on disk because analytical queries access a few columns across many rows.

The numbers follow the layout. On a row-store, a query that aggregates order_value across 100 million orders must read the entire heap (every row's full payload), because the values are interleaved with all other columns. On a column-store, the same query reads only the order_value column file, compressed 10-20× by columnar encoding (delta, dictionary, ZSTD), and skipped at the granule level by sparse indexes when filters are present. ClickBench, the public OLAP leaderboard at benchmark.clickhouse.com, shows columnar engines completing 42-query analytical workloads in 1-10 seconds on a single node; the same workload on Postgres takes minutes to hours.

The trade is symmetric. Inserting a single row into a column-store touches every column file and triggers eventual background merges. The column layout sustains bulk-insert rates of millions of rows per second but is inefficient for single-row mutations.

How they differ on query patterns #

OLTP queries are narrow and frequent: read one row by primary key, update one row, insert a small batch. OLAP queries are wide: scan a column, filter on a few predicates, group by one or two dimensions, aggregate the rest.

A canonical OLTP query that fetches a single customer's most recent five orders:

SELECT order_id, order_value, created_at
FROM orders
WHERE customer_id = 7842931
ORDER BY created_at DESC
LIMIT 5;

This returns in a few milliseconds on Postgres against a billion-row orders table when customer_id is indexed. The query touches one B-tree page chain and a handful of heap pages.

A canonical OLAP query that totals order value by country for the last quarter:

SELECT country, sum(order_value) AS revenue
FROM orders
WHERE created_at >= '2026-02-01'
GROUP BY country
ORDER BY revenue DESC;

This returns in under a second on ClickHouse against the same billion-row table, because the engine reads only the country, order_value, and created_at column files, prunes partitions, skips granules by sorting key, and aggregates in vectorised batches. On a row-store the query reads the full row payload for every order in the quarter, which is orders of magnitude more I/O.

How they differ on consistency and concurrency #

OLTP and OLAP take different positions on the CAP and consistency trade-offs because their workloads tolerate different things.

OLTP databases guarantee ACID transactions with serialisable or snapshot isolation. A bank transfer between two accounts must debit one and credit the other atomically, or roll back. There is no acceptable intermediate state. Postgres uses MVCC with snapshot isolation by default. OLTP systems handle thousands of concurrent transactions per second by keeping locks short and contention low through row-level locking.

OLAP databases might guarantee ACID at write (an insert either lands or doesn't) but accept eventual consistency on read paths. A dashboard query that runs at 12:00:01 can return data ingested at 12:00:00, with a brief lag through background merges or replication. ClickHouse, Druid, and Pinot use this model deliberately: it's how a column-store sustains millions of rows per second of ingest while keeping read latency low. Concurrency profiles differ too. Traditional OLAP engines (Snowflake, Databricks, BigQuery, Redshift) handle tens of concurrent analytical queries, while modern OLAP engines like ClickHouse can handle hundreds to thousands per node.

HTAP and CDC: where the line blurs #

Some databases are marketed as "unified" or HTAP (hybrid transactional/analytical processing) databases, single systems claiming to run both workloads. They work for narrow joint workloads where transactional and analytical queries share a small footprint, but the underlying architectural trade-offs haven't disappeared: row vs columnar storage, point vs scan I/O, lock-heavy vs lock-light concurrency. See the deep dive on unifying OLTP and OLAP for the full argument.

The standard production pattern in 2026 uses two dedicated databases connected by change data capture (CDC), not a unified one. Postgres or MySQL serve writes; ClickHouse, Snowflake, or BigQuery serve analytical reads; a CDC pipeline replicates between them in seconds.

When to choose OLTP #

Pick an OLTP database when the workload is mostly per-row writes and point reads. Specifically:

• Single-row reads or writes account for the majority of query volume.
• Tail latency must stay below 50ms (checkouts, payments, real-time leaderboards, session state).
• Concurrent transaction rate exceeds 1,000 per second.
• Transactional integrity matters, so multi-row updates must be atomic.
• Data volume sits in the gigabytes to low terabytes, a range Postgres, MySQL, and Oracle handle without partitioning gymnastics.

Postgres is the default choice for greenfield OLTP.

When to choose OLAP #

Pick an OLAP database when the workload is mostly aggregations and the data volume exceeds what a row-store can scan quickly. A workable heuristic:

• Reads scan large ranges, and perform aggregations and GROUP BYs.
• The write path is bulk inserts or streaming ingest, not single-row mutations at high concurrency.
• Total data volume exceeds a terabyte, or query latency on a row-store has crossed multi-second territory.
• Workloads include dashboards, ad-hoc exploration, time-series analytics, or log/metric search.

Traditional cloud data warehouses (Snowflake, BigQuery, Redshift, Databricks SQL) suit batch-oriented BI reporting with minutes-to-seconds latency tolerance. Real-time OLAP engines (ClickHouse, Druid, Pinot) suit sub-second user-facing analytics and streaming ingest. For a speed-ranked shortlist with ClickBench numbers per candidate, see the fastest OLAP databases.