Every insert writes a part

The table had 187 columns. Every dashboard built on it read four of them, and the bill was computed on all 187.

BigQuery charges $6.25 per TiB scanned on demand, after the first TiB each month comes free. Snowflake bills warehouse time per second, with a 60-second minimum every time a warehouse starts. Neither is a bad price. They are prices designed for a workload that looks nothing like a factory floor, where a dashboard wakes up every sixty seconds, across three shifts, forever.

Manufacturing telemetry has a shape, and the shape decides the storage engine. Most arguments about columnar stores versus warehouses turn out to be arguments about whether your data has that shape, conducted by people who never wrote the shape down.

The shape of a telemetry row

I spent an evening in February with a servo drive parameter manual from a Shenzhen manufacturer, in Chinese, because nobody had translated it and nobody was going to. Several hundred parameters per drive. Position error, bus voltage, phase current, encoder feedback, IGBT temperature, alarm codes with their own history buffer. Maybe forty of those are worth sampling continuously. Multiply by six axes, by thirty machines, by four lines, by however many plants, and you have your tag count. That number only ever climbs, because commissioning a machine adds tags and decommissioning one does not delete its history.

Five properties follow. Cardinality is high and growing. Writes are almost pure appends: a reading, once written, is correct forever. Data arrives late, sometimes by hours. Rows are wide, or they are narrow and there are far more of them. And the queries are, with real monotony, aggregations bounded in time: a mean, a p95, an alarm count, over an interval somebody picked from a date control.

That fourth property splits into two schools and most plants run both. The narrow shape stores one row per reading: timestamp, tag id, value. The wide shape gives each tag its own column and writes one row per sampling instant per machine. Narrow produces enormous row counts. Wide produces the 187-column table. The historian speaks one, the MES speaks the other and somebody has to reconcile them at two in the morning.

The volumes are not hypothetical. China installed 295,000 industrial robots in 2024, 54 percent of world installations, pushing operational stock past two million units, with electrical and electronics alone taking 83,000. Every one of those has a controller with a register map, and sooner or later somebody wants the registers in a database.

The store sorted them, compressed them, folded them into the hourly aggregate and served them to the shift supervisor, cheerfully, in the wrong hour.

Row storage prices this workload badly

A correction before the argument, because this audience will check. BigQuery is not a row store: it keeps table data in columnar format, each column stored separately, in Capacitor. Snowflake's micro-partitions are organised in columnar fashion too. The genuinely row-oriented systems in a plant are the ones already installed: SQL Server behind the MES, Oracle under the ERP, Postgres under whatever the integrator wrote in 2018 and walked away from.

So layout is not always the problem. The pricing model and the ingest model wrapped around the layout are. On-demand BigQuery bills bytes scanned, which is generous when queries are selective and unkind when a dashboard is not. Snowflake bills the warehouse being awake, so a four-second query on a cold warehouse bills sixty seconds, and twelve dashboards refreshing every minute keep a cluster running all day to answer questions that each touch a few gigabytes.

A columnar store tuned for this reads only the columns the query names, then skips the parts and granules that cannot contain matching rows. Two separate savings, and they multiply. Column pruning turns 187 columns into four. Key pruning turns three years of history into the handful of granules covering the shift you asked about.

Partitioning and the ordering key carry the design

ClickHouse's documentation is blunt about partitioning: in most cases you do not need a partition key at all, and in most other cases nothing more granular than by month, with daily partitioning reserved for observability workloads. Telemetry sits close enough to observability that daily is usually right for hot data. What you must not do is partition by machine or by site. Every distinct partition value an insert touches creates a new data part, so a partition key with a thousand values turns one insert into a thousand parts.

Site and machine belong in the ORDER BY instead. Four or five columns is usually enough, arranged so that the ones excluding the most rows come first and so that adjacent rows resemble each other, which is what the compression codecs feed on. (site_id, machine_id, tag_id, ts) is a sane default. Time goes last. A query for one tag across one shift then reads a contiguous run of disk rather than a scatter.

