Big Data in Real-Time: How ClickHouse® powers Admiral’s visitor relationships for publishers

Recorded: June 16, 2020
Presenters: James Hartig, Co-Founder, Admiral & Robert Hodges, CEO, Altinity

In this webinar, Altinity CEO Robert Hodges hosts Admiral CTO James Hartig on how Admiral uses ClickHouse to power real-time visitor relationship management for publishers, helping them grow subscriptions and engagements (including its well-known ad-block recovery) while complying with GDPR and CCPA.

Robert covers the key ClickHouse features behind the architecture: the MergeTree engine family, compression and type-aware codecs, materialized views, sharding and replication, and tiered storage with TTL moves.

James then explains Admiral’s move from MongoDB, where oversized documents and sharding issues drove the switch, to a design built on time-based parts, horizontal scaling, a Redis buffer, batched Pub/Sub inserts, and materialized views that fan one insert out to an archive, a targeting table, and a SummingMergeTree session queue. He reports 95th-percentile read latency under 20 ms before joining Robert for an extended Q&A.

Here are the slides:

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 0:08 – Introduction and housekeeping
• 1:37 – Introducing Altinity and Admiral
• 2:59 – What Admiral does: engagements and targeting
• 5:14 – Event storage requirements and tech stack
• 8:31 – Table engines: MergeTree and SummingMergeTree
• 12:54 – Compression, codecs, and LowCardinality
• 16:15 – Materialized views
• 18:43 – Sharding, replication, and distributed tables
• 23:33 – Tiered storage and TTL moves
• 27:53 – From MongoDB to ClickHouse
• 34:01 – Admiral’s architecture: Redis and the pending queue
• 44:22 – Future use cases and takeaways
• 47:33 – Q&A

Webinar Transcript

0:08 — Introduction and Housekeeping

Robert Hodges: Welcome, everybody. My name is Robert Hodges. I will be helping host our webinar today on big data in real time: how ClickHouse powers Admiral’s visitor relationships for publishers. I’m delighted to say we have James Hartig with us from Admiral, who’ll be telling us about their journey with ClickHouse and some of the key features they’ve used to enable their business.

Before we jump into the details, I’d love to share a few items that will help you enjoy this more. First and most importantly, this webinar is being recorded; we will send you a link to the recording along with the slides, so you don’t need to take frantic notes. You should get that within a few hours after the webinar finishes. The next thing is that we have time planned for questions. If you have questions as the talk progresses, feel free to pop them into the question and answer box on the screen. If possible, we may answer some as the talk proceeds, but we’ll also have time at the end and try to cover every question you have. A final thing is that at the end of this webinar, we will run a short poll, as we do for all our webinars; our goal is to collect answers to three simple questions that help us determine how we did and find topics for future webinars. So with that, I’d like to dive in.

1:37 — Introducing Altinity and Admiral

Robert Hodges: My name is Robert Hodges. I’m the Altinity CEO. I’ve been working on databases for 30 years, and ClickHouse is database number 20, and as my CTO tells me, it is my favorite. I’m delighted to have James Hartig with us today; he’s CTO of Admiral and is currently working on distributed systems and Go to build the Admiral platform, which he’ll talk quite a bit about in the slides to come.

A little about our respective businesses. Admiral is the visitor relationship management company; it’s a platform that helps publishers grow visitor relationships and revenue. I think you’ll be very interested in how they do it; it certainly explained some basic plumbing about the internet that I did not previously understand. As for Altinity, we are the leading software and services provider for ClickHouse. We make ClickHouse fast and cost-efficient, and we’re also a major committer and community sponsor in the United States and Western Europe, so you may have joined some of our events in the past. With that, I’d like to turn it over to James, who’ll introduce Admiral and how they’re using data in their business.

2:59 — What Admiral Does: Engagements and Targeting

James Hartig: Hi everyone, I’m going to get started by giving a quick overview of what Admiral is. Admiral provides a platform for publishers to build relationships with their visitors and, in turn, increase their revenue. What we allow them to do is sell subscriptions to subscribers and also show engagements to these users. Engagements could be anything from asking for an email to get people onto your email newsletter, and our most popular product, which you’ve probably seen if you use an ad blocker, is our ad-block engagement. We actually detect if you have an ad blocker and show a modal that asks you politely to whitelist or turn off the ad blocker, or alternatively, subscribe to the site. We also offer products that allow a publisher to conform to different privacy regulations such as GDPR and CCPA. We have a simple one-tag installation, much like Google Analytics; you just copy and paste a script tag and put it in the head of your site, and you can do all configuration after that point in our dashboard without making any code changes.

We allow the publisher to design their own engagements however they want, with different colors, texts, and images, and they can also do elaborate frequencies for those engagements. For instance, in this example workflow, the publisher has an engagement show up every hour for three times and then show up on every single page view, so they progress the engagement as the user visits more frequently. They can also target these subscriptions or engagements based on multiple different facets, such as the referrer, the subscription state, where the user is located, and even custom key-value pairs that the publisher can provide in real time. All of this targeting happens in real time, and the publisher doesn’t need to make any code changes on their site when they change these options.

