OLTP systems handle transactions, OLAP systems handle analytics. In 2026, CDC pipelines, HTAP engines, and real-time OLAP have blurred that line - here is where one engine works and where you still need two.
At some point every backend needs to do two things that hate each other: record transactions and scan them. One workload inserts orders, updates profiles, and decrements inventory with strict correctness, thousands of times a second. The other reads months of those orders to compute revenue by region or a retention curve. The first is OLTP, the second is OLAP, and since Codd coined the second term in 1993 the advice has been to keep them apart, because the storage layout that makes one fast makes the other slow.
That advice still holds. What changed is the cost of keeping them apart. Change data capture now moves rows from a transactional database into an analytical engine in seconds, real-time OLAP engines serve sub-second queries to thousands of concurrent users, and "hybrid" engines promise to do both at once. So the question has shifted from "OLTP or OLAP" to "which of the patterns below fits this workload".
The split, briefly
OLTP engines (PostgreSQL, MySQL, Aurora) handle high volumes of short concurrent reads and writes against current state: insert an order, update an email, fetch a row by primary key, all under ACID and in well under a millisecond. OLAP engines (ClickHouse, Snowflake, BigQuery, Pinot, Druid) run aggregations over large volumes of history: sums, group-bys, percentiles, joins across billions of rows.
The split falls out of two opposing storage decisions. OLTP databases store data row by row, so every column of one record sits together on disk and a single read returns the whole entity. OLAP databases store data column by column, so a query touching three of fifty columns reads only those three, and long runs of similar values compress well. ClickHouse claims 5x to 10x compression over row-oriented databases for this reason (ClickHouse). Run an analytical scan on a row store and you drag every column off disk to use a fraction of them. Run point updates on a column store and you rewrite compressed column files to change one row. Each engine is pathologically bad at the other's job.
| Dimension | OLTP | OLAP |
|---|---|---|
| Primary workload | Short transactions: insert, update, point lookup | Aggregations, scans, group-by over large datasets |
| Schema | Normalized, many tables | Denormalized / star schema, wide tables |
| Storage layout | Row-oriented | Columnar |
| Query pattern | Read/write a few rows by key | Scan millions to billions of rows |
| Concurrency | Thousands of small concurrent transactions | Fewer, heavier queries (or many small ones for user-facing analytics) |
| Latency target | Sub-millisecond point lookups | Sub-second to seconds per scan |
| Example engines | PostgreSQL, MySQL, Aurora | ClickHouse, Snowflake, BigQuery, Pinot, Druid |
If your workload sits cleanly on one side, this table is most of the answer. Our database selection guide and the ClickHouse vs PostgreSQL comparison cover the cases where it does not.
Where the Line Breaks Down
The clean separation assumes analytics runs on yesterday's data and a handful of analysts run the queries. Plenty of workloads break both assumptions.
User-facing analytics is the clearest example. Embed a dashboard inside your product, a usage chart for every customer or a fraud score on every transaction, and you have an analytical query pattern (scan and aggregate) with an OLTP concurrency and latency profile: thousands of users, sub-second responses, fresh data. Operational dashboards on live event streams have the same shape. The question is analytical, but "how fresh" is now measured in seconds rather than nightly batch windows.
The freshness expectation is what pushed the industry toward the patterns below. Nobody wanted to merge OLTP and OLAP into one box for its own sake. They wanted to close the gap between a write landing in the transactional system and that write showing up in analytics. See also our guide to data warehouse architecture in 2026 for the OLAP side of the split.
The Pattern That Won: CDC From OLTP Into OLAP
In the platforms we build and audit, the architecture most teams land on keeps two purpose-built engines and connects them with change data capture (see also: modern data platform reference architecture). PostgreSQL or MySQL serves writes. ClickHouse, Snowflake, or BigQuery serves analytical reads. A CDC pipeline tails the transactional database's write-ahead log and streams every insert, update, and delete into the analytical store within seconds.
Debezium through Kafka is the open-source standard for this, and managed connectors have made it close to turnkey. ClickHouse's ClickPipes Postgres CDC connector reached general availability in May 2025 with replication latency as low as 10 seconds (ClickHouse blog); ClickHouse also claims an initial 1 TB snapshot in about 2 hours with 8 parallel threads (ClickHouse). On the cloud side, Amazon's Aurora zero-ETL integration replicates from Aurora into Redshift within seconds of the transactional write, generally available for Aurora MySQL since late 2023 and for Aurora PostgreSQL since October 2024 (AWS).
Each engine keeps doing the one thing it is good at. The OLTP database is not slowed by analytical scans, because those run on a separate system fed by the log. The OLAP database is never asked to do point updates, because it receives an ordered stream of changes it can merge in the background. A reference architecture for a typical SaaS backend:
Aurora / PostgreSQL (OLTP: writes, point reads)
│ write-ahead log
▼
CDC connector (Debezium / ClickPipes / PeerDB)
│ ordered change events, ~seconds latency
▼
ClickHouse / OpenSearch (OLAP: dashboards, search, aggregations)
The market agrees. In mid-2025 Databricks bought Neon (Databricks) for a reported $1 billion, and Snowflake bought Crunchy Data for about $250 million (CNBC, The Register). Both had already tried converging the two sides (Unistore, Lakebase). Both then went and bought a real Postgres.
The trade-off is operational. You now run two systems and a pipeline, and a row that just committed in Postgres may be a few seconds away from appearing in ClickHouse. For most analytics that delay is invisible; for a workflow that reads its own write and expects exact agreement, you design around it.
What actually bites in production is more specific than "eventual consistency". Schema changes on the source: a new column or a type change that the connector does not propagate, and the sink silently drops or nulls it. Deletes: the CDC stream carries a tombstone, and your ClickHouse table needs a ReplacingMergeTree (or CollapsingMergeTree) with a version and is_deleted column, plus FINAL or a deduplicating query pattern, or the deleted rows keep showing up in counts until a merge happens to run. Postgres REPLICA IDENTITY: without FULL, updates to TOAST-ed columns arrive without the old value and large text fields go missing downstream. Backfills: re-snapshotting a multi-terabyte table while the stream keeps flowing means reconciling snapshot rows with in-flight changes, and a replication slot that is left unconsumed during that window will fill the source's disk. None of this is exotic, but every one of them has paged someone at 3am.
If your "OLAP" side is really search and log analytics, the same shape applies with OpenSearch as the sink; the boundary cases are covered in using Elasticsearch or OpenSearch as your primary datastore.
HTAP and OLTP-Side Analytics: When One Engine Is Enough
The competing idea is to avoid the second system. Two flavors exist, and both have a real but narrow place.
The first is HTAP: one engine serving both workloads, usually by keeping a row store for writes and a columnar replica for analytics inside the same system. TiDB (TiKV replicating to TiFlash), SingleStore, and SAP HANA are the well-known examples. The promise is no pipeline and no lag. The reality is contention: the same CPU, memory, and I/O serve writes pulling one way and scans pulling the other. Snowflake documents its Unistore hybrid tables at roughly 16,000 operations per second (80/20 read/write mix) and 2 TB of hybrid-table data per database (Snowflake docs). That is enough for many applications and an order of magnitude below what a dedicated OLTP engine on comparable hardware sustains. HTAP earns its keep when the time between a transaction and its analytical visibility matters more than peak performance on either side, and when volumes stay modest.
The second flavor is OLTP-side analytics: keep Postgres, lean on replicas and extensions. A read replica absorbs reporting queries. TimescaleDB adds time-series compression and continuous aggregates. Citus shards for parallel scans. Columnar extensions narrow the scan-efficiency gap. For datasets in the tens of gigabytes and dashboards that tolerate a second or two, this is good enough, and a second engine would be premature. The point where Postgres stops keeping up (billions of rows, high-concurrency sub-second aggregations, scans no index can rescue) is where teams move to ClickHouse; we lay out that threshold in ClickHouse vs PostgreSQL and ClickHouse vs Snowflake.
Choosing: Match the Engine to the Workload
- Pure transactional: point reads and writes, strict consistency, modest reporting? A single OLTP database (PostgreSQL, Aurora). Do not add an analytical system you do not need.
- Pure analytical: large scans, batch loads, a handful of analysts? A dedicated OLAP warehouse (ClickHouse, Snowflake, BigQuery) on its own.
- Both, and the analytical side is large or high-concurrency? Two engines plus CDC. Postgres or Aurora for writes, ClickHouse or OpenSearch for analytics, fed by Debezium, ClickPipes, PeerDB, or Aurora zero-ETL.
- Both, but volumes are modest and you want one system? HTAP (TiDB, SingleStore) or Postgres with replicas and extensions. Accept the ceiling on each workload in exchange for one less moving part.
Two mistakes we see over and over. Running heavy OLAP queries on the production OLTP database starves transactions of I/O and locks; one analyst's group-by can spike P99 for paying users. Running OLTP-style point updates on a columnar engine is the mirror failure: column stores rewrite compressed files on update and have no business serving a write-heavy load. When a workload sits in the middle, split it by access pattern and connect the halves with a pipeline.
If you are weighing CDC pipelines, HTAP, or a Postgres-plus-ClickHouse split, BigData Boutique designs and operates these architectures in production. Get in touch and we will help you match the engine to the workload.
