Big Data and Beautiful Video: How ClickHouse® Enables Mux to Deliver Content at Scale

Recorded: 23rd June, 2020
Presenter: Adam Brown — Co-founder and Head of Technology and Architecture, Mux and Rober Hodges — CEO, Altinity

In this webinar, Altinity CEO Robert Hodges hosts Adam Brown, head of technology and architecture and a co-founder of Mux, to explain how ClickHouse enables Mux to deliver beautiful video at scale. Mux is an API platform that makes video streaming as simple as a single API call, the way Stripe simplified payments and Twilio simplified text messaging. Its two products are Mux Video, which takes a video URL or upload and returns a multi-bitrate playback URL for any device, and Mux Data, which collects client-side telemetry from many platforms to power dashboards, real-time monitoring, and alerting. Mux runs huge volumes through ClickHouse, including its data product, CDN access logs, and internal monitoring, and uses the data to optimize encoding and delivery in real time.

Robert walks through the ClickHouse features Mux relies on. He explains the MergeTree engine and its on-disk layout, where a sparse primary.idx index points through .mrk mark files into compressed .bin data, then compression with LZ4 and ZSTD and the LowCardinality codec, the system.columns catalog and the bar() function for inspecting compression, materialized views for last-point queries and aggregations paired with TTL, skip indexes for cutting I/O, and the CollapsingMergeTree engine for handling updates by writing sign-based cancellation rows.

Adam then details Mux’s migration of its historical metrics product from Postgres to Citus and finally to ClickHouse. Operating on raw video-view records rather than expensive pre-aggregations gave Mux unlimited filter depth, dynamic time ranges, at least a 2x performance gain, about a third of the previous disk size, and roughly half the infrastructure cost even with four replicas. He covers the challenges of rewriting queries, using CollapsingMergeTree with the FINAL keyword for view updates, a materialized view as a lookup index, and deliberately keeping Nullable columns despite a small performance cost. He closes with Mux’s Kubernetes deployment using chproxy for routing and a blue-green secondary cluster, then joins Robert for an extended Q&A.

Here are the slides:

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 0:07 – Introduction and housekeeping
• 1:34 – Speaker introductions
• 2:50 – What Mux does: video and data products
• 6:00 – How Mux uses its data
• 8:01 – Table engines and the MergeTree layout
• 13:10 – Compression and codecs
• 17:04 – Materialized views and aggregations
• 20:19 – Skip indexes
• 23:02 – CollapsingMergeTree and handling updates
• 25:15 – Mux’s historical metrics: from Postgres to ClickHouse
• 31:27 – Migration challenges: updates, lookups, and Nullable columns
• 37:17 – Deployment: Kubernetes, chproxy, and replication
• 42:02 – Q&A

Webinar Transcript

0:07 — Introduction and Housekeeping

Robert Hodges: Everybody, welcome to our webinar on big data and beautiful video: how ClickHouse enables Mux to deliver content at scale. I’m delighted to have our guest Adam Brown with us today; we’ll be digging into how Mux goes after this problem and how ClickHouse enables them to manage data for their business.

Before we dig too deeply, I’d like to point out a few things that will help you enjoy this webinar. First, the webinar is being recorded, and we will send you a link to both the recording and the slides shortly after it’s finished, so you don’t have to frantically take notes. Second, we have a question-and-answer box on the control screen, so if you have questions during the webinar, feel free to type them in; if possible we’ll take them during the talk, and we also have abundant time at the end for questions and answers. Finally, we do a poll at the end of every webinar; it’s three simple questions that help us determine the next topics, how we’re doing, and what your level of ClickHouse usage is. So with that, I’d like to dive in.

1:34 — Speaker Introductions

Robert Hodges: As I said, we are delighted to have Adam Brown, head of technology and architecture at Mux. He’s one of the Mux co-founders, and he has extensive experience in video encoding going back to Zencoder, a pioneering cloud video encoding service. As for my background, I’m CEO of Altinity. I’ve been working on database systems for over 30 years, in fact about 37 at this point, with additional work on virtualization and security, so I’ve worked on a lot of databases, and ClickHouse is approximately number 20.

A little about our businesses: Mux is an API platform that helps people make beautiful video possible for every development team. Adam will go into detail about how the business works and how it’s enabled by data. Altinity, my company, is the leading software and services provider for ClickHouse. We’re a major committer to the ClickHouse project, and we’re also a big community sponsor both in the US and Western Europe. So with those intros, I’d like to turn it over to Adam to dig into Mux, how it works, and how data is important to their business.

2:50 — What Mux Does: Video and Data Products