This is a high-level overview of how the targeting works. A user, the smiley face, visits a publisher’s website, in this case a news website, and our JavaScript on the page collects multiple pieces of information, such as the URL, the environment like which browser they’re using, whether they have an ad blocker, maybe whether they’ve opted out of CCPA, and any custom key-value pairs, and makes an AJAX call to what we call our front-end nodes. Those front-end nodes make a request to our history service, which pulls the recent months of events out of the database to send to our targeting service, which takes all of that information and decides what experience to show the user.

5:14 — Event Storage Requirements and Tech Stack

James Hartig: What kind of events do we store? We store page views, engagements, what the user has subscribed to, when they unsubscribe, their consent to GDPR, whether they’ve opted out of CCPA, and at the moment we’re generating over 2,500 events per second globally. We require very fast lookups for this targeting to happen, since we’re going to show an engagement that potentially takes over the whole page; we don’t want the user to wait and have the experience interrupted if they’re already browsing and then something comes up later. We also want long-term storage of these events for case studies or product development, just to understand how users react to different settings or how our product is being used. And finally, we need to aggregate these events to build a session and understand what happened over the lifetime of that visit, for instance, if the user signed up and subscribed to a pricing plan, or if they bounced and left the site and didn’t react favorably to the engagement.

For our user events, we’ve decided to go with ClickHouse to store these events. This allows us to have long-term storage on spinning disks, do fast lookups with an SSD and an in-memory cache, and use materialized views to insert only into one table to simplify our application, yet use those materialized views to build other rows and other tables for analytic purposes; I’ll talk more about that later.

Just a quick overview of our tech stack: we’re primarily on Google Cloud, using Google Compute, Google Pub/Sub, and Memorystore products, which I’ll talk about later. We have a Go back end with a microservice architecture; we have a little over 50 services at the moment, all across five regions on multiple continents, and we have over 10,000 HTTP requests per second globally at peak. Because we’re a pre-Series A company, we’re very focused on efficiency, and we’re doing all of this with less than 250 virtual machines. Now I’ll hand it over to Robert to talk a little about the features we use from ClickHouse.

8:31 — Table Engines: MergeTree and SummingMergeTree

Robert Hodges: Great, thanks a bunch, James. What I’m going to do now is dive into the background of some of the features that underlie the Admiral architecture. This is not a complete description of ClickHouse capabilities, but zeroes in on things relevant to this use case.

The first thing I’d like to start with is ClickHouse table engines. For those of you new to ClickHouse, one thing you’ll notice is that ClickHouse has an extraordinary variety of tables; the implementation of the table types is called an engine. If you used MySQL in the past, it’s quite similar to the idea MySQL had say 20 years ago, with the difference that ClickHouse has a vastly larger number of them, and they all do useful things. The number one engine in ClickHouse is MergeTree, the workhorse engine used to store large amounts of data. It’s called MergeTree because it allows data to be loaded in relatively small parts very quickly and be immediately queryable, and then in the background it is merged down to a more efficient representation, basically larger chunks of data that make reads more efficient. In the picture here we show creating a simple MergeTree table for test purposes. We have a couple of columns, we give the engine, and we have this PARTITION BY clause; because this is a test, I say PARTITION BY tuple(), which is a trick in ClickHouse to say I don’t really need this broken up into parts because it’s not going to be very big, and I ORDER BY key. I’m going to add a couple of values into it using inserts, and if you selected out of them afterwards you would of course see two rows.

It turns out that seeing all of the source rows isn’t always what you want, so we have another type of table engine called SummingMergeTree, most commonly used for aggregation. The idea is that it stores aggregates; it’s closely related to another MergeTree type, AggregatingMergeTree. What it does is, for every row corresponding to the primary key, it coalesces all the aggregation results for that key. Here’s how we create it: in this case I’ll create a SummingMergeTree using another nice idiomatic bit of SQL where I create it from the column definition of my previous example table with the same ORDER BY, which gives us a primary key that contains the key column. Then I grab the data from that table I generated previously, because that lets me show you a really interesting property of the SummingMergeTree table: if all of the columns that are being summed, which by definition is any numeric column in the table, sum to zero, ClickHouse will just automatically delete the row.

In order to see this, I have to run a select, a select with GROUP BY where I’m checking the sum of values from the keys, and with the particular data I loaded, that row just disappears out of the result. It’s the FINAL keyword that does this; it forces ClickHouse to resolve all data fully, similar to what happens when parts get fully merged. So this is an interesting and idiomatic usage of both different table types and a particular behavior of the SummingMergeTree table. We’ll come back to that in a minute, because it turns out Admiral uses this in a rather novel way that I think you’ll be interested to hear about.

12:54 — Compression, Codecs, and LowCardinality

