Cheap, Cheap, Cheap! 6 Best Practices to Save Big Money on Your ClickHouse® Bill

Recorded: August 19 @ 08:00 am PDT
Presenter: Robert Hodges

Robert Hodges, CEO of Altinity, walks through six practical ways to reduce what you spend running ClickHouse, without giving up the speed the database is known for. He starts with the cheapest possible beginning: developing locally with open source and Docker Compose, which now takes almost no setup effort because coding agents already know how ClickHouse works. On licensing and startup costs, the saving there is effectively total.

The middle of the talk is about deployed systems, where infrastructure rather than licensing drives the bill. Optimizing MergeTree table schemas delivers the largest single win, and Robert works through a support case where a customer cut data size by 53%, then demonstrates with a million IP addresses how switching from strings to a native type with the right codec moves the compression ratio from 20x to 46x. He covers TTLs for both deleting and recompressing old data, tiered storage that moves cold parts to object storage roughly four times cheaper than block storage, and scaling compute down through Kubernetes and the Altinity operator.

The final practice is architectural. Bring Your Own Cloud puts the clusters in your own account, which unlocks savings plans, private pricing agreements, startup credits, and low-cost providers like Hetzner or specialized high-performance clouds like Nirvana Labs. The talk closes with questions on CAS, a new content-addressed storage type arriving in Antalya 26.6 that keeps a single copy of data on S3, and on TTL support for hybrid tables.

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 0:00 – Introduction and what Altinity does
• 1:41 – What ClickHouse is and why it became popular
• 3:27 – What actually costs money when running a database
• 4:01 – Practice one: develop locally with Docker Compose
• 7:11 – Letting an agent build the environment for you
• 10:06 – Practice two: how ClickHouse stores data
• 12:11 – Data types, codecs, and sort order as cost tools
• 14:28 – Storing a million IP addresses four different ways
• 17:51 – Finding compression levels with system tables
• 19:08 – How query choices drive memory and compute
• 22:38 – Practice three: TTLs for deleting and recompressing
• 26:52 – Practice four: tiered storage and S3-backed MergeTree
• 34:02 – Practice five: scaling compute capacity down
• 40:27 – Practice six: bring your own cloud
• 47:34 – Hetzner, Nirvana Labs, and low-cost providers

Webinar Transcript

0:00 — Introduction and Altinity Overview

Robert: My name is Robert Hodges, and I’m CEO of Altinity. I’ll be your presenter today in our webinar about saving money when you’re running ClickHouse.

We have a variety of techniques. This turns out to be an incredibly deep subject, so we’ll be skimming the surface and hitting some of the biggest ways that you can save money on your ClickHouse bill. There’s a whole wealth of details here that we may go into in future talks. In fact, I’m even thinking we should have a full seminar on this. But this will get you the basics, and of course I’ll make sure that there’s time to answer questions.

All right, let me jump forward then. Just a little bit of introduction to us at Altinity. We are a vendor for ClickHouse. If you haven’t heard of us, I know many of you that I can see on the attendee list are friends and users of many years, so you know us well. But we’re a vendor for ClickHouse.

We offer a cloud version of ClickHouse called Altinity.Cloud. I’ll talk a little bit about that, because that also has some features that help save you on your bills. We have also done enterprise support for ClickHouse since 2018, actually 2017 when the company started. So we are not affiliated with ClickHouse, Inc., and ClickHouse is a trademark of that company.

1:41 — What ClickHouse Is and What Actually Costs Money

Robert: So with that, let’s dive in and talk about ClickHouse. First of all, if you have not used ClickHouse before, let me give you a little bit of background.

It is kind of like you could think of it as being like MySQL but for analytics. So many of us grew up using MySQL. Of course, some of you are Postgres users. I actually use both, but more MySQL.

ClickHouse is very much like MySQL in the sense that it’s just a binary that you can download, and you can instantly start it up and begin doing analytic processing. Very, very simple installation. It understands SQL, runs everywhere, and it’s open source. In the case of ClickHouse, Apache 2.

Now it also has a bunch of features which differentiate it from a transactional database like MySQL or Postgres. It has a shared-nothing architecture which allows it to spread data across many nodes connected by a network. Really important feature. It stores data in columns, and then it has very, very good parallel execution of queries.

And with these features, people can build clusters that run into the petabytes. One of our biggest customers loads about 400 terabytes of compressed data into their cluster every day. So the actual uncompressed data size is many petabytes.

With these features, ClickHouse has become incredibly popular. It’s basically the default database for doing analytics for many use cases. Everything from observability to web analytics.

So that’s ClickHouse. Let’s assume that you’ve got it and you now need to think about, how can I run this efficiently? So get high performance, which is what ClickHouse promises, but at the same time do everything I can to reduce the operating costs as much as possible.

So when we look at a database like ClickHouse, there’s a number of things that cost us money. Everything from proprietary licenses to your own labor to things like storage, network, and computing. In large systems that are fully deployed, it tends to be these bottom things in various shades of blue, but we’ll also consider in passing questions of proprietary licenses as well as human labor.

4:01 — Practice One: Develop on Your Laptop with Open Source

Robert: So these are the things that we’re going to try and minimize. We will dive into this now.

