OLTP vs OLAP Database Architectures in Depth

Two workload shapes

Everything else follows from the last row. An OLTP engine minimises the cost of finding and changing one row safely while many others do the same. An OLAP engine minimises the bytes read and the CPU spent per value when one query touches nearly everything.

Two workload shapes

Everything else follows from the last row. An OLTP engine minimises the cost of finding and changing one row safely while many others do the same. An OLAP engine minimises the bytes read and the CPU spent per value when one query touches nearly everything.

Inside a row store

Row stores such as PostgreSQL and MySQL InnoDB keep each row's columns together in fixed-size pages (8 KB in Postgres, 16 KB in InnoDB). A B+tree index maps a key to the page holding the row; with a fan-out of hundreds per page, even a billion-row table is three or four levels deep, and the upper levels sit in the buffer pool. A primary-key lookup is therefore a few page reads, usually all from memory, followed by returning one contiguous row.

Writes are made durable by appending to a write-ahead log and flushing it at commit; the data pages themselves are written back lazily. Group commit batches many transactions' flushes into one disk sync. Multi-version concurrency control (MVCC) lets readers see a consistent snapshot without blocking writers: Postgres keeps old row versions in the table and cleans them up with vacuum, while InnoDB keeps undo records. Row-level locks serialise conflicting writers only.

The same design makes analytics expensive. To sum one 8-byte column, the engine reads every page of the table, because each page interleaves that column with all the others. A 200-byte row means roughly 96% of the bytes read are irrelevant. Execution is traditionally one tuple at a time through an iterator tree, which spends many CPU cycles per value on interpretation overhead. Postgres can parallelise a scan across a few workers, but it remains a single-node engine reading whole rows.

Inside a column store

Column stores such as ClickHouse, DuckDB, Snowflake, BigQuery and Redshift store each column separately, in chunks of many thousands of values. Four techniques compound.

• Projection. A query reads only the columns it names. Two of forty columns means about 5% of the data before anything else happens.
• Encoding and compression. Values in a column share a type and often repeat, so dictionary encoding, run-length encoding, delta encoding and general compressors such as LZ4 or ZSTD shrink them dramatically. A status column with five distinct values needs only a few bits per row.
• Pruning. Each chunk records metadata such as the minimum and maximum value (zone maps, or sparse primary indexes in ClickHouse). A filter on day >= '2026-07-01' skips every chunk whose maximum is earlier, which is extremely effective when data is sorted or clustered by that column.
• Vectorised execution. Operators process batches of values in tight loops that suit CPU caches and SIMD instructions, instead of one tuple per function call. Distributed engines add massively parallel processing, splitting the scan across many nodes.

The costs are the mirror image. Reading one whole row means touching every column file and decompressing a chunk in each. Data is written in large immutable parts, so a single-row update or delete becomes a delete marker plus a rewrite during a later merge, and frequent tiny inserts create many small parts that the engine must keep merging. Column stores want writes in batches and reads in bulk.

Worked example: one orders table, two engines

Take an orders table with 500 million rows and about 40 columns averaging 200 bytes per row: roughly 100 GB in a row store before indexes. The checkout path runs SELECT * FROM orders WHERE id = $1 and small inserts; the finance team wants daily revenue by country for the last 90 days. The figures below are illustrative arithmetic, not benchmarks.

In Postgres the checkout lookup reads a handful of cached index and heap pages and returns in well under a millisecond of server time. The finance query, if the 90 days are scattered across the table, reads most of the 100 GB. Even with an index on the date, fetching tens of millions of rows by index means random heap access. It runs for minutes, competes with checkout for I/O and buffer cache, and holds an old snapshot for its whole duration.

In a column store sorted by (country, day) or partitioned by month, the same query reads three columns: day, country and amount. At 500 million rows that is about 6 GB raw, a fraction of that after encoding and compression, and pruning cuts it to the 90-day window, so the engine scans perhaps a few hundred megabytes and aggregates them in vectorised loops: typically well under a second on one machine.