Robert Hodges: A really big feature in ClickHouse is compression, and there are two ways to think about it. There is non-type-specific compression, for which ClickHouse offers LZ4 by default; it also has other compression options, one of which is ZSTD. And then ClickHouse has what are called codecs. A codec is a type-specific transformation of a data type to make it more compact in storage. In this case I’m using a codec called low cardinality; the syntax is a little different, as for many of the codecs you put them in the CODEC clause. Putting that function around the string says we would like to use dictionary encoding with this string, which means that instead of storing the string, we make a dictionary that contains all the string values, assign each one a number, keep the dictionary, and store the integer that corresponds to it instead of the full string. This is a very common transformation that can be used to make strings that have say ten thousand or fewer values much more compact in storage.

This is our test table, defined as MergeTree, not worried about partitioning or order, because we’re going to insert a bunch of test data and just show you the result of the codec compression. I won’t show how I generated the test data, but if you’re a ClickHouse person you probably guessed it comes out of system.numbers. What’s interesting is the effect that low cardinality combined with different kinds of compression has on storage, and it’s dramatic. In this bar graph, the first set of columns shows the uncompressed size of the strings I generated, then the effect of applying LZ4 compression, getting it down to about twenty or twenty-one percent of its original value, and then ZSTD level one compression, which gets us down to 12.28 percent, so pretty good compression. What’s interesting is that for the column where we applied low cardinality, which turned the column into an array of integers, when we apply LZ4 compression we get virtually no compression; in fact it gets a little bigger, because LZ4 is not particularly efficient on four-byte integers. But if we turn on ZSTD level one, we get that down to about 75 percent of its previous value. So this shows how you can play around with applying a transformation through a codec and then use different types of compression to get different storage sizes. There are many more codecs; we talk about these in some of the performance talks, but this gives you an idea of what you can play around with.

16:15 — Materialized Views

Robert Hodges: Another great feature in ClickHouse, still looking at single tables and single nodes, is materialized views, probably one of the single most useful features of ClickHouse. Basically they implement a synchronous post-insert trigger: you insert a bunch of rows, ClickHouse puts them in memory and fires a trigger on them, which then runs a transformation on the data. A question just popped up; you can put that in the question window, they’re easier to answer there, so I’ll still get it, but I just want to give you a heads up.

Materialized views basically transform data and can be used for everything. The most common use by far is aggregation, but they can also be used to build automatic reads out of a Kafka table engine, to build pipelines, and in recent versions to pre-compute what’s called the last point query, and they can also change your sorting or primary keys. There are cases where ClickHouse is very fast, but there are cases where it helps to have the entire data set for the table sorted in a different way, because that enables different queries to be more efficient; it’s similar to the idea of Vertica’s projections, if you’re familiar with that, and materialized views enable you to do that. The block diagram on the right shows the implementation of a last-point materialized view, a special type that just remembers the last value inserted for a given primary key inside a table. That allows you to very efficiently get the last reading from a family of sensors, and it’s a very common idiom. The reason it’s so effective is that it gives you huge compression on the amount of data you have to scan, because it only stores a small amount at the tail of the table. This is a really great feature; we’ve done a number of webinars and blog articles on it, definitely something to check out if you’re not using it.

18:43 — Sharding, Replication, and Distributed Tables

Robert Hodges: Finally, I’d like to talk about scaling features. Many applications can just go with a single node, but as your data size grows and you need more IO ops, one common thing to do is shard the data, splitting it into disjoint sets stored on different hosts. So in this example one third of the data is stored on the red host, one third on the blue host, and one third on the yellow host. You can also use built-in replication, which is managed using ZooKeeper, giving you additional copies of the data. That’s great for high availability, but it also gives you concurrency, because ClickHouse can choose between different replicas when it needs to execute a query.

A lot of things in ClickHouse have additional table engines to make this possible, and the two interesting ones here are the distributed table engine and the ReplicatedMergeTree. The ReplicatedMergeTree is a variant of MergeTree that knows there are other copies of itself in different locations, presumably on different servers, and if you insert into a ReplicatedMergeTree table, it will automatically ensure the data you insert replicates out to any other replicas it’s connected to. The connection is established through a path in ZooKeeper, which is fairly straightforward. In this case we have a couple of pairs of replicas divided into shards, which just means half the data is in the top and half is at the bottom, replicated twice. When we do a query, we don’t want to run an individual query against each copy, so that’s where the distributed table comes in; it knows the location of all the shards and that the shards are divided into replicas, and when you run a select on it, it decides where to send the queries to fetch your data.

Here’s an example of what happens when you run a query through a distributed table. Your application picks one of these distributed tables, here called on_time, and that’s what we call the initiator node, the ClickHouse server you connect to to start the query. It will automatically break up the query and push down as much of it as it can into the individual replicas; for each shard it picks a replica and pushes the query down. Generally speaking, and this can be configured a little, it’s going to be your leftmost sub-select that’s distributed and pushed down to run locally, so for example all your big aggregates where you do a full table scan get computed locally, then the partial aggregates get brought up and merged on the initiator and handed back to the application. This can be phenomenally efficient if your queries can take full advantage of this parallelization. Here’s an example of test results on the airline on-time data set. I ran a fairly large query scanning quite a bit of data, and we see basically linear performance. It’s over 173 million rows with fairly slow storage, which is why the response is not very quick, but for the cold run, where we force the reads to disk, it takes about 10 seconds, which decreases by half when we have two shards, by half again with four shards, and when the data is hot, meaning already present in, for example, the OS buffer cache, we again get a linear decrease in time. So the performance is really great; it lets you get very short response times from large data sets.