One of the key things that you can do with ClickHouse is that if you’re just getting started, the simplest thing that you can do is just develop on your laptop. This is one of the things that’s really great about ClickHouse, because it runs anywhere that Linux does. It actually runs on other operating systems as well, but people principally run it on Linux.

And if you’re like me, I use Windows, I have WSL2, which is a really wonderful virtualization of Linux, very efficient to use, and very simple and quick to bring up. I have at least three ways that I can install Linux.

So we’re going to go look at one of those, which is to go ahead and use Docker Compose. Docker Compose is a really handy development tool. It will allow you to bring up a set of containers which are connected to each other, have exposed network ports, and you can use that to develop applications that include a ClickHouse server and then other processes that talk to it.

So if we’re doing that, one way that we might start is, let’s say that we have an application, and I’m going to choose a ridiculously simple one, curl. I can just containerize it. And it’s about as simple as what you’re seeing right here. Just pull from Ubuntu 22.04. Actually, that’s old. It should be 24.04 or 26.04. And go ahead and build this. And then you’ll have that Docker image built and ready to use.

The next thing you do is you create a Docker Compose file. And here it is. This is the entire thing. I had to make the text a little bit smaller to get it all on one page, but that’s basically it.

This will bring up my ClickHouse server. I’m actually using an Antalya build, which is our advanced technology build for ClickHouse at Altinity. We’ll talk a little bit about them as we go along. But I also have my client, which I just built. So those two services are going to come up. There’s some data that’s being stored for the database. And then we have some network configuration to allow us to log into ClickHouse from the client.

And in fact, that’s exactly what we’ll do. We’ll go ahead and bring that Docker Compose file up. This is about as simple as it gets. So I would run this in a window. Minus D says start it as a daemon. I can then do a docker ps to see what Docker containers are running, and then I can go ahead and run my client. That’s what that last command does, and it’s proving that that curl client can actually log into the ClickHouse server and get information out of it, in this case the version.

So setting up ClickHouse is really simple. But actually what I’ve just shown you is kind of old school, because nowadays what we would do, well, here you can go ahead and extend it and add your open source components, all of this stuff is available.

The key thing I wanted to get to is that with agents you no longer have to go find those instructions that I just showed you. You can ask Claude to do it for you. And when I was updating for this talk, of course I went ahead and created a prompt that was pretty much like the following. I’ve taken out a few words to make it fit on the page. But this is it.

And the interesting thing about this, and this is where open source is incredibly powerful, is that Claude is, like Codex, like other agents that connect to frontier models, the models are trained on open source projects like ClickHouse. So they know a great deal about how ClickHouse works. They know how Docker Compose works, and they can set up all of this stuff with very, very little direction from you.

So this makes it even easier to use this approach, and basically you don’t have to go anywhere. You don’t have to pay anybody to run a cloud. You can just run this stuff on your own laptop.

And in fact, if you think about this, how much does this save you on licensing? How much does it save you on startup costs? For example, if you do it with Claude, assuming you have a reasonably priced account, the savings on licensing as well as your labor, are 100%. So you basically get started from scratch with practically zero investment.

So that’s a really important way of saving money that happens early in development. And sometimes it’s not a matter of saving money, of course. It’s just that, for example, if you’re working in an enterprise, you really don’t want to bother with going through procurement or having to open accounts. You can just do this all by yourself.

So that’s the first example of how we can save money. And so what we’re going to do is now dig in and look at deployed systems and figure out additional ways that we can make them operate more cost-efficiently.

By the way, if you have questions, feel free to put them into the Q&A box here, which you should be able to see. And I can see a confirmation that you can all hear me. If you have questions, feel free to throw them in. I’ll answer them as we’re going along in the talk, or answer them at the end.

10:06 — Practice Two: Optimize MergeTree Tables

Robert: All right, let’s look at issues that are more relevant to deployed systems. So you build your system. You start to get data on it. Maybe you have a few billion rows on it, or maybe you’re not even fully deployed, but you’re loading test data, checking it out.

One of the best things that we can do is optimize MergeTree tables. Let me talk about that a little bit.

So first, let’s explain how ClickHouse works. These are the moving parts that you’re seeing in this picture of a ClickHouse installation. So these little guys, the hexagons, the lightning bolts, that’s our symbol for a ClickHouse server. ClickHouse servers have memory. One of the things that makes ClickHouse fast is it loads a lot of essential data, like indexes, into caches, as well as blocks read from places like object storage.

We have block storage that’s attached to ClickHouse. So this is the original way that ClickHouse worked. Over time, ClickHouse has added the ability to place data in object storage. There are a couple of important ways that it does this. One is to take MergeTree tables and use what’s called S3-backed MergeTree. We’ll talk about that in a minute, where we basically take the MergeTree tables, and we put the table data out on object storage. And we’ll show you how that works in just a little bit.

There’s also a more recent innovation, which is to use open table formats like Iceberg. That’s the principal one that we work with. And these are shared tables that all of your ClickHouse servers can read. So if you have data, this is a way of getting it into a single copy and allowing it to be read not only by ClickHouse but many other applications as well.

So what we’re going to be doing is looking for ways to reduce the amount of block storage, because that’s relatively expensive, and also move things to cheaper types of storage as our data set grows.