Adam Brown: Thanks, Robert, and hi everyone. To start off, I’ll give you a quick overview of what Mux is and how we’re doing things. This is an example of all the pieces you need to put together in order to stream video effectively today. There are a lot of different components: ingest, storage, transcoding, your origin, CDN and delivery, and monitoring, all of which really take a lot of expertise and knowledge to put together. The example we like to use is that years ago, before Stripe, payments was a similarly complicated procedure where you had to wire together a lot of different pieces, and similarly for text messaging before Twilio. Both of those companies replaced these difficult workflows with single API calls, very simple to use. So Mux is trying to do the same thing, but for video streaming.

Our basic video product is: you post a URL to a video to us, or directly upload it to us, and we give you back a playback URL that can be used on any device, mobile, desktop, or TV, with multi-bitrate delivery using HLS for degraded networks. We optimize the encoding for those videos, and we do all this with live streaming as well. So we have two products: one is Mux Video, the product I just described, where you give us the video and we handle everything in between, and the other is Mux Data, our product for measuring the performance of that. They’re standalone products; the data product is used by people that don’t use Mux Video, and this one we’ll dig into a little more today. With Mux Data, we collect client-side telemetry from lots of different platforms, everything from Rokus to Xboxes, obviously iOS and Android, and we’ve put together dashboards and alerting systems for customers to see how their video is performing across lots of different dimensions, and Mux Video uses the same data to optimize the product as well.

On the data product, we have a few ways to access this data. We have APIs for getting the historical metrics out, we have real-time metrics, which is the current state of the world; we’ve actually used that for the past two Super Bowls, where Fox and CBS have used it to monitor their IP streaming in real time to make decisions like, is this network segment out, should we swap over to another one, are we performing well across all of our devices. We also have dashboards for viewing this data in our products, and since we treat it as your data, you can export it to your own platform, we can stream it out in real time, and we also have alerts tripping off of it.

6:00 — How Mux Uses Its Data

Adam Brown: A few more cases for this data. Most of the data for our data product comes from our SDKs, and here’s a list of how many we support. Internally, we also collect all of our CDN log data, the access logs for every piece of content that’s requested, and we put that into ClickHouse as well, and we have a ton of internal monitoring use cases in ClickHouse. A quick example of where we use some of this data: a couple of years ago Verizon had a major network outage in the Northeast, and these graphs are from some of our ClickHouse data based on the two different CDNs we were delivering through, so you can see that when the network outage happened on one network segment, we were able to automatically switch over and deliver through a different CDN, which is the green and yellow bars.

Finally, we use this data internally to optimize the video encoding itself. Any particular video may be most optimally encoded at a different bitrate; with complex video you can deliver the same quality at a lower bitrate. Once we can select that optimal bitrate, we can also use our global network data to build what we call the right prediction set for delivering that video, things like, maybe half of your viewers have a one-megabit connection or 500-kilobit connections, so we’ll build an encoding tuned to maximize delivery there. So that’s some of the use cases we have for this amount of data, and where we’re using ClickHouse to query it quickly and effectively and drive a lot of decisions in the video product as well.

8:01 — Table Engines and the MergeTree Layout

Robert Hodges: Thank you very much, Adam. What I’d like to do now is jump in and give some background on the general ClickHouse query features that Mux is leaning on, and then Adam will come back and go into much more detail about how they use these to enable Mux Data to function. Let’s start with a basic intro, since some people attending may not have used ClickHouse before. The most basic concept is the MergeTree table engine. ClickHouse uses table engines in a way similar to MySQL, if you’re an old-time MySQL user; the idea is that each table engine is optimized to access, manage, and deliver data. MergeTree is the most important one overall, because it’s the basic structure used to maintain very large tables of data, and that’s a particular focus for ClickHouse, dealing with very large fact tables.

When we define a MergeTree table engine, we use SQL tuned a little for the dialect ClickHouse uses. There are some important components: there’s an engine clause, where we select MergeTree, then PARTITION BY, where in this case, with sample data on airline on-time performance, we break the data up by date and, within the parts, by month, giving ClickHouse guidance on how the table should be broken into segments that can be processed independently. Finally, within those parts we give an ORDER BY, which tells ClickHouse how to build a primary key index and sort the data in the parts. There are at least a dozen variants of this table type, and you’ll see a couple of them as we go forward.