-- OLTP side: narrow, indexed, transactional (PostgreSQL)
CREATE TABLE orders (
  id         bigint PRIMARY KEY,
  user_id    bigint NOT NULL,
  country    text   NOT NULL,
  amount     numeric(12,2) NOT NULL,
  status     text   NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX orders_user_created ON orders (user_id, created_at DESC);

-- OLAP side: sorted, partitioned, append-friendly (ClickHouse)
CREATE TABLE orders_analytics (
  id         UInt64,
  country    LowCardinality(String),
  day        Date,
  amount     Decimal(12, 2),
  status     LowCardinality(String),
  version    UInt64                   -- source LSN or updated_at, for de-duplication
) ENGINE = ReplacingMergeTree(version)
PARTITION BY toYYYYMM(day)
ORDER BY (country, day, id);

SELECT day, country, sum(amount) AS revenue
FROM orders_analytics FINAL
WHERE day >= today() - 90 AND status = 'paid'
GROUP BY day, country
ORDER BY day, country;

ReplacingMergeTree keeps the row with the highest version per sorting key when parts merge, which is how repeated CDC updates for the same order collapse to the latest state. Because merges happen in the background, queries that must see exactly one row per key use FINAL or an explicit argMax aggregation, at some extra cost.

Moving data between them: CDC, ELT, lakehouse and HTAP

Change data capture reads the OLTP engine's own log instead of querying its tables. In Postgres, a logical replication slot decodes the WAL into row-level inserts, updates and deletes in commit order; tools such as Debezium publish those to Kafka, and a sink loads them into the column store or lakehouse tables. The primary does almost no extra work, and the analytical copy trails by seconds to minutes. Batch ETL or ELT jobs, scheduled with tools such as Airflow and transformed with dbt, are simpler and still fine when hourly or daily freshness is enough.

Three details decide whether the pipeline is trustworthy. Delivery is usually at-least-once, so the sink must be idempotent: carry the source position or a version column and de-duplicate on it. Deletes must be propagated explicitly, as tombstones or soft-delete flags, or the analytical side silently keeps rows that no longer exist. Schema changes on the source must be planned with the sink in mind, since a renamed column can stall or corrupt the stream.

Two architectures reduce the number of copies. A lakehouse stores analytical tables as open formats (Apache Iceberg, Delta Lake or Hudi) on object storage with transactional metadata, so several engines such as Spark, Trino and DuckDB can query the same data. HTAP systems run both workloads in one product: TiDB, for example, keeps a columnar replica (TiFlash) that is fed by its replication log and lets the optimiser pick row or column access per query. They reduce operational surface but are rarely the best at either extreme.

Operational guidance: finding and watching the boundary

Two queries give you most of the operational picture on the Postgres side. The first finds OLAP-shaped statements hiding in your OLTP workload, using the pg_stat_statements extension: queries that return or touch many rows per call and dominate total execution time are the candidates to move.

-- Which statements behave like analytics? (requires pg_stat_statements)
SELECT left(query, 80)                         AS query,
       calls,
       round(total_exec_time)                  AS total_ms,
       round(mean_exec_time)                   AS mean_ms,
       rows / greatest(calls, 1)               AS rows_per_call,
       shared_blks_read + shared_blks_hit      AS blocks_touched
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- How far behind is each CDC consumer, in bytes of retained WAL?
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots
WHERE slot_type = 'logical';

Alert when slot lag grows steadily or a slot goes inactive, and chart the analytical side's freshness as the age of the newest row it holds. On the column-store side, watch the number of active parts per table, merge backlog, bytes scanned per query and the share of queries that skip pruning entirely; the last one usually means a sort key that no longer matches how people filter.

Failure modes

• Analytics on the primary. Long reports compete for I/O and cache, and in Postgres a long-running snapshot holds back vacuum, so dead rows accumulate and tables bloat. Move reports to a replica at minimum, and set statement_timeout for reporting roles.
• Replica conflicts. On a Postgres hot standby, long queries either get cancelled when vacuum records replay, or, with hot_standby_feedback on, push the bloat back to the primary. A replica is not a free warehouse.
• Stalled replication slots. A slot retains WAL until its consumer confirms it. If the CDC connector stops, WAL piles up until the primary's disk fills. Alert on slot lag and set max_slot_wal_keep_size so a dead consumer cannot take the primary down.
• Too many small parts. Inserting row by row into a column store creates thousands of tiny parts and merge storms; ClickHouse eventually rejects inserts. Batch inserts into thousands of rows or seconds of data.
• Transactional work on a column store. Frequent single-row updates and point lookups are slow and expensive there. Keep the system of record in the OLTP engine.
• Double counting and phantom rows. Missing de-duplication or delete handling makes revenue drift upwards over time. Reconcile daily totals between source and sink.

Choosing an architecture

The trade is freshness against complexity and cost. Each step from replica to batch ELT to CDC to HTAP buys fresher analytics and isolation from production, and costs another moving part to monitor.

OLAP vs OLTP vs HTAP: which one fits your workload?

OLTP workloads are many short transactions that read or change a few rows by key, such as placing an order or updating a balance. OLAP workloads are fewer, longer queries that scan and aggregate many rows over a few columns, such as dashboards and reports. HTAP (hybrid transactional and analytical processing) is a single system that serves both, usually by keeping a row store for transactions and a columnar copy for analytics, synchronized inside the database.

Quick answers

• Is a data warehouse OLAP? Yes. A warehouse is the classic OLAP system, and a lakehouse serves the same workload over open table files.
• Is PostgreSQL OLTP or OLAP? OLTP. It handles moderate reporting, and columnar extensions exist, but large scans run better on a column store.
• Can one database do both? Up to a point. Small datasets and occasional reports fit on an OLTP database with a read replica. Past that, either feed a column store or adopt an HTAP engine.
• When is HTAP worth it? When analytics must see transactions within seconds and you would rather not operate a CDC pipeline. If hourly freshness is enough, a replica or batch ELT is simpler and better understood.
• Which side owns the truth? The OLTP system. Treat the analytical copy as derived data that can be rebuilt from it.

What to do next

1. List your top 20 queries by total time and label each OLTP or OLAP by rows and columns touched.
2. Move OLAP-shaped queries off the primary, starting with the most expensive.
3. Pick the freshness each report really needs; use batch ELT where hours are fine and CDC only where they are not.
4. If you adopt CDC, make the sink idempotent, propagate deletes, and alert on replication-slot lag.
5. Design the analytical table's sort key and partitions around its most common filters.
6. Reconcile totals between source and analytical copy every day.
7. Read on: columnar storage, MVCC, CDC with logical decoding, ClickHouse in depth and DuckDB in depth.