Nothing gives an engineer a false sense of invincibility quite like running your first analytical query on ClickHouse during local development. After spending years watching standard relational databases choke on complex multi-month aggregations, seeing an analytical engine scan fifty million rows in forty milliseconds feels like discovering a cheat code for backend performance. Buoyed by that initial benchmark, I wired our live production event stream directly into a fresh ClickHouse cluster to power real-time merchant dashboards, point-of-sale telemetry, and live order flow analytics. Less than forty-eight hours after flipping the switch, the ingestion workers started throwing fatal exceptions, dashboard queries timed out, and the storage disk was thrashing at one hundred percent utilization.
How Thousand-Row Micro-Inserts Triggered the Too Many Parts Meltdown
My first catastrophic mistake was bringing an online transactional processing mindset into an online analytical processing world. In a standard PostgreSQL setup, firing an individual INSERT statement every time a customer completes a checkout or a driver updates a GPS coordinate is completely normal. Following that exact habit, I configured our Node.js event consumers to push individual JSON payloads directly into ClickHouse the millisecond an event arrived on the message bus.
What I failed to appreciate is how the ClickHouse MergeTree engine physically writes data to disk. Every single INSERT query creates a brand-new immutable directory on the filesystem called a data part, which background threads must later merge into larger, optimized segments. By firing hundreds of tiny single-row inserts per second across multiple worker nodes, we were creating thousands of microscopic directories faster than the background merge pool could ever compact them. Within hours, ClickHouse triggered its self-defense mechanism and rejected all incoming writes with the dreaded Too many parts (300) exception, effectively blinding our real-time monitoring pipeline right during peak afternoon traffic.
Putting High-Cardinality UUIDs First in the Sorting Key
Once I stopped the bleeding by pausing the ingestion workers, I inspected the system query logs and discovered a second architectural flaw hiding inside our table definition. When I originally wrote the schema migration, I defined the ORDER BY clause using the exact same instinct I use when indexing a relational table, placing the unique event_id UUID right at the front of the tuple.
In ClickHouse, the ORDER BY clause does not build a traditional row-level B-tree index; instead, it dictates the physical sort order of compressed columns on disk and builds a sparse primary index over blocks of eight thousand rows. Because every event_id was a completely random UUID with maximum cardinality, sorting the table by event_id first meant that data for any specific retail branch or date range was scattered randomly across every single granule on disk. Every time a merchant opened their dashboard to filter sales by branch_id and created_at, ClickHouse was forced to perform a full table scan across millions of rows because the sparse index could not skip a single data block. Furthermore, compression ratios were terrible because adjacent rows shared no repeating prefix values.
Rebuilding the Pipeline With Async Inserts and Materialized Views
Fixing the outage required redesigning both how data entered the cluster and how it was physically organized on disk. First, I eliminated the single-row insert storm by buffering events in memory through our Kafka stream and enabling ClickHouse asynchronous inserts as a safety net so the database itself would batch incoming payloads in memory before flushing a single combined part to disk. Next, I rewrote the core table schema to order data strictly from lowest cardinality to highest cardinality, starting with branch_id, followed by the timestamp, and leaving the unique identifier at the very end.
Pairing that reordered ReplacingMergeTree table with a SummingMergeTree Materialized View transformed our system performance overnight. Because branch_id and category now sit at the front of the sorting key alongside LowCardinality dictionary encoding, identical strings sit right next to each other on disk, shrinking our storage footprint by nearly seventy percent through ZSTD compression. More importantly, dashboard queries no longer scan the raw event stream at all. As batched chunks of three thousand events land in pos_events_v2, the materialized view automatically pre-aggregates hourly revenue totals on the fly during the insert phase, bringing dashboard response times down to less than eight milliseconds regardless of how many million rows accumulate in the raw table.
Respecting the Physics of Columnar Storage
Breaking our ClickHouse deployment in production was a humbling reminder that high-performance infrastructure never forgives lazy abstractions. You cannot treat a columnar analytical engine like a drop-in replacement for PostgreSQL and expect it to survive real-world concurrency. Once you respect how MergeTree parts are flushed to disk, batch your ingestion streams rigorously, and design your sorting keys from low cardinality to high cardinality, ClickHouse easily lives up to its reputation as an unstoppable analytical powerhouse.