Let me dive into the actual layout within the parts of a MergeTree table, because this is important to understand why ClickHouse is so fast and to give you guidance on how to optimize it. Within these parts we see some major data structures. The first is a file called primary.idx, the primary key index, which is a sparse index, meaning it only has entries for, by default, every 8,192 rows. This means we can access data in chunks using a relatively small index that will typically fit in memory, even for very large tables. That index points to dual files which are arrays that point to the data, because ClickHouse is a column store, meaning that instead of storing all the data of a row together, we store all the data of each column together; you can think of it as a long array broken up into chunks. To refer to a point in the data, we run down the primary.idx file, and if our query uses one of the fields in that index, we locate the point in the .mrk2 file, an array of pointers showing where the segments of compressed data begin, and then read that. We call this segment of data in the index a granule, we call the entries in the mark files marks, and then there are compressed blocks for the data. In some cases ClickHouse will not be able to use this index, because you’re filtering on different conditions or doing a brute-force query, in which case it just scans the rows.

One other thing I should point out is that ClickHouse has a special optimization you’ll see if a column is set to be Nullable: you will see an extra set of .mrk2 and .bin files for each of those columns, storing a bitmap that lets you identify which row values for that column are null. So it adds extra storage, an extra file that you have to look at.

13:10 — Compression and Codecs

Robert Hodges: One of the really key things that makes this work is compression. Because we store the data in an array for each column, the types are similar, and we also have a sort order, which often further improves the odds of compression because the data is non-random. By default ClickHouse will use LZ4 compression, and you can get enormous performance gains from that, because it compresses the data down and makes it quite small. You can also use what are called codecs, which come in a couple of flavors. A codec is a type-specific transformation on the data that organizes it in a different way before we turn it over for compression. One of the most important transformations we run is called low cardinality. What this does is take a string, look at every value in the part, put it in a dictionary, and then instead of storing the strings, store integer offsets into that dictionary. LowCardinality works really well for any column where the values are on the order of 10,000 or less.

This is an example of a test table where we’re not only using the low cardinality codec but also picking between different types of compression, LZ4 as well as ZSTD, which is also supported by ClickHouse. If we load this with test data, with a reasonable distribution to give a way of testing effectiveness, we can see the effect. The first set of bars is column A with no low cardinality applied: we have the uncompressed size, we get down to 20.84 percent with LZ4, and down to 12.28 percent with ZSTD, so pretty good compression out of the box. We then see that if we apply low cardinality, at least in the LZ4 case, we get even better compression, because it’s reducing the amount of data we’d stored, and ZSTD as it turns out is pretty good at compressing the integers, so that gets us to below 8 percent in total. This can have a huge effect on both the amount of storage and, because ClickHouse performance is fundamentally based on how much I/O you do, the more you can reduce the data in storage, the faster you get to go, because ClickHouse can read it out of storage as quickly as possible.

One common question is, how did I get those numbers? It turns out ClickHouse has really awesome system catalogs, and here’s the query I used to get the numbers in the previous slide. It also illustrates a cool ClickHouse feature, the bar() function, where you don’t have to go to Google Docs, you can generate those bar graphs right inside the SQL query. As you’re working with ClickHouse, tuning schema, and checking performance, you want to go back to the system.columns table, look at the level of compression you’re getting, and keep tuning it until you can’t reduce it anymore.

17:04 — Materialized Views and Aggregations

Robert Hodges: I want to switch gears and talk about another really important ClickHouse feature, materialized views. In addition to compression and compression codecs, which reduce the data stored in the source table, one of the other big features ClickHouse has is materialized views, which restructure the data and put it in a different table. This is typically used because it lets you down-sample the data in a way that gives you ready answers to questions you commonly ask. Here’s an example: we have a benchmark, originally developed by the TimescaleDB folks, that uses a CPU table containing a bunch of measurements of the activity on particular CPUs. A common question in monitoring is, what is the current CPU usage? I don’t care what it was two months ago, I want to know what it is right this second, or the last update you have. You can construct a materialized view that, as data is inserted into the CPU table, stores the last value recorded, so the materialized view acts as a trigger. This is an example of part of that view’s definition; it’s basically a SELECT that fires automatically and uses some special syntax ClickHouse makes available to control aggregation precisely, in this case looking for the maximum time by CPU and adding that row into the new table.

This table uses an engine called summing merge tree, which is optimized for doing aggregation, and the key thing is you get enormous compression of the data, far beyond what’s available through LZ4 or ZSTD, because you’re only storing a fraction of the data. These examples show the typical levels of reduction of data size you’d get; this is a feature that lets you get queries to come back in milliseconds. The most common way this is used is to do aggregations, a very common use case for a large percentage of people who have ClickHouse installations. For example, a common pattern if you’re recording web visits is to have your source data, which you might hold for something like seven days, and ClickHouse has a great feature called TTL that lets you say hold it for seven days then automatically delete it, and then you have long-term aggregates, so for example you might want to remember the number of hourly sessions. There are many other uses of materialized views, and Adam has a really cool alternative usage he’ll visit later in the slides.

20:19 — Skip Indexes