The primary index is sparse: one mark per index_granularity rows, 8192 by default. Fifty billion rows produce about six million marks, small enough to keep resident in memory. That sparseness is why the index costs almost nothing to maintain on append, and why it is useless for point lookups. It prunes. It does not locate.

Late data falls out of this design for free. An insert carrying yesterday's readings creates a part inside yesterday's partition and gets merged on the normal schedule. No window, no watermark, no reindex. This is the sharpest practical difference from a segment-oriented store like Druid, where a late row landing outside the auto-compaction offset stops being an insert and becomes an operational task with a ticket attached.

Ingest is a merge budget, not a throughput number

The documented advice is at least 1,000 rows per insert, ideally between 10,000 and 100,000. Teams read that as a performance tip. It is a survival constraint.

Every insert writes a part. Background merges combine parts into larger ones. When active parts in a partition pass parts_to_delay_insert, 1000 by default, the server begins artificially delaying inserts to let merges catch up. Past parts_to_throw_insert, 3000, it rejects them outright with Too many parts, which in my experience arrives at 02:40 on a Sunday and looks exactly like the pipeline dying.

Do the arithmetic before building the collector, not after. Four hundred edge gateways each writing once per second into a daily partition is 400 parts per second against a merge process that will not keep up on hardware anyone will approve. A bigger server does not fix this. It postpones it by about a week.

Two fixes, and you usually want both. Put a queue in front, one topic per site, and have the consumer accumulate for thirty to sixty seconds before writing. For clients that genuinely cannot batch, enable async_insert and let the server buffer: it flushes when the buffer reaches async_insert_max_data_size, 100 MiB by default, or when async_insert_busy_timeout_ms elapses, 200 ms self-hosted and 1000 ms on ClickHouse Cloud. Leave wait_for_async_insert on unless you are comfortable with a client that believes a write succeeded before it was durable.

Rollups computed at insert time

A ClickHouse materialised view is not a cached query. It is a trigger that runs over each block of rows as it is inserted into the source table and writes the result somewhere else. The rollup gets paid for by the writer, once, instead of by every dashboard asking the same question for the next three years.

The telemetry pattern is a chain. Raw rows land in a MergeTree table with a TTL of ninety days. One view aggregates into one-minute buckets in an AggregatingMergeTree, a second rolls minutes into hours, a third into shifts. Each level stores partial aggregate states rather than finished numbers, so an average of averages stays correct and a quantile stays a quantile.

Two things trip people up. The GROUP BY of the view has to line up with the ORDER BY of its target table. And the view fires only on inserts into the leftmost table of its query, so a dimension table joined on the right can change all day without triggering anything at all. Where a rollup cannot be expressed incrementally, refreshable materialised views re-run the whole query on a schedule instead. Slower, simpler and honest about what it is doing.

Where the column store breaks

Updates are the first wall, and the documentation is precise about why: for MergeTree tables, mutations execute by rewriting whole data parts, as asynchronous background processes. A QA team reclassifying a scrap code across six months of history is not a small edit. It is a rewrite of that column in every part it touches, competing with live ingest for the same disk and the same cores.

Lightweight updates, still marked beta, soften this by writing patch parts that carry only the changed columns. The documentation is explicit about the boundaries: designed for small amounts of rows, up to about 10 percent of the table, and unable to touch any column used in the primary or partition key. It also warns that small updates which are too frequent lead straight back to Too many parts. Better than a full mutation. Not a transactional update.

Deletes obey the same physics. A lightweight delete marks rows as gone and lets the merge process reclaim the space later, on the merge schedule rather than yours. For retention that is fine, and TTL handles it without being asked. For an erasure request with a legal deadline attached, plan the operation instead of typing it into a console at 5pm on a Friday.

Joins are the second wall and the failure mode is memory, not time. The hash join streams the entire right-hand table into memory and builds the hash table on a single thread; parallel hash builds several concurrently and wants more memory again; grace hash spills to disk and pays for it in latency. Join a fifty-billion-row fact table to a forty-million-row equipment history and you will meet the limit. The idiomatic answer is to stop joining: load small and medium dimensions as dictionaries that live in memory as key-value structures, and denormalise the rest at write time. Denormalisation has a price and it is paid in schema migrations, one for every field the plant adds.