So how do you optimize MergeTree tables? Well, ClickHouse is not like Snowflake. It is an open source database, and you can almost think of it as a toolkit for storing data and querying it efficiently. And the reason I say toolkit is it gives you a bunch of ways that you can specify how data is stored very precisely.

So you can think of yourself as an expert craftsman. And what you’re doing is taking advantage of the fact that ClickHouse has a wealth of data types. So for example, Int32 or UInt16. So you can precisely control the size.

You can precisely control the level of compression down to the column level. You can use what are called codecs. A codec is a function that gets applied to data when it’s stored in the table, and it converts it to something that’s typically smaller.

Double delta is an example of a codec, and the way that it works is that instead of storing successive values in a table, it stores the change of the change in the table, if that makes sense. So it’s a double derivative. And what it does is, for values that are slowly or monotonically increasing, it can greatly reduce the amount of storage that you need.

There are things like aliases to fake data, so that you have what look like additional types in the table, or columns in the table, but in fact they’re pointing to other things.

And then within the table as a whole, we can sort the data. So for example we’re ordering things by time here. There are a number of columns, but on the right side we order by time. This can often have huge impacts on the amount of space you use, because it affects the efficiency of your compression. If the data values are correlated, the compression will be better.

So there are a lot of tools here, and so you get to look at tables and kind of decide whether you need to apply these.

14:28 — Compression in Practice: The IP Address Example

Robert: Let me take a specific example. I’d like to look at IP addresses, because this is something that came up a couple months ago in a support case that we were dealing with, where we had a user, and they wanted to reduce their data size on their MergeTree tables by 50%. So we managed to do this. We got it down by 53%, I believe. And it turned out that a lot of it was due to the way that they were storing IP addresses, which were fairly prominent in their data.

So here’s an example. This is a kind of artificial example, but I think you’ll see it shows how big these differences can be.

So you might be tempted, when you drop IP addresses into your data, just sort of as strings. That works perfectly well. You can search on them. Ordering is a little bit tricky, but here we have the IP addresses stored as strings.

But the thing is that ClickHouse actually has a native type for IPv4 and also for IPv6. So the alternatives to using strings are: you can just store it as IPv4, in which case it’s a 32-bit integer. You can store it as IPv4, but you can specify let’s compress it using ZSTD instead of LZ4, which is the default compression in ClickHouse. We can also store it as IPv4, but we can use a delta codec, which means we don’t store the values but store the differences between the values. So that can reduce the data size by quite a bit, and again compress what you get using ZSTD.

So these are four different options for storing IP addresses. It turns out that there are really big differences between them. Let’s have a look.

So what this graph here shows is the actual storage size for the IP addresses. And if we just use the string, that’s going to result in the largest amount of data. This was a sample of about a million IP addresses generated randomly, and they took about 600,000 bytes. And the compression ratio for the strings from the original input data was pretty good. It was a 20x compression rate. That’s good. So you come in with your input data, and when the compression is done, you’re left with 5%.

So let’s compare this to the other types. What we see right here is, if we use IPv4, surprisingly, even though it’s holding it as a 32-bit integer instead of a string, it turns out that that doesn’t compress as well. And the reason for that is LZ4 does not compress integers particularly well.

So what happens is, with IPv4, if we say: “Hey, use ZSTD!” – we’re going to get much better compression. At that point the compression rate is 32. And then if we use a delta codec, we can get the compression ratio to 46.

So this is why we’re able to recover huge amounts of disk space just by looking carefully at the levels of compression that we’re getting in tables, and then picking off different columns and improving them.

How do we find out how big the compression is? The answer to that is system tables. So I’ve been using ClickHouse for a really long time, and I love the ClickHouse system tables. They’re the best of any database I’ve ever used.

This is an example of a query that’s looking for the compression at the level of the table. It’s using a table called system.parts, which gives me exact statistics of all the parts that make up my table. So MergeTree tables in ClickHouse are split into parts, which belong to partitions. So, for example, by month or by day, something like that. And then what I’m doing is just looking for the different sample tables I have and printing those parts, printing the statistics out.

I can also look at the level of the table. There’s a table called system.tables, another great one, and system.columns allows me to zero in on specific columns. So these are tools that you can use, just by writing simple queries, to find out exact levels of compression and then begin to optimize.

19:08 — Optimizing Queries and Finding the Numbers

Robert: While we’re optimizing schema, it’s a good idea to look at queries as well. This also will reduce the amount of compute that you need to process this better schema that you’re creating.

Here’s a simple example of the kinds of variations that you can see. So for example, this query on the left is looking for average departure delay by carrier, for a famous example data set called ontime. It comes back pretty fast, but the interesting thing is it uses an extremely small amount of RAM. And that’s because average is a very small aggregate, and so it requires very little space to hold that while the query is traversing the data.

By contrast, here’s another query where we’re asking for more information, and in particular we’re using a more complex aggregate, uniqExact, that counts the exact number of aircraft that have a particular tail number. And so it basically buckets the data, and it has more group by values.

So between uniqExact being a pretty heavy function for aggregation, and the fact that we also have more group by keys, this in fact is going to take something like a million times more RAM. So it goes from basically one that’s 1600 bytes to something that’s up in the billions.