Robert Hodges: There’s yet another way of cutting down I/O which I want to mention, because it’s relevant to some of what Mux is doing and in general for large ClickHouse installations, and that’s what’s called skip indexes. With ClickHouse, as I’ve mentioned, performance is all about reducing I/O. A skip index is basically an index that lets you look at different marks when you’re scanning columns and say, the data I’m looking for is not there, so you can skip reading it and skip decompression, and all that time is freed up for faster response. There are a number of different index types; this shows a typical ADD INDEX command. For example, an ngram index is kind of like a bloom filter on chunks of text, and there’s a set index which stores the values, which turns out to be handy for indexing things like UUIDs. It’s fairly flexible across data types, so you can add these in easily; if you run an OPTIMIZE TABLE it will implement the index, or you can do an ALTER TABLE that will cause the index to be materialized.

The effectiveness really depends on how your data is distributed, so widely distributed or high-cardinality data tends to have pretty good effects with these indexes. This is an example from our pet data set where we’re filtering data based on destination, and on carrier where we look at a couple of different carrier values. In this data set, if we use the carrier column and select a small carrier, we get a pretty good query response rate, coming back in 90 milliseconds in this test. By contrast, if we set the carrier to WN, which is Southwest, that’s about one-seventh of the rows in this data set, so the rows tend to be sprinkled through most of the marks and you end up reading most of the data, giving much slower query performance and a less effective index. But for high-cardinality data that’s evenly distributed, or has favorable distributions, this can be a very good technique for reducing I/O and speeding up query response.

23:02 — CollapsingMergeTree and Handling Updates

Robert Hodges: One final thing I’d like to talk about is that ClickHouse has a wealth of table engines; you’ve already seen two so far, but one I’d like to mention, because Mux makes use of it, is the CollapsingMergeTree. This belongs to a family of at least three table engines designed to help you deal with updating data. ClickHouse in general does not do updates or deletes; what we like to do is write more data which cancels out what happened before. The reason is that actually going in and rewriting a row, since we’re writing a bunch of columns, is a very expensive operation; it’s much simpler to go somewhere else, put a mark in, and say, cancel what I said, then let queries sort it out, and eventually it gets merged into the table.

The CollapsingMergeTree is a variant on this. The way it works is it has a sign column. In this example we have a CollapsingMergeTree with some test data, a user ID and views. In the first insert we say user 32 currently has five views. A little later we get more metric data, and it turns out user 32 now has 98 views, so we add a row with the first value and a minus one, which cancels out the previous row, and then a row with a one on it that has the new value. Then, if you do a select off it, in this very simple example I could just put a FINAL keyword on the end of the select, and it will automatically collapse the rows down and give me the final value. It turns out when you’re actually using these in a distributed system it’s more complicated than that, but this table gives you a way of dealing with updates in a systematic fashion that doesn’t impact your performance. We’ve got a couple of questions here, which we’ll take in the question and answer; what I’d like to do right now is turn it over to Adam again to talk about specifically what you’re doing with ClickHouse underneath.

25:15 — Mux’s Historical Metrics: From Postgres to ClickHouse

Adam Brown: Sure thing. We’ll start by talking about our data product, particularly our historical metrics product. One of the reasons I want to start there is that this was the first product we built at Mux, and we did not build it on ClickHouse; as we went along, we found it harder and harder to manage our existing solution, and we made a huge migration over the last year to ClickHouse with a lot of success. We’ve got the beacons coming in from the left, sent from our SDKs on the player side; the way that works is that roughly once every 10 seconds a beacon is sent from a playing video player saying this person has had this much rebuffering, or they’ve watched this many more seconds of video, and all the details about the client itself. We’re going to talk about the metrics product in particular. This is our historical metrics page for video views; this data comes from those beacons, but we have a layer that assembles them into the full video-view session. The way we look at this is the total viewer experience score by total viewing session. We have other products, particularly the real-time product, that treat things more like what’s happening right now across network segments, but this one is unique in that it has to assemble all of those beacons into the full view. On the left you can see the kind of charts you can build, comparing how iOS and Android are doing over the last day or up to three months, their typical retention period, and on the right you can see the data it’s built off of, a full video view with the full window of everything that happened, all the play and pause events and all the details. In ClickHouse, our rows are that full video view, and they’re about a hundred and fifty columns wide for all the details about the video view.

A few of our metrics around what we’re storing: currently we’re storing billions of views a month in ClickHouse, at the large size 500 million views for a customer, generally averaging a hundred thousand beacons per second of raw data, and we’re operating all of this off of raw data today, so all our queries operate off those full video-view records, no pre-aggregation. The way this works is we have multiple tiers of collectors collecting those beacons, putting them on Kafka, directing them to our processing layer, and this processing layer is all a custom in-house solution that can assemble those beacons and write them out as full video-view records. It can also export those out to other Kafka streams for customers and partners and drive real-time alerting, but today we’re mostly going to talk about historical metrics operating on the video views themselves.

