HomeFind your caseRunning ClickHouse in production keeps hurting
Case · Enterprise
A common situation · not a client storyRunning ClickHouse in production keeps hurting.
Teams that adopted ClickHouse for fast analytics and now find schema changes, slow queries and incidents harder than expected.
What you're probably seeing
If this sounds familiar, you're in the right place.
- Schema changes are applied by hand, and can't be undone in a transaction.
- Nobody can say which migrations ran on which cluster.
- When queries slow down, there's no quick way to see what is running.
- Incidents are solved again from scratch each time, because nothing was written down.
- Memory errors or too many parts appear as data grows.
The usual ways out
What teams usually try. Each works somewhere. None works everywhere.
| The way | Works when | The catch |
|---|---|---|
| Move to a managed ClickHouse service | You'd rather not run the cluster at all | Cost, and some operations still land on you |
| Apply migrations by hand, carefully | Changes are rare | No record, no safe re-run, no way back |
| Use a general migration tool | It supports ClickHouse well | Many assume transactions ClickHouse doesn't have |
| Add monitoring dashboards | You know which metrics matter | Shows that something is slow, not what is running |
| Write runbooks after each incident | The same incidents repeat | Only works if they stay next to the tools |
| Rethink the table design | Parts, merges or memory trouble keep coming back | A migration project, so test it on a copy first |
What other companies did. Their published results, not ours.
We read each company's own write-up and asked one question: would our diagnostic's tests have led to the same call? We judge the approach, not the result.
| Company | What they did | What they published | Source |
|---|---|---|---|
| Trigger.devTask-execution platform. ClickHouse stores OpenTelemetry traces and spans for thousands of active long-running tasks. The deployment model (self-hosted or Cloud) is not stated. | An events table was partitioned by span start time. End events arriving weeks later landed as tiny parts in old partitions until inserts hit the 3,000-part limit. They added replicas, cut debug logging, dropped materialized views that blocked merges, and created a new table partitioned by insert time. | About 275,000 inserts failed across three windows between 28 Nov and 1 Dec 2025 (about 6 hours, 30 minutes and 19 hours). Some trace data was missing and some may be permanently lost. Task execution was not affected.They noted: Do not partition by a field that can arrive late. Monitor active part counts, merges and part sizes. Their testing missed long-running tasks, high-cardinality keys and backfills. | OTel incident post-mortemNov 2025 |
| CloudflareGlobal network. A ClickHouse query builds the Bot Management feature file every five minutes, and the file is pushed to every proxy. | A ClickHouse permission change made tables in a second database visible. A metadata query that did not filter by database then returned duplicate rows and doubled the feature file, past the proxy's 200-feature limit. They rolled back to a known-good file, stopped automatic file deployment and restarted the proxy. | Core CDN and security services returned 5xx errors from about 11:20 UTC. The main impact was resolved at 14:30 and all services were restored at 17:06. Cloudflare called it its worst outage since 2019.They noted: The query assumed only one database would appear. The change reached ClickHouse nodes gradually, so failures came and went and an attack was suspected at first. Their follow-ups focus on treating generated config files like user input and adding kill switches. | Cloudflare outage on November 18, 2025Nov 2025 |
| PostHogProduct analytics on ClickHouse, with monthly releases shipped to self-hosted users. Some changes, such as a new sorting key, mean reinserting every row, which can take weeks on large tables. | Built 'async migrations': versioned migrations that run in the background. They have prechecks such as disk space, health checks that can stop a run, progress tracking, pause and resume, rollback, and an admin UI. | Not stated.They noted: Counting executed operations is often a poor progress measure. Safety still depends on review and tests. Users must start migrations themselves within a version window. | Enabling zero downtime data migrations for self-hosted usersMar 2022 |
| Azur GamesMobile game publisher with 8 billion+ installs. About 120 TB of telemetry sat on 20+ self-run bare-metal ClickHouse servers with reduced redundancy. Running it took two Airflow engineers and one DevOps engineer, and the software was 2+ years old. | Moved to ClickHouse Cloud on AWS over about three months, using full duplication, verification and an atomic switchover. Moved side databases to RDS. | One administrator has 60% of their time freed, and an ETL engineer saves 40%. Overall cost is 'comparable' to the self-run setup.They noted: Overall cost stayed 'comparable' to the self-run setup, and the team says it must be more mindful of resource use in the cloud. The time savings are self-reported. | Azur Games migrates all game analytics data to ClickHouse Cloud on AWSJul 2024 · vendor story |
Our read
Reasonable call. One thing showed up late. Teams fixed what hurt, but the parts and schema trouble was found in production, after the design or change was already live.
Trigger.dev's own lesson is that its testing missed long-running tasks and backfills. Its late-arriving partition key only showed up when parts hit the 3,000 limit. At Cloudflare, an access change in ClickHouse altered what a metadata query returned, and the query's single-database assumption had not been checked. PostHog built migrations that are tracked, resumable and reversible, which is what hand-applied changes lack (see the table above). Azur Games moved to a managed service for reasons it stated (redundancy, upgrades, staff time) at similar cost, which fits the managed option in the table above.
What we would test first: Measure parts, merges and memory under your real load, including late and backfilled data.
What the diagnostic would test
The tests that rule the wrong ways out.
On your own systems and data, read-only first. The number of tests sets the diagnostic's price, quoted before it starts.
- 1
Every schema change in the last quarter, and how it was applied.
- 2
The slowest queries and what they read.
- 3
Parts, merges and memory under your real load.
- 4
Your last incidents: cause, fix, and how long they took.
- 5
What your team can run without help.
This week, without us
Three things you can do yourself.
- 1
List every schema change from the last three months, and where each one is recorded.
- 2
Query system.query_log for your slowest queries this week.
- 3
Write down the fix for your last incident, before it's forgotten.
Analytics databases: ClickHouse, Postgres and warehouses
Sound like you? Bring it to a 30-minute call. We'll tell you which tests your case needs, and what they cost.
This page describes what teams in this situation commonly face. It is not a client story.