So this is just a simple example. Yes, these queries are different, but they also show that by asking for more information, or maybe by asking for more information than you really need, you have opportunities to raise the cost efficiency.

One of the things that you can do with uniques, which are kind of a famous example, is you can exchange them for less precise but much faster and much more memory-efficient functions that will do the same thing, but with fewer resources.

How do you find out what queries are actually doing? system.query_log. This is another one of my favorite tables. This is just an example that shows, in fact, this particular query was able to find out those stats that I just printed on the previous page.

Okay. So what can you do if you fix up storage? Savings on storage can be pretty substantial. From table schemas, maybe 70%. I think it varies depending on how inefficiently your schema was designed, or how things have changed over time. So maybe your initial assumptions are no longer correct. Going through and doing this kind of analysis and cleaning things up can yield enormous benefits in terms of cost.

One thing that’s really nice about ClickHouse is most of the changes that I’m describing here can be done on production systems. ClickHouse gives you the ability to do things like change data types, within reason, and do other types of things, such as changing compression. What will happen is you can make these changes and then ClickHouse will just apply them to new parts. So they tend to be fairly lightweight operations.

22:38 — Practice Three: TTLs for Deletion and Recompression

Robert: Here’s another one. So we’re talking about schema, and another thing that you can do is use TTLs. TTL stands for time to live. It is a really, really great ClickHouse feature.

So for example, if you hold data, you typically have some period of time that you really need to look at it, and then after that you just want to throw it away. Now, if you don’t want to do manual cleanup yourself, you can use what’s called a TTL on your table definition.

And this is an example of it, where we have a table which is holding web events, and we add a TTL to this table definition that basically says, once the value that’s in column time is 12 months from the current date, or once the date is advanced by a year, throw it away.

So what this will do is, there’s a background process that looks for rows to TTL out. It will fire, and it will basically wipe out these rows. There is a setting that’s important to know. I don’t have it written down, but it basically tells ClickHouse don’t discard individual rows, but discard whole parts. That’s much more efficient. So that’s something that you can do to prevent load when it’s actually doing this operation.

But this is a key operation. And in fact, one of the places where you definitely want to apply TTLs if you don’t have them already is on your system tables. Things like system.query_log, there’s typically no reason to keep that data more than say 7 days or 14 days. So you put a TTL on it just to drop it as soon as it hits that limit. And this will cause you to use less disk space, but also prevent you from running out if your system tables are heavily loaded.

One of the cool features in TTLs isn’t just dropping data. That’s good, but a much more interesting feature that you can use is what’s called TTL recompress. And this is a really cool innovation, because what it says, and you can kind of read it off the TTL here, we still delete stuff after 12 months, but what we’re going to do is after a month, we’re going to recompress it and use ZSTD1. And then after 6 months, we’ll recompress it to ZSTD10.

What level of compression you use is up to you. One of the recommendations from our support team is that you probably don’t want to go above ZSTD3. But you can play around with it and check the effects.

The interesting thing, though, is when you apply a TTL like this, without doing anything whatsoever, you can see the effects where it will actually change the compression on the parts automatically.

And so this is test data that I generated a while back. These are parts which are 50,000 rows each. And let’s see, system.parts. And you can see that when it first comes in, the initial LZ4 compression is about 600k, and then it drops to half that, and then drops to about two-thirds of that.

So this is a really powerful way to reduce the amount of data that you’re using at practically no cost. What it does mean is when you read this more highly compressed data, it will be more expensive, but the typical pattern that we see is that older data is read less commonly. So that’s a fair trade-off.

So this is a pretty cool feature. How much can you save on it? Well, it’s hard to say, you know, 20%. But you get it practically for free. So it’s very simple to do. This is definitely something you should consider if you have data sets where you need to keep the source data for a long time.

26:52 — Practice Four: Tiered Storage and S3 Backed MergeTree

Robert: Okay. So speaking of hot and cold data, let’s talk about tiered storage. This is another feature of ClickHouse that has existed for many years. Altinity did a lot of the work. We did a TTL move, for example, that was one of ours. We did quite a bit of work on tiered storage, and we use it very heavily, both in our cloud and with our users.

So how does it work? All right, this is a slightly complicated picture, but what it is portraying is the fact that if you have time series data like observability, where you’re reading, for example, log messages or traces, the most common thing you’re going to look at is stuff in the last 24 hours, because you’re interested in something, a problem is happening now, you go look at log messages and try and figure out what’s happened.

If you have a deeper problem, you may extend out, say, look at the last month. That’s pretty common. When I see something where somebody’s running out of memory or they’re seeing particular kinds of messages, I like to know, hey, what’s the pattern of this over time? Problems often, you may have a problem today, but you may need to look back some distance in history to understand the pattern.

And then finally, you may have a small number of queries where you might need to go back over a year of data. And these are sort of more long-range patterns where you’re trying to figure out strategies for dealing with things. They could actually be things like, you might actually be training off it. But this pattern of queries is pretty common in time-ordered data.

So with tiered storage, the idea is we’re going to match the storage type to the pattern of queries on it. And so on Amazon, what we’re typically going to do is use Amazon EBS. You can of course also use SSD, but for many cloud systems we prefer EBS because you can scale it up and down easily.