Where did we start with this? We started like any reasonable startup would, with Postgres, and then we needed a bigger Postgres, and then we needed a sharded Postgres, so we moved to Citus, and then to an even fancier Citus setup where not only were we using the Citus distributed Postgres tables, we were also bringing in columnar storage at the Postgres level, managing a lot of complexity. Ultimately we had a really fancy setup where we had Airflow driving lots of aggregation, so we were pre-aggregating all of these metrics into hourly divisions by a limited set of dimensions. This was really expensive and really hard to maintain; it worked, but it was a constant struggle to keep it running effectively. The switch over to ClickHouse gave us an unlimited filter depth which we didn’t have before; before, we had pre-aggregated data down to about three levels of filtering because aggregation was so expensive, and we got to eliminate that completely. Now we can have things like exclusion filters, so we can say show me everything that’s not Android, and run queries on dynamic time ranges, so instead of just hourly aggregation we can do minute or 10-minute aggregation. With our old system, the more traffic we added, the more we found an exponential increase in the compute cost for doing aggregation. Moving this all to raw video views, we saw a dramatic increase in performance and decrease in latency on ClickHouse versus even our pre-aggregated Citus data, so almost everything improved by at least 2x, even our video-view listings. On the cost-benefit side, since we’re no longer doing the aggregation, we’re saving a ton on CPU and disk, and the columnar compression, particularly low cardinality, gave us about a third of the disk size we saw before even on the un-aggregated data. We’re running smaller machines, and in the process of this migration over the first half of the year, our infrastructure cost for the project basically halved, so it was just a huge win all the way around.

31:27 — Migration Challenges: Updates, Lookups, and Nullable Columns

Adam Brown: We did run into some challenges; it wasn’t the easiest migration in the world. ClickHouse and Postgres are not the same, you have to think about things a little differently, and the queries need to be rewritten, it’s not a drop-in replacement. We do update views, which Robert alluded to earlier where we use the CollapsingMergeTree, and individual record lookups presented an interesting challenge. On the view updates: why do we have updates? The concept of putting together a video-view session is not always the easiest to get your head around. We can’t really write the view into this query database until the view is complete, so a part of that processing layer says this view has now ended and it goes into this database to be queried. We have a relatively low percentage of these that get updated, but an example is when someone pauses the video or closes their laptop for 20 minutes, comes back, opens it up, and starts playing, and we want to reassemble those together.

There are a couple of ways to handle this. The way we historically handled it was to have that processing layer remember what we’d said before, reassemble it into a new record, and write that out. Since we already had this stored data of what we’d written before, we were able to use the collapsing merge tree, issuing that negative sign value for the old row to cancel it out and issuing the new one. This probably wouldn’t work as well if it was 80 percent-plus of the views getting updated, but realistically we see about 10 to 15 percent of views updated, and this solution works pretty well. We do use the FINAL keyword sometimes, and that was one of the most challenging things about this approach, because the FINAL keyword has a performance hit since it needs to reconcile all of those plus and minus signs. So we use it in some cases, like listing video views, where you want the full list of records and need to squash those down so as not to present duplicates, and the performance is fine there. On the larger metrics queries, though, if you’re looking back at say ninety days of data at once, we’ve taken different approaches where we can use the sign value in the query to cancel it out; for calculating an average, instead of using the FINAL keyword, we can take advantage of the sign itself to know how to cancel out those values. We also wrote a nightly optimize which does this collapsing and rewrites the partition, so it does that full collapse.

The next challenge was the individual view lookup. We allow customers to look up individual views by their ID, or a customer-provided ID, and a few other ways. So we use the materialized view approach, where we have a separate second table that more or less maps exactly which part a video-view record could be in, by those IDs, and then when we’re doing those lookups we can check that table first to know exactly what to look into. This is Robert’s skip-index approach, which wasn’t available yet in the main branch when we were doing this, so we still need to explore that, but this actually works surprisingly well, and we use it for several different use cases. You can dig into a lot more of the technical details in our blog post that I linked below.

Finally, we make extensive use of the Nullable columns that Robert mentioned earlier. There’s a note in the ClickHouse documentation that Nullable can negatively impact performance; I was joking with Robert earlier that any time you put that in there, any engineer is going to recoil, and we did internally as well, where some of our engineers would go to do this implementation. We had used Nullable extensively in our old implementation, all of our queries were written expecting these potentially Nullable values, and where this comes into play is, say we really want to know the difference between no one sending a metric for a certain measurement versus the measurement actually being zero, so it’s very important for us to know it was null and not zero. So instead of doing all the work at the migration step to re-implement things as non-Nullable, we just said let’s try it, and it turns out it does have a performance impact, up to like 10 to 15 percent in our experience, but that was totally fine for our use case, and as you saw from my performance graphs, much better than what we were doing before.