23:33 — Tiered Storage and TTL Moves

Robert Hodges: The final feature I’d like to talk about is one that Admiral has been thinking about applying, and that is tiered storage. For those new to ClickHouse, traditionally it can only mount data in a single place; in other words, you have a single directory where all your data is located. Unfortunately, that’s not really what you want for things like time series data, because typically the most interesting information is the most recent stuff. Maybe 95 percent of your queries look at what happened in the last day or even the last couple of hours, then a much smaller percentage scan the last month, and a vanishingly small percentage scan the entire series, for example the last year.

The idea behind tiered storage is to give you different types of storage that store the data, so for example your hot data, the stuff that arrived in the last day, goes onto NVMe SSD, and then older data automatically migrates onto slower, cheaper, but much denser hard disks. Another interesting thing about the way ClickHouse does this is that not only can you have different storage types, but you can group them as you see on the right side, and in the best case this will quadruple your bandwidth, because we can spread queries as well as inserts over multiple devices. This is available in version 20 of ClickHouse, and you’ll see there’s a new type of configuration setting called a storage configuration, used to enable tiering. At the top you have a policy that defines a couple of different volumes the data will be stored on, and those volumes consist of disks, which are mount points. What you see here is data1, your hot storage, and then data1 and data2 for your compact hard disk.

This is actually very easy to apply. One of the really cool features added recently, stable I believe in 20.3, is TTL to move the data. What you’re seeing here is a table with a normal partition and ORDER BY, and then a TTL set. Some of you may be familiar with TTL deletes; in this case the TTL has been extended so the data arrives and goes to the default volume, which is hot, and then after an interval of about a day ClickHouse automatically merges the data over to the slow volume, putting it on one of those slow disks, and then after a year it will be deleted entirely. The way we tell ClickHouse where to find these volumes is we set the storage policy, which you can see here is “tiered.” So this is a very simple addition to the table syntax but gives you the powerful ability to use more storage and take advantage of both different capacities and different capabilities of storage, and also to multiply them by using them together. With that, I’m going to turn it back to Admiral. We’ve got a question pending here which ties into some of the details of compression; we’ll get that one at the end.

27:53 — From MongoDB to ClickHouse

James Hartig: That was great. I’m going to talk a little about how we arrived at ClickHouse and what we started with. What we started with was actually MongoDB. The only reason we chose it was that we already had self-hosted MongoDB, so it was a familiar database, and the only way it actually made sense to do it was to shard by region. We have five regions across three continents, as I mentioned earlier, so we would shard the data by region, so if you’re in US East 1 and did a lookup, it would hit the local primary and not have to make a cross-region or even cross-continent lookup, in order to speed those up. This gave us the advantage of a flexible data structure, so we didn’t have to do any alters or worry about the database at all if we needed to add a new event type or field. But it gave us a downside of having very large documents, because since the primary key of the document was the user ID, as a user visited over time they would have thousands if not tens of thousands of events stored in their document, which made it very slow to look up, and the decoding time on our application side was significant. It was hard to prune these old visits, because we’d have to loop over every single user and do a pull operation to pull off the old events we don’t care about anymore. It also gave us huge indexes, so whenever we needed to add a new primary or a new replica there would be really long rebuild times, sometimes over 24 hours, before the replica was usable.

MongoDB operates on a primary-secondary model, and if you’re using sharding you also have to deploy mongos, which made our deployment complicated and updates difficult, because we’d have to step down the primary before we could update it and then promote it back. Because of this primary-secondary model, we could only do reads against the primary, so we didn’t have any stale reads, but that led us to only being able to use vertical scaling, so we’d increase the number of CPUs, disk capacity, and RAM on each server whenever we needed to grow. Finally, there were multiple bugs we encountered with sharding, including that a read at a shard boundary should really only hit one shard but actually hit both because of a bug that’s still not fixed to this day, and an issue with cleaning up after a shard split, where we had trouble cleaning up the old data left over on older replicas. I linked both issues in case anyone’s interested.