And block storage has some big advantages. For one, you can dial up the IOPS fairly high. That’s a nice feature of cloud block storage. Also, there’s no cost for accessing it. On the flip side, it does cost more at rest. So, for example, Amazon EBS, the GP3 storage costs about 8 cents per gig per month.

On the other hand, you have object storage which is much cheaper. It’s about four times cheaper. The on-demand price is on the order of about 2 cents per gig for S3 standard storage. And so the at-rest cost is relatively low. You do pay a cost for accessing it. So if you have a lot of access on it, this may not be as cheap as it seems. But in general, for data that’s not commonly accessed, this is a really good storage medium.

How can ClickHouse help you with that? Well, a few years ago ClickHouse introduced what’s called tiered storage, and it uses three entities that you can combine together to arrange the storage in a way that will, in this case, reduce cost.

So let’s start from the bottom. The first thing that you have is you have disks, and you can think of those as mount points that are on the operating system. Every ClickHouse server, when you bring it up, has a disk called default. If you do nothing, that’s the name of this disk, and that’s the mount point that the data goes into by default. You can then define additional disks, and we have one defined here called S3, and that’s actually going to point to a location in S3 storage.

You can then create what are called volumes on top of the disks. In this case, they map one to one, but you can also have volumes that have multiple disks. We call this JBOD, like just a bunch of disks. There are other patterns, but basically you can have several underlying disks then combined into a single volume.

And then you can have, sitting on top of that, a policy. And the policy basically tells you what the relationship is between these volumes, where the data should land, and where it should go next.

So you can set this all up with configuration files. They are done in XML, and they’re a little bit long, but this is a really simple example that shows you the key parts, where you have the disks, which are defined in a separate section, and then they are basically related to each other in the form of volumes in the policies section.

And what this is saying is the first volume is the default. And the way that ClickHouse treats this configuration is that’s going to be where data arrives, and then the next place it’s going to go is to the S3 disk. These storage policies then give the configuration a relationship between the volumes and the underlying disks.

But you have to do one more thing. You have to tell ClickHouse to use it for particular tables. And for that we have TTLs. And the first thing that we have to do, to make the TTLs work is to set the storage policy for the table. So in this case I’m making a table, and I have a setting, storage policy equals S3 tier. That will use my policy, and now I can refer to the volumes in the TTL. And so what this says is basically: after 7 days move any of the data out to S3, and after 365 days delete it.

This is a pretty powerful technique. We also call this S3-backed MergeTree. One thing that’s important: let’s see if I have a picture of it. I don’t. But one thing I need to tell you is that each ClickHouse server preserves its own copy of the S3 data. So you do get a benefit, but it’s not the biggest benefit that you could get, because there’s not a single copy of storage. We have a solution on the way for that. But for now, what you need to do is just do it this way, and you will be able to reduce your cost considerably.

This is very robust and widely used, and the typical savings you get are on the order of 30%, something like that. It really depends on what the distribution is and how long you keep your data.

34:02 — Practice Five: Scaling Down Compute Capacity

Robert: All right. So that is another one on storage. Let’s see what else we can do. Scaling down compute capacity.

And as you can see, a lot of these things are actually things that, we started out looking in detail at schema, but many of these things are more at the level of the system view. How are you using particular resources? So we were looking at storage. Let’s look at things that we can do with compute capacity, because that’s a really important driver of costs.

So with compute, one of the most common things that we do is we scale it up or we scale it down. And a big reason why we use network-attached block storage, or cloud block storage as many people call it, is that it is very easy to switch VMs.

So if you’re running on Kubernetes, this is one of the things that Kubernetes does a really outstanding job at. You can just scale up the VM when you need more capacity, and I’ll show you an example of it in a minute. Kubernetes will kill the current VM, or the pod that’s running inside it, and it will make another one, and it’ll reattach it to the storage.

That also applies, of course, when we want to reduce capacity. So here’s a Graviton VM that’s running on Amazon in US East 2. And what we might want to do, we detect that, hey, we really don’t need all the capacity, it’s mostly idle. What we’re going to do is reduce this by a factor of four in terms of the number of vCPUs, and that will reduce our price by 75%.

So this is a pretty simple operation, and it ends up attached to the same storage. How does that actually happen? Well, what’s cool about Kubernetes, and why this is so important to doing this easily, is that Kubernetes is wired into the underlying cloud infrastructure and it has the ability to allocate new pods. And when it does this, it will also automatically commission and decommission VMs for you. So Karpenter is a common way to do this on EKS, which is on Amazon, and you can use node groups.

The other thing it can do is it provisions storage for you. So Kubernetes is a great substrate for running on this, because it has this wiring that automatically scales resources up and down.

We can take advantage of that when we’re running on Kubernetes and using the Altinity Kubernetes operator, which is widely used for provisioning ClickHouse clusters on Kubernetes.

So here’s an example. It uses a simple resource definition called a ClickHouse installation. And this is the top part of that. They can be relatively short. This one just says, hey, we have a pod. It’s going to run in US East 2A. So it refers to a template, and that actually gives us the pod definition here. This is in the 2A availability zone.

And a key thing right here is you can see we have the m8g node type, and we also have some stuff that says which zone we want it to appear in. If we want to change the node type to scale it up or scale it down, we simply change that m8g.xlarge to whatever we want it to be, resubmit the resource, and Kubernetes does everything else for us.