37:17 — Deployment: Kubernetes, chproxy, and Replication

Adam Brown: Some deployment details about how we’re running this. All of our clusters run in Kubernetes; we’ve done that from the beginning. I wouldn’t say there’s been any significant challenge to running it in Kubernetes, no more than running any other database, and in fact it’s probably a little simpler. We have four clusters and four deployments, all done a little differently. The one we were just discussing, the historical metrics, runs as a primary cluster with replication internally, and we also run a second cluster that is replicated as well, so typically we have four replicas of this data. We weren’t going to do that originally when we were looking at cost, but once we saw how good the compression was, we had this opportunity to store all this data with this many replications and still be significantly cheaper than what we were doing before. Part of the reason we run the secondary cluster is for performance, but also for doing migrations: we have this opportunity to operate on a more or less live cluster with a scary migration before we go to the primary, so we treat this as a blue-green deployment, running a major migration on the secondary, swapping the queries over to it, and then running on the other cluster, which has been really nice.

Similar story with our real-time metrics, though we don’t use the internal replication for real time; it’s very short-lived data, only a day’s worth of retention, but we do the blue-green multi-cluster approach there as well. For our CDN logs we don’t do replication, and we actually usually run it all on a single node; it’s not customer-facing with uptime requirements, it’s just for internal metrics queries, and the point is that you can get surprisingly far on a single node, growing it vertically before you even have to think about going horizontal. The last cluster we have is our raw beacons; we recently started recording all of those raw, before they get rolled up into video views, mostly for debugging purposes, but we’re looking at turning that into something product-facing too. All of this is fronted by chproxy, a great little project; if you haven’t looked at it, it handles an extra layer of caching, user routing, and rate limiting, and we use it to swap queries between these different clusters, so we can say our API dashboard user is now routed to the secondary cluster or the primary. Additionally, it exposes Prometheus metrics, which is what we use for all of our metrics; the operator does that now as well, but back when we first started using this, chproxy was the best way to get Prometheus metrics out of ClickHouse.

For what’s next, we’re building some more advanced alerting based on continuous queries to ClickHouse, and we’re moving most of our internal BI metrics and data warehousing at least partially into ClickHouse; anything at a significant scale, we default to that. And as I mentioned, for those beacons, we’re looking at turning that custom processing layer of Go servers, which is rather complicated, into just running materialized views off the raw beacons in ClickHouse to materialize those video-view records.

Some final takeaways: several of our data engineers and architects, when they ran into ClickHouse, have made the statement, and I’ve made it plenty myself, that it feels like magic operating off the raw data like this, particularly given where we came from with very complicated, very large, very expensive aggregation clusters. It’s just made a world of difference in our simplicity around scaling and operations, and ClickHouse has really become our default for statistical data, anything at a significant insert rate that we’re ever going to run a query on, it’s going into ClickHouse these days.

42:02 — Q&A

Robert Hodges: Thank you so much, Adam, that was a really great presentation. I have a couple of follow-up questions, and we also have some from the crowd, but first I’d like to launch our poll; for the webinar attendees, please fill that out, it’ll take you 15 seconds tops. I’m going to go through some of the open questions. There was a question: is there a primary key or an index? You said index and sort, would you please explain?

Great question, I think I glossed over that. ClickHouse doesn’t have a primary key in the sense of a constraint the way we’re used to in databases like MySQL or Postgres. The primary key is really a primary key index. You can have multiple values with the same primary key, and MergeTree is perfectly happy with that, it doesn’t really care. But there’s a distinction between the entries in the primary key, that sparse index I talked about, and your sort order; that sort order can be different and can have more columns than the primary key, so people can play around with that to get the best performance. One of the things you typically do with sorts, as I said before, is that by sorting the right way you can get better compression. There was another question: can it be joined with a MergeTree table? Adam, I think your example showed that pretty well with the lookup tables you were using. Did you guys have any difficulty getting that view to work, since you have to construct it and put it in your queries, but it looked fairly straightforward?

Adam Brown: I didn’t do the implementation, so it was easy for me. But I hear the complaints, and I think it was pretty straightforward.

Robert Hodges: Okay, great. Another question: does the final keyword affect query performance? Yes, it does, it’s expensive. Do you have any comments, since you’re sometimes using it and sometimes not, what was the trade-off?