At the beginning of last year we decided we needed to switch to something else, and what we landed on was ClickHouse. Some of the benefits, as Robert mentioned, are that we have materialized views now, so the application can insert into one table and ClickHouse automatically massages that data and puts it into other tables without our application needing to worry about it; I’ll talk about how we use each of those materialized views in a few slides. We also have time-based parts, so instead of looping over each user to delete data, we can use the parts ClickHouse builds automatically to move, archive, or back up each month’s chunk of data, so we don’t have to worry about each individual user. We also have horizontal scaling, so if we need to increase scale we just add a new replica, and we can do rolling updates without any downtime; there’s no stepping down of a primary, all the replicas are homogeneous, so we can deploy a new one or take one down, update it, and bring it back without any downtime. There is one thing we can improve on: at the moment, whenever we add a new replica, our application cannot handle stale reads from ClickHouse, so we have to wait until that replica is caught up before adding it to the application pool. That’s something not too difficult for us to automate, we just haven’t gotten around to it yet.

34:01 — Admiral’s Architecture: Redis and the Pending Queue

James Hartig: We also have a very high compression ratio with ClickHouse, which I’ll detail in a few slides, but in order to achieve the fast reads we have, we dedicate 50 percent of the RAM on each of our ClickHouse servers to the uncompressed cache, which enables us to get an 85 to 90 percent uncompressed cache hit ratio, before even the OS cache. Our deployment is three ClickHouse servers per region, all homogeneous; we can deploy a fourth if we need more capacity. We also have a Memorystore, which is Google’s hosted Redis cluster, per region. The reason we added that is that we do batched inserts into ClickHouse: whenever an event comes in, we asynchronously send it to Pub/Sub, and a separate consumer we call internally a bundler takes all of those events over a few seconds and inserts them in one big batch into ClickHouse. This makes ClickHouse’s job of merging simpler and gives us fewer, larger parts; the general consensus is that you shouldn’t be doing more than one insert per second to your ClickHouse cluster.

But this causes a potential race condition where a user generates an event such as a page view, and we need to read that event out of ClickHouse before it’s been inserted; for instance, if a read comes in 100 milliseconds later, the event isn’t in ClickHouse yet because it’s still being bundled. So we have a fairly small Redis cluster that just holds recent events: we synchronously add the events to Redis and asynchronously send them to Pub/Sub, so when it’s time to do a select or get the user’s history, we read first out of Redis and then out of ClickHouse, backfilling with the rest of their history, since Redis is small and only holds about an hour of data.

How do we use the materialized views? All of our inserts go into a spinning-disk table we call events, on a spinning disk purely for cost, so we can store vast amounts of data without spending a ton of money. We have two materialized views coming off that table. One stores a similar row on an SSD, which only holds the last few months of history since we don’t need more for targeting, allowing a smaller, faster data set to do targeting reads from. It also inserts a row into a summing merge tree table we call the pending table, that acts as a sort of queue, which I’ll detail later. We’re hoping to switch to TTL-based storage in the future so we can eliminate one of these materialized views and have a single table to insert and read against, simplify the application a bit, and expand our spinning-disk capacity beyond a single disk to multiple disks.

On the performance side, on our selects out of ClickHouse the 95th percentile is actually less than 20 milliseconds, and our 50th percentile is 7 milliseconds; our goal for the project starting last year was 100 milliseconds for the 95th percentile, so we’ve gotten way below it, and we’ve also decreased the 95th percentile compared to MongoDB. As a comparison, our reads out of Redis are 12 milliseconds at the 95th percentile and 3 milliseconds at the 50th, so it really surprised us how fast ClickHouse is able to service these reads, almost coming within twice as fast as Redis. We’re doing over 1,000 ClickHouse queries per second globally, and in our busiest region over 400 reads per second across those three ClickHouse servers, and roughly 50 percent of our queries actually hit ClickHouse. The reason is that whenever an event comes in we synchronously add it to Redis, so if a user is visiting for the first time and has only one event, say a page view, all of their history is stored within those few hours of data in Redis, and our application stores what we call a tail in Redis to indicate there’s no more data for that user other than what’s in Redis, so we can query straight out of Redis and not hit ClickHouse at all. That happens roughly 15 percent of the time.

Now I’ll go into how we use this pending table. We have to compile what we call a session, a collection of events that happened for a user until there is no activity within three minutes, or a catch-all at midnight where all sessions expire. This definition is taken straight from Google Analytics, just so we’re synchronized with what they do and there’s no confusion about who defines a session in what way. We have a materialized view that inserts data into this SummingMergeTree, and as a recap, SummingMergeTree sums a value column for the same primary key and deletes any rows with a zero value. We’re actually able to use this table as a queue, so instead of using something like Kafka or Google Pub/Sub, we use ClickHouse and have a single data store for this queueing of events. For example, I have a highlighted row here: from 10:00 a.m. to 11:00 a.m., Alex on the game site had five events, so we inserted five rows into the events table, which created five rows in the SummingMergeTree table, each with a pending column of one, and that table then summed all of those rows together into one row with a value of five.