So this means that this change is relatively simple. And the Altinity operator, which is really orchestrating this, also takes care of making sure that if we have multiple replicas, we do them one by one, so your applications don’t see this happening. We make it as transparent as possible.

So patterns for using this compute rescaling. A good example is if you find that demand has gone down, or you have particular times when you know that you’re going to have lower demand, you can just scale down. The operation on large clusters takes a while, because we typically back each ClickHouse with its own VM, that’s a best practice. But if you know that demand has just gone down in general, you can reduce replicas this way.

Another way that you can use this, and I’ve seen this used in many clusters, is actually to split the hot and cold data into separate clusters. And for those colder clusters you just have less VM capacity, because the queries are rarer and so there’s no need to provision a lot of capacity on it. You instead allocate smaller VMs and reduce costs that way. And depending on the balance between the hot and cold data, that can result in substantial savings.

Another thing you can do is just turn off compute completely. With the Altinity operator, it’s actually pretty simple to do this. There’s a blog article that describes it. Basically, you can go in and do a kubectl edit on the replicas and set the number of replicas to zero, and then what’ll happen is it’ll just stop the instance.

In Altinity.Cloud, we do this automatically, by default After an hour or two of idleness, we’ll just shut the VMs down. This is great for development systems. You obviously wouldn’t want to do it for production.

So when you’re making these types of scaling decisions, how much can you save? Well, it really depends. You have to evaluate the compute. You don’t want to put things too low, because you might then have a spike in capacity and not be able to meet it. But you could save perhaps 40% on your compute bill if you use it effectively, and especially if your system is overprovisioned.

40:27 — Practice Six: Bring Your Own Cloud

Robert: So we’ve taken kind of a tour of the different approaches that we can use that involve going in and changing resource usage. Of course, we looked at tables, how can we go in and perform optimizations on the schema?

There’s also a really important way that we can change the cost profile of ClickHouse, and we do this at the level of the entire system. So let’s just talk about bring your own cloud. The simplest thing to do is to show you a picture.

In the beginning, when databases moved to the cloud, the vendors put them in a SaaS. And the analytic database that first did this was Redshift, and what it did was it provisioned Redshift VMs and then made them available to you.

Over time, this has evolved to a pretty standard architecture, and this is how we built Altinity.Cloud, but many other clouds use it as well. You’d have the ClickHouse clusters running in the vendor account. They would be running on top of Kubernetes using something like the Altinity operator, and then the Kubernetes would be wired through to be able to allocate and manage the storage, the compute, and the networking.

And so, in fact, what you’re looking at is a picture of the main components of an Altinity.Cloud environment, which is where user clusters go. So these are dedicated.

Now, the problem with that is that if you’re putting that in a vendor’s account, it’s convenient because they set up everything for you and just give you an endpoint and you work, but you can’t really control the costs. And one of the things that we were working on Altinity.Cloud, within a very short period of time, we had requests from people to let them run it in their own environment.

So how do you do that? It’s called BYOC, but basically the way that it works is you give us access to, and sort of keys to the kingdom. In Google, you might have to, you’ll create a project for us. In Amazon, you may have to run a script, or for example a Terraform script, to provision the stack, where you provide the permissions to do it.

But what you set up is a soup-to-nuts VPC which includes, in the case of Amazon, it would have Kubernetes running on EKS. It would have all the correct wiring to the storage, compute, and networking, and then it would let you manage the ClickHouse clusters.

So you get a VPC that is in your own environment, so you can see it, control it, and you can prevent it from being accessed from the outside. But it’s being managed from the cloud, and in fact in Altinity.Cloud you cannot tell the difference from a management perspective between something running in your own account and something that’s running in ours.

So this is one common pattern. I sometimes call it pure BYOC. We just do everything from soup to nuts. It’s very convenient, but there’s less control. So you may work in an organization that actually wants to control a little bit more, like, for example, prevent us from having privileges that allow us to create subnets or VPCs. These are very powerful, and depending on the authorization model, they can be global across your entire account.

So what we also have, and this is another pattern of BYOC, you can just supply Kubernetes. And in this case, the bottom two layers, the storage, compute, and networking, and the wiring into Kubernetes, you as the user take care of that, and you simply drop a container into that Kubernetes, which will then register the Kubernetes cluster with Altinity.Cloud. And at that point we can again manage the ClickHouse clusters.

So you still have the convenience of the clusters being managed. We do all kinds of monitoring and alerting and things like that, so you don’t have to manage that part. But you do have responsibility for managing Kubernetes, making sure that node types are correctly set, if Kubernetes needs to be upgraded, that’s for you to do, so on and so forth.

So these are two models that enable BYOC, and they’re becoming increasingly popular. This is actually one of the fastest-growing areas of management of databases. And the reason for that is it allows you a degree of control over costs, in addition to other things, that’s profoundly different from running inside a vendor’s stack.

So what does that mean? Well, let’s give some examples. So you can play around with storage. This is a very simple example. For example, if you have Amazon GP2 storage, you want to convert everything to GP3. That’s lower cost. But there are also many other flavors of storage. You have the freedom then to choose which ones you want to use and use those, for example, as the default storage type. This is something you can set simply with a small amount of configuration within Altinity.Cloud.