Adam Brown: It really depends on the query, particularly how many parts you’re going to be querying over. We just did a performance comparison this week of one of the routes, and we saw like a 10 to 15 percent increase in latency on most queries, but it was still well within the balance we wanted. It’s really something you have to measure, because it’s so case-by-case.

Robert Hodges: And FINAL is now parallelized in the latest ClickHouse releases, so we are doing some work to improve the performance on that. A question here: migration from Citus, is that why a secondary cluster is required? I’m not sure I totally understand the question; does it make sense to you, Adam?

Adam Brown: The secondary cluster is another ClickHouse cluster; we run two totally independent ClickHouse clusters, and one of those reasons, like I mentioned, is to be able to test migrations on the real production data. While we were doing the migration from Citus, we ran them both in parallel for about three months, so we ran Citus and both of our new clusters, and we routed some queries to ClickHouse and slowly rolled out more and more, comparing very carefully. We were seeing such good performance with ClickHouse that we switched it all over a little sooner than we expected, but no, the secondary cluster isn’t required for data integrity reasons since we have replication in the single cluster; it’s more of a nice-to-have from an operational standpoint, and the extra assurance, and we also use it for ad-hoc internal queries that we don’t want to run on the production database, so it’s like a perfect mirror that we can use as a playground.

Robert Hodges: Great. Folks, keep sending in questions if you have them. I had a couple more from my side, going back to your overview on the deployment details and Kubernetes. What Kubernetes distribution are you using, are you using EKS, your own, or something else?

Adam Brown: We’re using kops today. We run on both Amazon and Google; we looked at using each of their hosted providers, but they each have their own unique intricacies, and we wanted to be consistent across both, so we deployed all kops. We’re actually looking at migrating to something else, but I don’t remember what right now.

Robert Hodges: And were there any learning-curve issues on Kubernetes, or was it something you already had expertise in?

Adam Brown: We’ve been using Kubernetes for a while, so it wasn’t really anything new. One thing we ran into, since we use Kafka a lot, is that we initially tried to use one ZooKeeper deployment for everything, and that didn’t go very well. Kafka can be quite intensive on ZooKeeper, particularly when you’re running lots of partitions, and running replication with ClickHouse can also be quite impactful on ZooKeeper performance, so generally now we run a keeper per cluster and per application.

Robert Hodges: That’s actually a recommendation we make, to not mix those; it’s a bit like oil and water. A second question, off this page, is the routing you do with chproxy; you mentioned user routing to different locations, can you talk about how that’s actually implemented?

Adam Brown: There are two different ways to go about it. What we’ve done is set up in chproxy a lot of different users that are routed differently, so let’s say for our real-time cluster we have a ClickHouse real-time green user, so we have one mechanism at the user level, at the API level, picking which user you want, and that routes independently. We can also internally change the mapping of what user goes to what cluster; chproxy has a concept of clusters and users, and you can map them however you want. It does get a little complicated because we deploy all that with a config map in Kubernetes, and you have to roll the proxy nodes to do that, so it’s not the cleanest upgrade. Generally what we tend to do is have a ton of very verbose mappings; we even have a mapping down to each individual node, so if we want to access an individual node externally or from somewhere else in the cluster, we can. This is also how, if we need to access across clusters, we have mTLS to chproxy and don’t have to go directly to nodes.

Robert Hodges: Got it. And with these proxies, this routing is invisible to your applications, is that right?

Adam Brown: Yes, they just have a notion of user, but beyond that that’s all they need to know. We do also sometimes write not through chproxy; in certain use cases we want to write to specific nodes and not write through the distributed table, so we’ll go directly to the nodes for those, but often we do our writes through chproxy as well, mainly because of the really nice metrics it gives us.

Robert Hodges: Follow-up question: you have two clusters, do you take regular backups?

Adam Brown: Yeah, this has been in flux a bit. We do take snapshots, though snapshots can be problematic, like actual EBS volume snapshots, because you don’t really know the state of the cluster when you do that. We’ve rebuilt from that, and we have a relatively large retention in Kafka, so we can say if something’s corrupt on the leading edge, drop off a couple of days and replay it from Kafka. We’ve also used the more standard S3 backup approach; we’re not using that right now.

Robert Hodges: Is that clickhouse-backup, by any chance?

Adam Brown: Yes, we have used that, and I believe we use it for some of the logs clusters, but really we’ve relied more on replication and writing to clusters, and on the longer retention in Kafka for replay in a disaster recovery scenario.

Robert Hodges: And what’s your retention in Kafka, how many days?

Adam Brown: It varies a lot; it’s usually three to seven days for beacons, for example, but our CDN logs are much longer.

Robert Hodges: And what’s your rough data size across these clusters?