Every hour we look at this table and all the events that happened for all the users in that hour, and decide if a session has ended: if there was no activity in the last 30 minutes, or if midnight hits, a session has completed for that user. We process all the events, determine whether that session was successful, for instance if they saw an engagement and then subscribed, or unsuccessful, for instance if the user bounced after one page view, and then we insert a negative value into the table, which offsets the positive value, ending up with a value of zero in the pending column and the row removed. Here are two example queries that underpin this queue in ClickHouse. At the top we read from the system.parts table to see which parts, where the table is partitioned by hour, have how many rows left in them, meaning some users in that hour have not been processed yet. Once we have that list of hours, we run the next query on each of them to get all the users and their pending count from that table. As a little graph from the last three days, you can see the number of parts in the table is a very sawtooth pattern, because as the hour goes on more rows build up in the queue, and as soon as the hour ends a number of sessions have ended so we insert negative numbers and the rows get removed. I highlighted midnight on each of these three days in red, and you can see a dramatic drop at midnight because all sessions expire then.

44:22 — Future Use Cases and Takeaways

James Hartig: Finally, some future ClickHouse use cases. We have an internal Slack bot that lets our sales team know whenever a customer signs up, there’s a large traffic spike, or a publisher uses a new feature, and this is currently using ClickHouse; we’re hoping to expand it to be a public-facing feature where the customer can target their engagements differently based on whether an article is popular or trending, so if it gets posted to some news aggregator, maybe they want to alter the frequency or message for that article. Additionally, all of our publisher analytics, showing how many page views and how much revenue we’ve generated, is currently stored in Bigtable; we have over a hundred terabytes of that data in Bigtable, but unfortunately Bigtable is not fast enough to do real-time queries, so we have an hourly aggregation that runs and stores aggregated numbers in MongoDB. We’re hoping to switch to ClickHouse later this year to replace Bigtable and the aggregation, simplify that, and do real-time queries straight on ClickHouse. This would also give us a SQL interface to this data instead of a custom query language, which has prevented us from using various BI tools that only work with a SQL database. And finally, we have some audit-logging goals we’d like to put into ClickHouse because it’s just a much better data store than what we have in MongoDB at the moment.

That’s all I have on our path to ClickHouse. Some takeaways: we’ve had multiple benefits from switching, including expanded storage capacity, increased scaling because it’s horizontal now, performance, since our 95th percentile dropped dramatically, and reduced complexity deployments, since all the replicas are homogeneous so there’s no need to worry about which one is primary. This was a big milestone for us, because it was our first non-self-hosted data store, and I’d like to give a huge shout out to Robert and his team. They gave us customized training in the beginning, taught us all about the different MergeTree types, replication, and the ins and outs of ClickHouse, and actually helped us with our proof of concept on the design and the schema optimization, so we could be sure as quickly as possible that this was the right decision for us. As I mentioned, we’re hoping to expand our ClickHouse uses to new products and already have a few lined up. That’s it for me.

47:33 — Q&A

Robert Hodges: Thank you so much, James. I’m going to enable my video so people can see us bounce back and forth. Great presentation, and thanks for the shout out, we really appreciate it. I also really appreciate you explaining how this stuff works, because shortly before we got together on this, a website asked me to subscribe and I was wondering how they do that, so now we know. Let’s look at the questions. Here’s one: how much CPU and RAM is required for a compression algorithm? Did you do any studies on that?

James Hartig: We did compare ZSTD to LZ4. I don’t have the numbers off the top of my head, but it was something around 10 to 20 percent more CPU actually required, but the cost savings at that scale, where we’re talking about mere milliseconds, made it worth it for us to use ZSTD, especially since it performed better on our UUID column, which is strictly random data. So we decided to go with that, but at level one, the lowest level, at the moment. I don’t have exact numbers for CPU and RAM, but I wouldn’t say they were anything on different orders of magnitude, so I would just choose the one that works better for your data set.

Robert Hodges: I think people are in general moving towards ZSTD, so then it’s a matter of determining, for your workload, whether you care more about space, where you’d use a higher ZSTD level, or you just care about getting a minimal amount of compression but more than zero, with really fast reads and low CPU usage, which is your use case right now. As you mentioned, you’re also hoping to look into per-column compression codecs.

James Hartig: Exactly, that would be great.

Robert Hodges: I noticed you had a select out of system.columns to look at your compression levels, so that’s definitely useful. By the way, I want to kick off a poll; I hope you can fill it out while we answer a couple more questions. Here’s another: how many distinct strings gives the best performance when encoded with low cardinality? Our stock answer at Altinity is anything less than 10,000. Did you have further thoughts? Your cardinality was one to two thousand for that case.

James Hartig: Yeah, we went off just what you told us, that it should be less than 10,000, and we’re significantly lower than that, so it’s been a fine solution for us.

Robert Hodges: Your compression ratios are really good too, which I think is possibly because you have lower cardinality. The sample I ran didn’t get super good ZSTD compression, by the way; it was only about a twenty percent overhead to do the compression on insert, but you’re clearly getting better results than I did in my tests. By the way, low cardinality is probably the number one thing we recommend to users, because it’s such an easy thing to put in and there are so many cases where it really helps.

James Hartig: We did not know about that at all before we had the proof-of-concept design with you guys to go over schema optimization. It was recommended that we use low cardinality on the strings, and we were like, what is that, and then we looked at it and it was amazing, especially for the country problem, where there’s a defined set of countries, we’re not adding new countries all the time, so it was a great use case.