The third wall is the first two seen from another angle. Everything expensive here is expensive because the part is the unit of everything: of writes, of merges, of deletes, of updates. There is no transaction to hide behind either. Deduplication is eventual, through ReplacingMergeTree and a FINAL modifier you will come to respect, or through an idempotency key you enforce upstream where it belongs.

The warehouse you already have is probably good enough

Here is what benchmarks never price. A ClickHouse cluster is a thing somebody has to run. Keeper quorum, replica lag, merge backlogs, disk headroom, because a merge needs free space to write the merged part before it can drop the originals. Schema changes have to land consistently across replicas. The 26.x line ships a minor release every few weeks, with an LTS designated twice a year, in March and August, supported for twelve months, which means one of the two LTS lines expires every six months and somebody has to own that calendar. None of it shows up in a query benchmark. All of it shows up in an on-call rota.

If your telemetry runs to a few billion rows and you already operate Postgres, TimescaleDB is very likely the correct answer and moving would be vanity. Hypercore, its hybrid row-columnar engine, moves chunks from rowstore into columnstore automatically. Continuous aggregates give you the same rollup chain. Recent releases pushed more of the analytical path into vectorised columnar execution, including time_bucket inside grouping expressions. You keep foreign keys, transactions and every tool the team already knows. That covers a very large share of factories and nobody gives conference talks about it.

Druid earns its keep when many people need sub-second answers at the same time on the same data. Its segment model is the trade, and late data is where you pay for it. Snowflake and BigQuery earn theirs when the data team is two people already fluent in them, because a larger invoice is cheaper than a hire you cannot make. That calculation is not embarrassing. It is usually correct.

The switch pays when three conditions hold together: ingest that a tuned Postgres box cannot absorb, retention measured in years rather than months and query patterns that are dense scans across one family of tags rather than selective lookups. Below roughly ten terabytes of raw telemetry I would not bother, and that threshold is my judgement rather than anyone's benchmark. Above it the arithmetic turns quickly, because the columnar store's cost tracks the bytes you actually read while the warehouse's tracks the bytes you happen to have stored beside them.

Bursts from a plant outside Abeokuta

The grid in Nigeria fails often enough that a plant treats genset transfer as the normal case rather than the exception, and the network link is usually the first thing to drop and the last thing to come back.

A plant I visited outside Abeokuta had a pattern I have never once seen in Guangdong. The edge collector buffered for hours, then emptied its backlog in minutes when the link returned. Nine hours of readings arriving inside four. In ClickHouse that is a large insert into an older partition and the merges absorb it without anybody noticing. In a segment store it is a compaction conflict. In a streaming pipeline with a watermark it is silently discarded, which is the worst of the three, because nothing alerts and the dashboard still looks fine.

The engine handled it. What the engine could not handle was that the PLC clock on line two had drifted about forty minutes across the outage, so the buffered rows carried timestamps for an interval that never happened. The store sorted them, compressed them, folded them into the hourly aggregate and served them to the shift supervisor, cheerfully, in the wrong hour. Picking the right storage engine buys you the ability to ask three years of history a question and get an answer inside a second. It buys you nothing whatsoever about whether the answer is true.

Sources

ClickHouse docs: selecting an insert strategy and bulk inserts : https://clickhouse.com/docs/optimize/bulk-inserts

ClickHouse docs: asynchronous inserts (async_insert) : https://clickhouse.com/docs/optimize/asynchronous-inserts

ClickHouse docs: MergeTree table settings : https://clickhouse.com/docs/operations/settings/merge-tree-settings

ClickHouse docs: the lightweight UPDATE statement : https://clickhouse.com/docs/reference/statements/update

ClickHouse docs: custom partitioning key : https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/custom-partitioning-key

Google Cloud: BigQuery pricing : https://cloud.google.com/bigquery/pricing

Snowflake docs: virtual warehouses overview : https://docs.snowflake.com/en/user-guide/warehouses-overview

International Federation of Robotics: World Robotics 2025 : https://ifr.org/ifr-press-releases/news/global-robot-demand-in-factories-doubles-over-10-years