Adam Brown: That’s a great question. The CDN logs, historical metrics, and raw beacons are probably in the tens to 230 terabyte range; the real-time metrics is much smaller because it’s only a day’s worth of retention, probably a few terabytes.

Robert Hodges: I had a final question from my side: do you have any advice for people starting out on ClickHouse and building a fairly sophisticated processing pipeline, based on your experience?

Adam Brown: If you’re doing it in Kubernetes, start with the operator today. We don’t use the operator, mainly because we’ve built a lot of operator-like infrastructure and tooling internally for running databases on Kubernetes, but we’ve had good success with the operator when we’ve used it. The other thing would be, don’t be stingy with memory; we’ve tried to run really tight at times in the past, particularly on that real-time cluster where in theory we don’t need a lot of CPU and memory, but ClickHouse really likes to have a lot of memory to work with, so go ahead and give it 16 or 32 gigabytes. I think we basically run everything with 32 gigabytes of memory today.

Robert Hodges: Well, I think we’ve taken a bunch of questions, both from the crowd and from our list. Thank you everybody for the polls, and Adam, thank you so much for coming on today and doing this presentation; it’s great having you. We also really appreciate all the support you’ve given to the ClickHouse community, and I look forward to hearing more presentations from you. All right, thanks everybody, I think we’ll call it a day and close the webinar. You’ll get a link shortly to both the slides and the recording, so if you have further questions, feel free to ping us at Altinity and we’d be glad to follow up. Thank you and have a great day.

FAQ

What does Mux do, and how does it use ClickHouse? Mux is an API platform that makes video streaming as simple as a single API call. Its Mux Video product takes an uploaded or linked video and returns a multi-bitrate playback URL for any device, while Mux Data collects client-side telemetry from many platforms to power dashboards, real-time monitoring, and alerting. Mux stores its data product, CDN access logs, and internal monitoring in ClickHouse, querying raw video-view records to drive dashboards, optimize encoding, and make real-time delivery decisions.

Why did Mux migrate its historical metrics from Postgres to ClickHouse? Mux started on Postgres, grew to a sharded setup, then Citus with columnar storage and Airflow-driven hourly pre-aggregation, which became expensive and hard to maintain, with aggregation cost growing exponentially with traffic. Moving to ClickHouse let Mux query raw video views directly, gaining unlimited filter depth, exclusion filters, and dynamic time ranges, with at least a 2x performance improvement, about a third of the previous disk size, and roughly half the infrastructure cost even while keeping four replicas.

How does the MergeTree layout make ClickHouse fast? A MergeTree table is split into parts, and each part contains a sparse primary.idx index with an entry roughly every 8,192 rows. That index points through .mrk mark files into compressed .bin column data, so ClickHouse reads only the granules a query needs rather than scanning everything. Because data is stored column by column, compressed, and sorted by the ORDER BY, reads are highly efficient, and reducing the I/O required is the most important driver of ClickHouse performance.

How do compression and the LowCardinality codec help? ClickHouse compresses columns with LZ4 by default and also supports ZSTD, and codecs add type-specific transformations applied before compression. The LowCardinality codec replaces repeated strings with integer offsets into a dictionary, which works well for columns with about 10,000 or fewer distinct values. In the example shown, a string column dropped to around 12 percent with ZSTD alone, and applying LowCardinality brought the total below 8 percent. The system.columns table and the bar() function let you measure and tune compression directly in SQL.

How does Mux handle updates to video-view records? Because ClickHouse avoids in-place updates, Mux uses the CollapsingMergeTree engine, which has a sign column: to update a record, Mux writes a row with sign minus one to cancel the old value and a new row with sign plus one. Roughly 10 to 15 percent of views get updated, for example when a viewer pauses and resumes much later. For listing queries Mux uses the FINAL keyword to collapse rows, accepting a 10 to 15 percent latency cost, and for large metrics queries it instead uses the sign value directly in the query, plus a nightly optimize that rewrites the partition.

How does Mux deploy ClickHouse? Mux runs everything in Kubernetes with kops across both AWS and Google Cloud, operating four clusters: historical metrics, real-time metrics, CDN logs, and raw beacons. The historical metrics cluster uses internal replication plus a second replicated cluster used as a blue-green environment for testing scary migrations and for ad-hoc queries, while CDN logs run on a single vertically scaled node. All of this is fronted by chproxy for caching, user routing, rate limiting, and Prometheus metrics, with Kafka retention enabling replay for disaster recovery.

© 2026 Altinity, Inc. All rights reserved. Altinity®, Altinity.Cloud®, and Altinity Stable® are registered trademarks of Altinity, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc. Altinity is not affiliated with or associated with ClickHouse, Inc. Kubernetes, MySQL, and PostgreSQL are trademarks and property of their respective owners.