Robert Hodges: Let’s see another question: what’s the detail of removing rows with a zero value in a materialized view? I assume you guys run a FINAL at the end of the selects to ensure things got properly collapsed.

James Hartig: What we’re actually doing is, we don’t rely on it removing the rows in real time. We do a select with a GROUP BY and then HAVING pending greater than zero, so we only select rows out of the table that have a pending greater than zero. But where the delete comes in is that over time, as those merges happen, the data size goes down, so we’re not having to manually prune that table; the data size naturally goes down as more merges happen and things go to zero. I actually just did a select from the system.parts table yesterday and we only have three days’ worth of parts in there; if it wasn’t deleting those, we’d have years’ worth of data that were all zero. So this is an efficiency on our side that lets us get those rows deleted without having to manually do it, and it saves a lot of cost and space.

Robert Hodges: There’s at least one person on this call, the person who asked this question, who is looking at similar approaches using ReplacingMergeTree. These alternative engines have some really cool properties, and I definitely recommend people check them out.

James Hartig: We actually looked into the replacing merge tree for this. The only trick with it is that you have to insert the same row except for the value at the end, and then it replaces that last value, but the rest of the row needs to be the same. Given that we’re just trying to increase the number here, this was a little simpler for us, but the same use case could also be achieved with a ReplacingMergeTree.

Robert Hodges: I have a question for you, since I think we’re near the end of the audience questions, please do submit more: you mentioned a bunch of features you liked about ClickHouse, what’s your number one ask for new features?

James Hartig: Our biggest one, which is actually being solved as you mentioned, was the TTL. I commented on it on GitHub and was actively looking at it last year, hoping we could use it as soon as possible, because for our publisher analytics we have a very high hit rate on just the last few weeks of data and your example was perfect; we have less than a 1 percent hit rate on data from a year ago, but a very high hit rate on data from the last week or month. It doesn’t make sense for us to pay for SSD storage for all that data, so this automatic moving of parts as time goes on was something we would have had to build in-house if not for this feature. The other thing is there’s a little bit of edge case around the distributed table. We’re not actually using the distributed table at the moment, and one downside of our current deployment is the manual intervention whenever we add a new replica; we have to wait until it’s caught up before telling the application about it. It’d be nice if this was handled by ClickHouse, with a setting on the distributed table where you can say don’t do reads against replicas that are more than X seconds behind. The only downside is that if all the replicas are behind by that amount, we’d still like to do those queries, we don’t want it to fail as if there are no replicas available, so there are a few edge cases we just haven’t ironed out.

Robert Hodges: One thing I should point out about storage, particularly in Amazon: are you using EBS block storage for your data, or local disk?

James Hartig: No, we’re not using block storage at the moment; it’s something we’ve looked into but haven’t quite made that transition yet. We’re using ephemeral storage on the VMs.

Robert Hodges: One interesting thing we discovered in Amazon is that if you use block storage, which is network attached, Amazon will limit the bandwidth to each individual EBS volume, which effectively puts a bottleneck between your CPU and memory and your storage. But it turns out that if you allocate and mount, for example, four EBS volumes, Amazon will expand the bandwidth correspondingly, which is interesting and somewhat non-intuitive. We’ve seen big performance boosts just by using a JBOD configuration where we attach a bunch of them; we had one benchmark where we were able to exceed NVMe SSD, which was surprising, and again it has to do with the way Amazon is limiting bandwidth.

James Hartig: On the Google Cloud side, for block storage and network-attached storage, they actually smooth out the IOPS over a second, which is interesting. This took us a while to understand; we had some applications that do a burst of reads from disk, and it showed up that some of those IOPS were delayed and buffered because we hit the rate limit, but if you look at it over a second we didn’t actually hit the limit. Google smooths out that rate limit, so if you have a hundred IOPS per second you can’t do more than that proportion in a given millisecond, or they’ll start smoothing it, so that’s another case where having multiple disks could benefit you if you won’t actually hit that per-millisecond limit.

Robert Hodges: That’s a great point. There’s a question that just popped up: is there a use case in Admiral or Altinity where you need a sequence of events in the order they happened and do some aggregation per session?

James Hartig: We do require the events to be in order. Our current use case is that we store the events in order and fetch them back out in order. I didn’t really talk about the primary key of this events table, but it’s ordered by time, and we’re using time-based parts with a monthly partition. The way that works is that when the merges happen, it puts the data in order even if it wasn’t inserted that way, so we can fetch the data out in time order.

Robert Hodges: I have something to add to this, because that particular use case comes up all the time in funnel analytics on websites, and it turns out one of the ways to attack it in ClickHouse is to use arrays. There’s a wonderful talk that was given at a ClickHouse event in Europe a couple of years ago, so this case definitely comes up. ClickHouse has great array support, and in fact I can do a pitch: our next technical webinar will be on arrays, so that’s on tap and we’ll share some of the secrets.