So that’s handy, but it actually gets a lot better. Compute is something that you have enormous control over the costs, because you can do savings plans. And here’s a simple example of how big the savings can be.

So we typically, when we’re provisioning large systems, we’ll do savings plans on the VMs. And if you do a one-year committed use, you don’t have to do prepaid, you get 27% off the cost right there. If you know the system is going to be around for a while and you’re willing to do a three-year plan with prepay with Amazon, a pretty typical price break is 54%.

On top of that, you may have private pricing agreements. So these are general pricing agreements that affect the price of all resources. For large companies, these can be substantial. So you can see price decreases in VMs on the order of 60 or 70%. These are really big differences. You also get breaks on storage, and you can use these because it’s in your environment. You can also use, if you’re a startup and you have startup credits, say from Google or Azure or Amazon, you can of course use those as well.

47:34 — Low-Cost and Specialized Infrastructure Providers

Robert: Another thing you can do, and this is really important, is you can go to low-cost infrastructure providers. So, Hetzner, you can run Kubernetes on Hetzner. We do that. We do have a managed BYOC on Hetzner, and so you can deploy into that environment.

Oh, I see a question about something new coming with respect to S3 tables. I’ll get to that in just a minute.

So here, Hetzner is a good low-cost provider. It used to be 90% cheaper than Amazon. They did a kind of abrupt pricing change earlier this week. Now, last time I looked, it’s about 65%. But these are definitely low cost. You’re making a trade here, because they are not as reliable as Amazon, but they’re still very, very capable, because there are many workloads that just need to be cheap. It doesn’t matter if they have higher failure rates, for example.

What else can you do? Well, Hetzner is well known, but there’s also an emerging crop of specialized high-performance clouds. One of the ones that we’re partnering with is Nirvana Labs. What’s really cool about them is they’re very much based on, or coming out of, the web3 crypto analytics field, which is very focused on low-cost, tightly integrated compute.

With Nirvana, it’s a cloud, but you can collocate your applications, your data, and the things that generate the data like crypto nodes. You can collocate them within a rack, connected by a single top-of-rack switch. Moreover, they’re much cheaper, and the access to storage is very, very fast. So the Nirvana block storage is much faster than things like Amazon GP3. It’s also cheaper.

So these are things that you can now use with BYOC, and we’re specifically targeting these environments, which give you not only better cost but also higher performance as well.

So BYOC, because it operates at the systems level, the savings can be small if you’re just playing around with storage types, optimizing instances. But as you begin to apply savings plans, go to low cost providers, the savings can be substantial.

50:24 — Summary and Questions

Robert: Okay, so here’s a quick review of the different types of things. I hope I got the numbers right. They are kind of guesses.

Developing on a laptop with open source, there is a big upfront advantage to that, especially now that things like Claude Code and Codex are available. Long-term it’s not the biggest impact on your cost, because in large systems it’s driven by infrastructure. So things like optimizing MergeTree tables and bringing your own cloud, these deliver substantial improvements in costs.

MergeTree tables is particularly important, because if you’re using block storage, and many applications need block storage because it’s fast and very efficient, optimizing the MergeTree tables should be the first thing you go to. Bring your own cloud, particularly if you go to low cost providers, can also have enormous impacts on cost. Scaling down capacity when it’s not needed, using tiered storage to S3 and TTL, these are other things that can be put into the mix.

And there was a question. You mentioned something new coming with respect to S3 tables and the current need to have dedicated object storage per replica. Can you expand on that?

I’d be glad to. So in the next release of Antalya, which is 26.6, it will be out this month, we will have a new type of MergeTree storage. It’s called CAS, which stands for content-addressed storage. And what it does is you can put it into tiered storage and it works just like all the other MergeTree storage types, except that the data is stored in a single copy on S3.

So there may be some of you who are around and remember zero copy replication. This was an earlier implementation that attempted to use a combination of information on disk, information in ZooKeeper, and then S3, to have a single copy of storage. CAS is a new implementation that solves this problem in a much better way.

So key things about CAS. It is a single copy of data, and it’s an extension of the existing MergeTree model. So you don’t have to do anything special, you just add it as a tier. It puts all the data on S3. This includes all the metadata. So everything that is needed to understand what’s going on in storage is stored in S3. That means that if you have a ZooKeeper crash or you lose file systems, you don’t lose track of who the files belong to and which tables they’re organized under.

And the final thing is, the way that it keeps things consistent is it has a garbage collection algorithm which goes out and, from time to time, looks at who’s using which S3 files in which tables, and identifies files that are orphans and just purges them automatically.

This will be coming out in experimental form, as I say, this month. We have customers already lined up to try it out. If you would like to be one of them, please give us a yell. We would be delighted to help you with that. We won’t be making a bigger public announcement until we get more time in on testing with real data. It’s looking pretty good in our in-house tests. So we’re very excited about this.

And one of the things, as Nathan is bringing up here in the questions, does that mean you can add and remove compute nodes dynamically? Yes. It won’t be completely convenient in the initial implementation. It may take a few minutes for the nodes to be added, but it is going to be vastly faster, because there’s no longer any need to share the actual data itself. It’s just basically having the equivalent of the node come up and boot up and understand what storage it’s looking at.

