OLTP vs OLAP: 8 Costly Mistakes Data Architects Make in Real-Time Analytics, and What ClickHouse 26.10 Changes

OLTP vs OLAP is the distinction most real-time analytics programmes get wrong, and the cost rarely shows up in the first quarter. It shows up eighteen months later as a cluster that needs three times the hardware it should, dashboards that time out at month end, and an upgrade nobody wants to own. ClickHouse 26.10, released on 22 October 2026, ships features that soften some of these failures. None of them repairs an architecture designed with transactional instincts.

This paper is written for the people who sign off on data platforms: chief data officers, enterprise architects and the principal engineers who will be paged when the design decisions turn out wrong. We cover what ClickHouse 26.10 changes, then the eight OLTP vs OLAP mistakes we see most often in architecture reviews, each with the mechanism, the evidence and the correction.

What ClickHouse 26.10 changes for data architects

ClickHouse 26.10 is a monthly stable release, not an LTS. On either side of the OLTP vs OLAP line, our standing advice for production estates is to run the current LTS line (26.8 at the time of writing, with 26.3 still supported) and to use monthly releases such as 26.10 for evaluation and for workloads that need a specific feature. Every item below is taken from the official ClickHouse 2026 changelog; we checked it on 5 November 2026.

Two of these deserve attention at board level. The workload scheduler is the first native answer to the question every multi-tenant analytics platform eventually asks: how do we stop one team’s ad-hoc query from starving a customer-facing API? The analyzer removal is the reverse: a hard compatibility break that turns a deferred technical debt into an upgrade blocker. We cover both in the mistakes below.

OLTP vs OLAP: the mechanism behind every mistake

An OLTP engine stores whole rows together in pages and indexes them with a B-tree. Finding order 918273 is a walk of three or four pages. Changing its status is an in-place write protected by row locks and MVCC. That is the right design for a system of record.

ClickHouse stores each column in its own file, sorted by the table’s ORDER BY key, and keeps a sparse primary index with one mark per granule (8,192 rows by default). It cannot find a single row cheaply unless the sort key leads to it. It can read three columns out of forty across a billion rows very quickly. Data is written as immutable parts that are merged in the background. Updates are expensive rewrites, not in-place edits.

Figure 1. OLTP vs OLAP read paths. The OLTP engine finds one row through a B-tree. ClickHouse skips granules with a sparse index and reads only the referenced columns.

Every mistake that follows is a case of carrying the left-hand mental model into the right-hand engine. That is the OLTP vs OLAP problem in one sentence.

Mistake 1: treating ORDER BY like an OLTP primary key

The most expensive OLTP vs OLAP error is a sort key that leads with a unique surrogate identifier, usually a UUID or an auto-increment id, because “every table needs a primary key”. In ClickHouse the ORDER BY clause decides physical row order and which granules the primary index can skip. A unique id first means no analytical predicate can use the index.

We measured this rather than asserting it. Two tables, identical 20 million rows of synthetic event data across 2,000 tenants and roughly 58 days, differing only in sort key:

-- OLTP habit: surrogate key first
CREATE TABLE lab.events_oltp_style
(
    event_id    UUID,
    tenant_id   UInt32,
    event_time  DateTime,
    event_type  LowCardinality(String),
    amount      Decimal(18, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY event_id
SETTINGS index_granularity = 8192;

-- OLAP design: sort key follows the dashboard predicates
CREATE TABLE lab.events_olap_style
(
    event_id    UUID,
    tenant_id   UInt32,
    event_time  DateTime,
    event_type  LowCardinality(String),
    amount      Decimal(18, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_type, event_time)
SETTINGS index_granularity = 8192;

-- Data generator (identical rows loaded into both tables)
INSERT INTO lab.events_oltp_style
SELECT
    generateUUIDv4(number)                                          AS event_id,
    toUInt32(number % 2000)                                         AS tenant_id,
    toDateTime('2026-07-01 00:00:00') + intDiv(number, 4)           AS event_time,
    ['view', 'click', 'cart', 'purchase', 'refund'][1 + (cityHash64(number) % 5)] AS event_type,
    toDecimal64((cityHash64(number, 7) % 100000) / 100, 2)          AS amount
FROM numbers(20000000);

INSERT INTO lab.events_olap_style
SELECT *
FROM lab.events_oltp_style;

The query is a typical tenant dashboard: hourly purchases and revenue for one tenant over one week. We ran it with EXPLAIN indexes = 1 against both tables.

EXPLAIN indexes = 1
SELECT
    toStartOfHour(event_time) AS hour_bucket,
    count()                   AS purchases,
    sum(amount)               AS revenue
FROM lab.events_olap_style
WHERE tenant_id = 42
  AND event_type = 'purchase'
  AND event_time >= '2026-08-01 00:00:00'
  AND event_time <  '2026-08-08 00:00:00'
GROUP BY hour_bucket
ORDER BY hour_bucket;

Index section of the plan for the OLTP-habit table (output trimmed, marked with …):

ReadFromMergeTree (lab.events_oltp_style)
  Parts: 1 | Granules: 1134
  Indexes:
    Min-Max
      Keys: event_time
      Parts: 1/2
      Granules: 1134/2442
    Partition
      Keys: toYYYYMM(event_time)
      Parts: 1/1
      Granules: 1134/1134
    PrimaryKey
      Condition: true
      Parts: 1/1
      Granules: 1134/1134
...

And for the table designed for the OLAP side of the OLTP vs OLAP divide:

ReadFromMergeTree (lab.events_olap_style)
  Parts: 1 | Granules: 2
  Indexes:
    Min-Max
      Keys: event_time
      Parts: 1/2
      Granules: 1134/2442
    Partition
      Keys: toYYYYMM(event_time)
      Parts: 1/1
      Granules: 1134/1134
    PrimaryKey
      Keys: tenant_id, event_type, event_time
      Condition: and((event_time in (-Inf, 1786147199]), and((event_time in [1785542400, +Inf)),
                 and((event_type in ['purchase', 'purchase']), (tenant_id in [42, 42]))))
      Parts: 1/1
      Granules: 2/1134
...

Partition pruning did its job on both tables, removing the July partition. Only the second table’s primary key could act on the predicate, cutting 1,134 granules to 2. Over seven warm runs the median latency was 107.9 ms against 8.6 ms.

Figure 2. The OLTP vs OLAP sort key effect, measured. Lab setup: chDB 4.4.0 embedding ClickHouse 26.9.2.1, 2 vCPU, 7 GB RAM, warm cache, query cache disabled, median of 7 runs.

Read this with care. It is a small lab on one node, not a production benchmark, and the latency gap on your hardware will differ. The granule ratio is the durable lesson, and it grows with table size. Order the key by the columns your queries filter on, lowest cardinality first, time last. If you also need lookups by event_id, add a bloom_filter skip index or a projection rather than surrendering the sort key.

The OLTP vs OLAP rule: a primary key identifies a row; a sort key decides what the engine can skip. Design the second for queries, not for identity.

Mistake 2: row-at-a-time writes from the application tier

OLTP applications commit one business event per transaction, which is correct on the OLTP side of the OLTP vs OLAP divide, and developers reasonably carry the same pattern into ClickHouse. Each INSERT creates at least one new part on disk. Background merges must then combine those parts, and when inserts outpace merges, ClickHouse throws TOO_MANY_PARTS and rejects writes.

In the same lab, with merges paused to expose the raw effect, 500 single-row INSERT statements produced 500 active parts. One INSERT of the same 500 rows produced one part. Check your own estate with:

SELECT
    database,
    table,
    count()                        AS active_parts,
    max(modification_time)         AS last_part_written,
    round(avg(rows))               AS avg_rows_per_part
FROM system.parts
WHERE active
GROUP BY
    database,
    table
ORDER BY active_parts DESC
LIMIT 20;

An avg_rows_per_part in the tens or hundreds on a hot table is the signature of OLTP-style ingest. The correction is architectural: batch in the transport layer (thousands to hundreds of thousands of rows per INSERT), or enable server-side batching where clients cannot change.

-- Server-side batching for clients that cannot batch.
-- Test in staging first: wait_for_async_insert = 1 keeps acknowledgement durable.
ALTER USER ingest_service
    SETTINGS async_insert = 1,
             wait_for_async_insert = 1,
             async_insert_busy_timeout_max_ms = 1000;

The OLTP vs OLAP rule: OLTP commits one event per transaction; ClickHouse wants one batch per INSERT. Put the batching boundary in the transport layer.

Mistake 3: UPDATE and DELETE as a daily workflow

In the OLTP world, a status change is an UPDATE, and this is where the OLTP vs OLAP difference becomes an operational one. In ClickHouse, a classic ALTER TABLE … UPDATE is a mutation that rewrites every affected part. Run it hourly against a large table and the mutation queue becomes the bottleneck for the whole cluster. Lightweight updates and deletes have improved this considerably in recent releases, but they still are not a substitute for a write model designed for append.

Model state changes as new versions and let the engine collapse them:

CREATE TABLE sales.order_state
(
    order_id     UInt64,
    tenant_id    UInt32,
    order_status LowCardinality(String),
    amount       Decimal(18, 2),
    updated_at   DateTime64(3),
    is_deleted   UInt8 DEFAULT 0
)
ENGINE = ReplicatedReplacingMergeTree(
    '/clickhouse/tables/{shard}/sales/order_state',
    '{replica}',
    updated_at,
    is_deleted
)
PARTITION BY toYYYYMM(updated_at)
ORDER BY (tenant_id, order_id)
SETTINGS index_granularity = 8192;

-- Read the latest version per key
SELECT
    order_status,
    count()     AS orders,
    sum(amount) AS gross_amount
FROM sales.order_state FINAL
WHERE tenant_id = 42
GROUP BY order_status;

Watch system.mutations for anything with is_done = 0 older than an hour. That is the clearest OLTP vs OLAP smell in an operational review. Note the 26.10 change here as well: SELECT … FINAL on SummingMergeTree no longer removes rows whose summed columns are all zero, so dashboards built on that behaviour need an explicit filter.

The OLTP vs OLAP rule: OLTP changes rows in place; OLAP appends versions and resolves them at merge or read time.

Mistake 4: importing the third-normal-form schema wholesale

In OLTP vs OLAP terms, normalisation protects write integrity, which is exactly what a system of record needs. An analytics engine pays for it with joins on every query. ClickHouse joins have improved release after release, and 26.10 enables runtime-filter index analysis and PK-range sharding for joins by default. These reduce the cost of a join. They do not remove the cost of six.

The pattern we recommend is a wide fact table populated at ingest time, with small, slowly changing reference data served from dictionaries:

CREATE DICTIONARY ref.product_dict
(
    product_id   UInt64,
    category     String,
    brand        String
)
PRIMARY KEY product_id
SOURCE(POSTGRESQL(
    host 'pg-primary.internal'
    port 5432
    user '${PG_USER}'
    password '${PG_PASSWORD}'
    db 'catalog'
    table 'products'
))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 600);

SELECT
    dictGet('ref.product_dict', 'category', product_id) AS category,
    sum(amount)                                         AS revenue
FROM sales.events_wide
WHERE tenant_id = 42
  AND event_time >= now() - INTERVAL 1 DAY
GROUP BY category
ORDER BY revenue DESC;

Before denormalising, look at system.query_log for the joins that actually run. The tables joined in the top fifty queries by read_bytes are the ones worth flattening.

The OLTP vs OLAP rule: normalise the system of record, denormalise the analytical copy, and serve reference data from dictionaries.

Mistake 5: partitioning like an OLTP shard map

Architects coming from transactional sharding often partition by tenant, by day, or by both, a quieter OLTP vs OLAP error than the sort key but just as costly. In ClickHouse a partition is a unit of data management (TTL, drop, move to cold storage), not an index. Every partition multiplies the number of parts, and merges never cross partitions. A table partitioned by tenant_id with 2,000 tenants carries 2,000 times the part overhead and loses most of the benefit of merging.

Keep partitions coarse, usually monthly, occasionally daily for very high-volume tables with short retention, and let the sort key do the pruning. Audit with:

SELECT
    database,
    table,
    uniqExact(partition)  AS partitions,
    count()               AS active_parts,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active
GROUP BY
    database,
    table
HAVING partitions > 100
ORDER BY partitions DESC;

The OLTP vs OLAP rule: partitions manage data lifecycle; the sort key does the pruning.

Mistake 6: sizing concurrency like a transaction processing system

OLTP capacity planning thinks in connections and queries per second, each query cheap and short. An analytical query uses many cores and a large share of memory for a few hundred milliseconds or seconds. Three hundred concurrent dashboard tiles opened at 9 a.m. is not three hundred small queries; it is three hundred full-width scans competing for the same cores. This OLTP vs OLAP sizing error is behind most “ClickHouse is slow at peak” escalations we receive.

ClickHouse 26.10 gives architects proper tools here. Workloads can now choose a scheduler and carry their own spill thresholds:

-- ClickHouse 26.10+. Validate syntax against your build and test in staging.
CREATE RESOURCE cpu_pool (MASTER THREAD, WORKER THREAD);

CREATE WORKLOAD all;

CREATE WORKLOAD customer_api IN all
SETTINGS
    priority = 0,
    scheduler = 'fair',
    max_concurrent_threads = 32;

CREATE WORKLOAD adhoc_analysts IN all
SETTINGS
    priority = 1,
    scheduler = 'fair',
    max_concurrent_threads = 8;

-- Route a role to its workload
ALTER SETTINGS PROFILE analyst_profile
    SETTINGS workload = 'adhoc_analysts';

-- Cap ingest volume per user with the new written_rows quota resource
CREATE QUOTA ingest_cap
    FOR INTERVAL 1 hour MAX written_rows = 500000000
    TO ingest_service;

Measure before and after with ProfileEvents in system.query_log, specifically the time queries spend waiting for CPU slots, alongside p95 and p99 query_duration_ms per workload. Concurrency limits move latency around; they do not create capacity, which is the OLTP vs OLAP sizing lesson in its simplest form.

The OLTP vs OLAP rule: OLTP sizes for connections; OLAP sizes for cores, memory and isolation between workloads.

Mistake 7: querying the OLTP database for real-time analytics

The cheapest-looking option, pointing the BI tool at a PostgreSQL or MySQL read replica, is usually the most expensive one. Analytical scans evict the buffer pool, long transactions hold back vacuum on PostgreSQL, and replica lag becomes a business metric. The OLTP vs OLAP boundary should be a physical one.

Figure 3. A reference real-time analytics architecture with a hard OLTP vs OLAP boundary: log-based CDC, a batching transport, and ClickHouse doing the aggregation work at insert time.

On the OLAP side of the OLTP vs OLAP boundary, materialized views move aggregation from read time to write time, which is where an analytical engine wants it:

CREATE TABLE sales.revenue_hourly
(
    tenant_id    UInt32,
    hour_bucket  DateTime,
    purchases    AggregateFunction(count),
    revenue      AggregateFunction(sum, Decimal(18, 2))
)
ENGINE = ReplicatedAggregatingMergeTree(
    '/clickhouse/tables/{shard}/sales/revenue_hourly',
    '{replica}'
)
PARTITION BY toYYYYMM(hour_bucket)
ORDER BY (tenant_id, hour_bucket);

CREATE MATERIALIZED VIEW sales.revenue_hourly_mv
TO sales.revenue_hourly
AS
SELECT
    tenant_id,
    toStartOfHour(event_time) AS hour_bucket,
    countState()              AS purchases,
    sumState(amount)          AS revenue
FROM sales.events_wide
WHERE event_type = 'purchase'
GROUP BY
    tenant_id,
    hour_bucket;

The OLTP vs OLAP rule: never let analytical scans share a buffer pool with the system of record.

Mistake 8: upgrading an analytics cluster like an OLTP patch

OLTP teams are used to minor versions that change nothing observable, and that expectation does not survive the OLTP vs OLAP crossing. ClickHouse monthly releases can and do change semantics, and 26.10 is a clear example. The old query analysis path is gone, arrayCount changes its return type, accurateCast from String to a Nullable target now throws instead of returning NULL, and system.metric_log changes shape. On a mixed-version cluster mid-upgrade, these differences surface as wrong results, not just errors.

Run a pre-upgrade inventory against the real query history before any rolling upgrade to 26.10:

-- 1. Settings that 26.10 makes obsolete, still set by users or profiles
SELECT
    name,
    value
FROM system.settings
WHERE changed
  AND name IN (
      'enable_optimize_predicate_expression',
      'query_plan_read_in_order',
      'query_plan_optimize_prewhere',
      'query_plan_aggregation_in_order',
      'use_analyzer_for_mutations',
      's3_max_connections'
  );

-- 2. Queries in the last 14 days that rely on changed semantics
SELECT
    normalized_query_hash,
    any(query)  AS sample_query,
    count()     AS executions
FROM system.query_log
WHERE event_date >= today() - 14
  AND type = 'QueryFinish'
  AND (
      positionCaseInsensitive(query, 'arrayCount(') > 0
      OR positionCaseInsensitive(query, 'accurateCast(') > 0
      OR positionCaseInsensitive(query, 'metric_log') > 0
  )
GROUP BY normalized_query_hash
ORDER BY executions DESC
LIMIT 50;

Replay that query set against a 26.10 staging replica and compare result checksums before touching production. Keep a tested rollback path: the previous binary staged on every node, and no use of new on-disk formats (the PCO codec, for example) until the upgrade is signed off. Our ClickHouse 26.8 LTS release analysis covers the LTS line most production estates should standardise on.

The OLTP vs OLAP rule: treat a monthly ClickHouse release as a semantic change, rehearse it on staging, and standardise production on LTS.

An OLTP vs OLAP checklist for architecture review boards

Review boards can test any real-time analytics design against the OLTP vs OLAP boundary with six questions. Each has a measurable answer in the system tables.

Where this guidance stops

ClickHouse is not a system of record, and nothing in this OLTP vs OLAP paper argues otherwise. If the workload is dominated by single-row reads and writes with strict transactional guarantees, PostgreSQL or MySQL remain the right engines, and we say so in our own guidance on when not to use ClickHouse. The lab figures above come from one small node and a synthetic dataset; we publish them to show the mechanism, not to predict your latency. The 26.10 workload syntax is new, so validate it against your exact build.

Treat every recommendation here as a starting hypothesis to test on your own workload, in staging, with a verified backup and a rehearsed restore behind it.