James Hartig: They clarified the question: by sequence they mean the activity of this customer per session. We do use that at the moment; the use case is determining whether the user has bounced, looking at the actual order of events in that session and confirming whether they bounced, or determining if they subscribed because of a particular engagement. A publisher might have any number of engagements showing and they want to know the performance of each one, so by having the events in order we can determine which engagement led to that conversion. It’s very similar to funnel analytics.

Robert Hodges: Okay, two more questions and then we’ll call it a day. Is it good practice to use a single huge table to store events with a lot of nulls, or multiple tables? In our experience it’s generally better if you can put everything in one big table, even at the expense of adding columns, because columns are essentially free if you don’t query them. Did you have to deal with that in your schema?

James Hartig: Yeah, a little. As I recall, nulls take up a little bit of space, I forget if it’s one byte, but it’s almost negligible. The way we were told, when we originally went over the proof of concept, was that having one big table is a vastly simpler option from the application side and the management side, and as you mentioned, if you don’t use a column in a particular query, it doesn’t affect that query at all, so I would definitely say just have one huge table even if there are nulls in it.

Robert Hodges: Sometimes it’s even more efficient, because nulls effectively add files, so using something like 0 or minus 1 or something appropriate instead can also speed things up occasionally. Final question: do you ever update in ClickHouse, and if so how often and what strategies? I think you showed one way to do updates, which was just to let those tables collapse over time through the semantics of the SummingMergeTree, which is a really great illustration of how ClickHouse handles updates. You tend to have idiomatic ways of doing it; yes, you can do a SQL update, but it’s far more efficient to do something where ClickHouse automatically collapses the data, or use TTL to delete. There are other tricks too, and we have a blog article on this, so definitely check the website.

With that, James, thank you so much for doing this. I love the presentation; your use case is fun. For people listening, check out Admiral’s technology and give them a call if it looks like something you can use.

James Hartig: I appreciate you taking the time out for this as well, this was fun. I enjoy talking about technologies we use, and ClickHouse has been a super success for us, so I’d like to make sure everybody else understands that as well.

Robert Hodges: Cool, thanks a bunch, and have a great day everybody, talk to you soon.

FAQ

What does Admiral do, and why does it need real-time data? Admiral is a visitor relationship management platform that helps publishers grow subscriptions and revenue through engagements, including its well-known ad-block engagement, while supporting privacy regulations like GDPR and CCPA. It installs as a single script tag, much like Google Analytics. Every page visit triggers real-time targeting, where JavaScript collects signals, a history service pulls recent events, and a targeting service decides what experience to show, all fast enough that the visitor’s browsing is never interrupted.

Why did Admiral move from MongoDB to ClickHouse? With MongoDB, Admiral keyed documents by user ID, so active users accumulated thousands or tens of thousands of events per document, making lookups slow and pruning old data difficult, with index rebuilds sometimes taking over 24 hours. The primary-secondary model limited them to vertical scaling and reads against the primary, and they hit sharding bugs. ClickHouse gave them time-based parts, horizontal scaling, rolling updates with homogeneous replicas, and high compression.

How does Admiral use materialized views? All inserts go into a single spinning-disk “events” table, and materialized views fan that data out automatically. One materialized view writes a similar row to an SSD table holding only the last few months for fast targeting reads, and another writes into a SummingMergeTree “pending” table used as a session queue. This lets the application insert into one table while ClickHouse builds the other tables, and Admiral hopes to consolidate further using TTL-based tiered storage.

How does the SummingMergeTree “pending” queue work? SummingMergeTree sums a value column for rows with the same primary key and deletes any row whose summed value reaches zero. Admiral inserts one “pending” row per event, which sum to a per-user, per-hour count. Each hour it checks which users have ended a session (no activity for 30 minutes, or midnight), processes those events, and inserts a negative value that zeroes the count so the row is removed. They query with GROUP BY and HAVING pending greater than zero, letting background merges clean up the table over time.

Why does Admiral pair ClickHouse with Redis? Admiral batches inserts into ClickHouse through Google Pub/Sub and a bundler, since doing more than about one insert per second is discouraged, which creates a brief window where a just-generated event is not yet in ClickHouse. A small per-region Redis cluster synchronously stores recent events (about an hour’s worth), so reads check Redis first and then backfill from ClickHouse. About 15 percent of the time a new visitor’s whole history fits in Redis, so ClickHouse is not queried at all.

What performance and compression results did Admiral see? Admiral reported a 95th-percentile read latency under 20 milliseconds and a 50th percentile of 7 milliseconds against a goal of 100 milliseconds, close to its Redis read latency of 12 milliseconds at the 95th percentile. By dedicating 50 percent of each server’s RAM to the uncompressed cache, it achieves an 85 to 90 percent cache hit rate before the OS cache. Using ZSTD level one plus LowCardinality, it sees compression ratios around 96 percent on user-agent strings and over 99 percent on low-cardinality columns like country and engagement ID.

© 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.