So this is actually going to be a sea change in the way we scale data. We’re really excited about it. We’ve been working in storage for years and this is a knotty problem. I think we’ve finally got the right design for it, and I’m really, really looking forward to this build.

Let’s see. So we’re running over. You can learn more, here’s some interesting information. And yes, if you’d like to go ahead and learn about BYOC, go ahead and check into that. And then we also have the option to contact me directly on LinkedIn if you’d like to find out more. I’m a database geek and I love working with this stuff. So feel free, I’ll stay around for a couple of minutes if there are further questions.

Oh, hybrid tables. Okay, great. Kayatan, thank you so much. Are hybrid tables integrated with TTL, something feasible to happen this year? This could be something useful for big data platforms, data retention.

Absolutely. Yes. Okay. So yeah, why don’t we have TTLs already? Well, that’s kind of the question I ask myself, but here’s what hybrid tables are for people who don’t know them. It’s a new engine that we introduced in Antalya, and it allows you to take different tables that are actually defined even in different places and different storage types. So, for example, MergeTree tables and Iceberg tables, and you can combine them so that they look like one single table. And you do a query on the hybrid table, and it goes to both of them, or more, you can have multiple segments.

So the obvious thing is you’d like to have TTLs, and we would too. What we have been focusing on is ensuring we can do a very efficient export of data out of MergeTree tables into Iceberg, because that’s the key to publishing. We have implemented that for replicated MergeTree. It’s out. It works. You can use it with hybrid tables but there’s no TTL built in.

The next step that we’re working on right now is to get the export to work correctly for plain MergeTree tables, because obviously people use those as well. And then the next step after that is to do TTLs.

And if you are interested in this, we would really like to talk to you, because it turns out that TTLs across different storage types have a number of special cases, and we’d really like to understand them properly so that we can get the implementation right. So please feel free to contact me directly. I’ll put you in contact with the development team, and we’d love to work with you to get this to work.

Okay. So I don’t see any other questions. Let me just check the chat. One second. Unfortunately, you can’t really. All right. Oh, I got questions in chat. Oh my goodness. Yeah, CAS sounds really exciting. Yeah, thank you. I really appreciate that.

So, good. I don’t see further questions. Thank you all so much. And I’m sorry for the mishap on starting. That doesn’t happen very often. I guess that’s our accident for the decade. But you will get a copy of these slides, and feel free to contact me or Altinity in general. We’d love to, if you’re interested in this and would like to hear more, we’d be delighted to talk with you.

Thank you very much and have a wonderful day. Talk to you soon. Bye.

FAQ

What are the six best practices for saving money on ClickHouse?

Develop locally on your laptop with open source rather than paying for cloud resources during development. Optimize MergeTree table schemas using appropriate data types, codecs, and sort order. Apply TTLs to delete old data and recompress data you need to keep. Use tiered storage to move cold data from block storage to much cheaper object storage. Scale compute capacity down when demand drops, or turn it off entirely for development systems. And use Bring Your Own Cloud so you can apply savings plans, private pricing agreements, and low-cost infrastructure providers.

Which practice saves the most money?

It depends on where you are. Developing on a laptop with open source has the biggest upfront advantage, effectively eliminating licensing and startup costs, but it matters less long-term because large deployed systems are driven by infrastructure spend. For deployed systems, optimizing MergeTree tables should be your first stop, particularly if you rely on block storage. Bring Your Own Cloud can deliver comparable or larger savings, especially combined with low-cost providers.

How much can schema optimization actually save?

In one support case the customer wanted a 50% reduction in MergeTree table size and the result came in around 53%. Much of that came from how IP addresses were stored. In a test using a million randomly generated IP addresses, storing them as strings gave a 20x compression ratio. Switching to the native IPv4 type with ZSTD raised that to 32x, and adding a delta codec raised it to 46x. Savings from table schemas can reach roughly 70% depending on how the schema was originally designed.

What is TTL recompress and how is it different from a normal TTL?

A normal TTL deletes data once it passes a defined age. TTL recompress instead changes the compression on parts as they age, so you might apply ZSTD1 after one month and a higher level after six months, while still deleting at twelve. Reading the more heavily compressed data costs more, but older data is typically read less often, so the trade-off usually favors the saving. The Altinity support team suggests not going above ZSTD3 in most cases.

How much cheaper is object storage than block storage?

Roughly four times. Amazon EBS GP3 runs about 8 cents per gigabyte per month, while S3 standard on-demand pricing is on the order of 2 cents per gigabyte. The important caveat is that block storage has no access charge, while object storage does, so heavily accessed data may not be as cheap as the at-rest price suggests. Typical savings from S3-backed MergeTree land around 30%, depending on your data distribution and retention.

What is CAS and when is it coming?

CAS stands for content-addressed storage, a new MergeTree storage type arriving in Antalya 26.6 in experimental form. Unlike the current S3 backed MergeTree, where each server keeps its own copy of the S3 data, CAS stores a single copy along with all its metadata on S3, so a ZooKeeper crash or lost file system does not leave you unable to identify which files belong to which tables. It includes a garbage collection algorithm that identifies and purges orphaned files, and it should make adding and removing compute nodes considerably faster.

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