Building a Serverless ClickHouse

Building a managed ClickHouse offering has always been hard. On top of the day-to-day problems of running a complex technology at scale, the company behind it runs its own cloud, and some pieces only exist there.

One of the biggest changes I’ve seen in ClickHouse is SharedMergeTree, a table engine that stores data in S3 and keeps its metadata in ClickHouse Keeper, which gives the compute and storage separation many users were asking for. It’s only available in ClickHouse Cloud. In the open source version the closest thing was zero-copy replication, which never became production ready and was later deprecated and removed.

Everything seemed lost, but then a new kind of disk appeared in ClickHouse: plain_rewritable, which stores both metadata and data directly in S3. It came with a lot of caveats¹, but it opened the door to compute and storage separation in the open source version.

At the same time, working at Tinybird, I always had the feeling that while we made ClickHouse much easier to use, people saw us as one more API tool and not as a real database.

So I tried to imagine the simplest possible way to put together a managed ClickHouse offering under these three premises:

• Compute and storage separation using S3 on plain_rewritable disks
• An interface that feels like a database in the cloud: one endpoint, the native protocol, and the client you already use
• Trivial to start. No need to sign up for anything before trying it²

This might look like a pitch, and ClickHamlet is a real service you can try, but this is a technical post about how I built everything behind it.

The Idea

The central idea is a router that looks like a ClickHouse server from the outside. You connect with the client you already use, and behind it there are regular ClickHouse servers that only run while there is work to do. None of them own data: every table lives in S3 on a plain_rewritable disk, so any server can open it. That gives a few rules:

• A service that speaks the native protocol, so any ClickHouse client works
• Compute that starts on demand and stops when idle, so an unused database costs as little as its storage
• A single writer per service, because plain_rewritable can’t handle more yet
• Readers to scale reads horizontally (for now, one reader per service)

The first piece I built was the router, and the first thing to prove was compatibility: if clickhouse client doesn’t believe it’s talking to a real ClickHouse server, nothing else matters. The protocol looks complex, but you don’t have to write it: linking against ClickHouse’s own dbms library gives you the protocol handling and the SQL parser. So the first version was quite simple and did almost nothing: answer the handshake, accept any user and password, parse the query, and return a one-row table with its type.³

❯ clickhouse --port 9001
ClickHouse client version 26.9.19.9 (official build).

hello :) SELECT 1

Query id: d92c18b7-fae3-410c-836f-e665d0095ae1

   ┌─type───┐
1. │ Select │
   └────────┘

1 row in set. Elapsed: 0.000 sec.

hello :) EXPLAIN SELECT 1

Query id: f37c15a5-a3d6-413e-b4a5-a0e8d8183cb6

   ┌─type────┐
1. │ Explain │
   └─────────┘

1 row in set. Elapsed: 0.000 sec.

hello :) Bye.

It’s simple, but that one column is the key to everything else. Once the router knows the query type, it knows whether a reader or the writer should run it.

Routing a Query

You could argue whether we need a module written in C++ sitting on top of ClickHouse’s codebase just to extract the query type and forward it to the right backend. In theory the job is easy and detecting if a query is a select or an insert shouldn’t be that difficult to do in any language. But there are a few reasons why I decided to implement it this way.

• You don’t only need to parse the query and decide the type. You also have to speak the ClickHouse native protocol, with all its quirks
• Parsing the entire query and extracting the full AST also enables smarter routing. For example, you can extract the affected tables and use this information to route to one backend or another
• Some queries (like SELECT 1 or SELECT version()) can be answered without needing a backend
• We have to keep some session state in the router when a query comes with USE or SET statements, since the next query may go to a different backend
• Using ClickHouse’s own parser means new syntax works out of the box, with the exact same implementation
• Parsing a query can get expensive. Once you have the AST, you could skip parsing it again on the backend⁴

So the router is in C++. The other piece is what I’ve called the controller, where the business logic lives. It can be written in any language as long as the hot paths are fast enough (more on this later). Its job:

• Authenticate and authorize calls
• Start and stop backends when needed
• Return the backend, and the settings to mount the required tables

A basic block diagram:

And this is the flow of a warm query, when the backend is already running:

The hard part is deciding where every query goes and making sure there’s a single writer per service at any time.

Readers and Writers

Every service gets a writer and, optionally, a reader. Both are regular ClickHouse servers started from the official image. The only difference between them is how they see the service’s bucket.

The writer owns the bucket. Its tables and its databases’ metadata live on a plain_rewritable disk that covers the whole bucket, so from the writer’s point of view it’s just a server with an S3 disk. Every INSERT, every DDL statement, and every SHOW or query on system tables goes there, since it’s the only backend that knows about every table.

The router doesn’t try to be clever on this path. It sends the original query text, not the AST it parsed⁵, together with the session’s settings, and relays everything back to the client as the backend writes it. An INSERT streaming a big file, a long query reporting progress, or a Cancel in the middle of either, all behave the same as against a real server. Something I didn’t expect is that the router has to decode every block it relays, because the client and the backend each negotiate their own protocol revision and compression. So it has to understand every type it might see, AggregateFunction columns included, which means linking ClickHouse’s aggregate functions too. One more reason to build on top of ClickHouse’s code.

Readers start empty. They have no storage configured and no idea which tables exist. The controller keeps a registry of the writer’s tables (name, UUID, location in the bucket and the CREATE statement), updated after every DDL and every time the writer starts. When a query goes to a reader, the controller returns the tables it touches that the reader hasn’t attached yet, and the router turns each CREATE into an ATTACH before running the query:

ATTACH TABLE IF NOT EXISTS default.events UUID '…'
(
    `id` UInt64,
    `at` DateTime
)
ENGINE = MergeTree
ORDER BY id
SETTINGS disk = disk(type = object_storage, object_storage_type = s3,
    endpoint = 'https://<service bucket>/', include = 'bucket_key',
    metadata_type = plain_rewritable, readonly = 1),
    refresh_parts_interval = 5

The table keeps the writer’s UUID, so the reader finds its data, and the disk is defined inline and read-only, so a reader can mount tables from anywhere without any configuration of its own. The bucket’s credentials never show up in SQL: include = 'bucket_key' reads them from the reader’s config file.

refresh_parts_interval is what keeps readers up to date: every 5 seconds the reader lists the table’s location and picks up new parts. This is the main trade-off. A reader can be up to 5 seconds behind the writer, even in the same session that just inserted the data. I started with 2 seconds, but every refresh is a LIST, and S3 bills a LIST like a PUT. It can get expensive.

Then there’s THE rule: a single writer. plain_rewritable has no coordination between servers, so two writers on the same bucket could corrupt all the data in a few seconds. The simplest way to enforce it is one writer per service, not per table.⁶

I never built a lease for it, because Kubernetes already has one: a pod’s name. Writer pods have a fixed name per service, Kubernetes only allows one pod with a given name, and a deleted pod keeps its name until the server is dead, unless you force the deletion, which the controller never does. So the controller refuses to start a writer while the previous pod still exists. The trade-off here is availability: if a node goes silent, its services can’t get a new writer until the node comes back or is removed from the cluster.⁷

Inside the controller, there’s also a lock per service. Every request for a backend takes it, so two queries arriving at the same time can’t both decide to start a writer.

Starting and Stopping

All of this assumes the backend is already running. When it isn’t, the first query pays the price for starting it. The controller creates the pod, waits for it to be ready and reachable, registers the required tables, and then returns its address. That takes a few seconds.

The whole start runs under the service’s lock. For the client, it’s just a slow first query: the router waits up to 300 seconds for the controller, as long as clickhouse client waits for a server.

Backends stop on their own too. Every minute the controller checks in-flight queries: a backend running a query is never idle. Otherwise its idle time counts from the last query the router sent it. By default readers and writers stop after 5 minutes without activity⁸.

Readers can just be stopped: they can’t lose anything other than in-flight queries, and we already checked there are none. Writers need to drain first: the controller stops their merges and waits until there are no merges, mutations, moves or asynchronous inserts left, up to 5 minutes by default. If a query arrives in the meantime, merges get started again, the drain gets cancelled and the writer keeps serving.

Services and the Controller

A service is just a user. The first time someone connects with a new user name, the controller creates a service with that name and password. There’s no sign-up form or API key. One clickhouse client command and you have a database. The obvious downside is that a typo in the user name creates a new service instead of failing. I can live with that.

❯ clickhouse client --host cloud.clickhamlet.com --secure --user acme --password s3cret
Warnings:
 * Service created with name: acme
 * Open your dashboard at https://cloud.clickhamlet.com/dashboard/… and sign in before … to keep this service, or it will be deleted

That warning is how claiming works. Until someone owns the service, every login prints a claim link. The first person to open it and sign in becomes the owner, and from then on logins print the link to the service’s dashboard instead. Unclaimed services are deleted after two days.

The controller is a single Python process. It knows about services, their passwords and settings, the table registry, and which backends are running.

Every service is isolated from the others. Each one has its own writer and reader, its own S3 bucket, and its own IAM user that can only touch that bucket. The user is created under a permissions boundary, so not even a compromised controller could give it more access. The key never appears on a command line, in SQL or in the environment: backends get it in a config file for their disks. A backend has no credentials of its own, so a service’s s3(...) calls can’t sign requests with the bucket’s key. And backends only accept connections from the router, with a password generated per service.

The rest is enforced by ClickHouse itself. With table_engines_require_grant, a service can only create MergeTree-family tables, views and materialized views, so every table lives in the bucket and nothing ends up on a backend’s local disk. file, url and remote are revoked too, since url and remote could reach the host’s other processes or other services’ backends.

Making It Fast

Once everything worked end to end, I wanted to know how much it cost to route a query. As usual, the answer was too much. The first benchmark, 16 clickhouse client sessions running 300 SELECT count() each through the router, did 451 queries a second.

After a handful of changes, the same benchmark does 2713 queries a second, 6x the baseline, and the limit is now the test service’s writer, not the routing.

Throughput alone hides where the time went, because the last bars are capped by the writer. CPU per query by component is more useful: the controller went from 2.34 ms to 0.30 ms.

Most of the work was in the controller, and most of it was removing things it did on every call without needing to:

• Each /backend call read every running backend’s pod from the Kubernetes API, to check it was alive. The read took about 1 ms, but turning the whole pod into Python objects to look at one field was 40% of the call, under the service’s lock. Now only the checks every minute read pods, and a crashed backend is noticed by the first query that fails.
• The service row was read from SQLite several times per call. Now it’s read once and passed along.
• The router opened a TCP connection to the controller for every query, and accepting and closing them was 13% of the controller’s time. Keeping them open only paid off after a TCP_NODELAY fix.
• Under load, the controller saturated at one core of Python, and FastAPI’s overhead was most of each call. The four routes the router calls are now plain routes, while the dashboards stay on FastAPI. That change alone took the benchmark from 1201 to 2200 queries a second.
• Every thread had its own SQLite connection, so reads would never wait for writes. But Python releases the GIL inside each SQLite call and takes it back after, so 16 threads reading at once spent their time handing it to each other. It looked like a SQLite problem, but it was the GIL. One shared read connection behind a lock fixed it.

The last change was in the router, which opened a new connection to the backend for every query. Besides the CPU, every closed connection kept a port in TIME_WAIT for a minute, so past about 470 queries a second to one backend the router ran out of ports and queries failed with “Cannot assign requested address”. That explained a few dropped connections I had seen and never understood. Now the router pools its backend connections, shared by every session.

This is where a single /backend call spends its time, before and after:

Some of the work was about cost, not speed. ClickHouse writes about a dozen objects for every small part, and deletes them one by one after a merge. The internal service that records usage, inserting about 400 rows an hour, was making 5500 PUT-class requests an hour. Packing small parts into a single object (min_bytes_for_full_part_storage, the same approach ClickHouse Cloud uses) cut that by 69%.

Running It

Everything started running on a single EC2 instance. The router, the controller and Caddy ran as systemd units, and the backends ran as pods in k3s on the same machine. The only thing that left the instance was data: each service’s bucket, and the controller’s SQLite database, streamed to S3 by Litestream.

Kubernetes on a single machine sounds like overkill, but the backend layer was already turning into a small Kubernetes: limits, admission, crash detection and addressing. Before it, the controller started clickhouse-server processes itself and was growing code for cgroups and free ports. Moving to pods deleted all of that, and it turned the next step into mostly a configuration change.

That next step was moving only the backends to a managed cluster (EKS) next to the instance. The router and the controller stay where they are and keep reaching backends through node ports. The controller just got a kubeconfig for a different cluster. What changes is that backends are no longer capped by one machine: the node group grows when a backend doesn’t fit, and can shrink to zero.

Every backend runs on spot instances, writers included, with on-demand only as a fallback. A Graviton r7g.xlarge (4 vCPU, 32 GiB) costs $0.25 an hour on demand and around $0.10 on spot. Backends are a good fit: they hold no data, and a reclaimed node looks like a crash. AWS gives two minutes’ notice, the node is drained, running queries fail, and the next query starts the backend on another node. Acknowledged inserts and finished merges are already in S3. The single-writer rule still holds, because the replacement only starts once the old pod, and its name, are gone. That’s also why spot needs a proper drain: a node that just disappears keeps its pods, and their services can’t start a new writer until the node is removed from the cluster.

Phase 2 is only a plan, for when one instance isn’t enough for the router and the controller themselves: a load balancer in front of several routers that send queries straight to backends, and a controller that places backends on hosts and grants each writer a real lease. None of it exists yet.

Pricing is simple. Free services have a 10 GiB storage cap and share resources. Paid accounts pay for what they use: $0.2181 an hour per 8 GiB of running server, drain included, and about $0.025 per GiB stored a month. The controller records usage every minute in an internal service (dogfooding baby!), and an hourly job reports it to Stripe.

Where It Is Now

It works! You can point clickhouse client at it, create tables, insert and query, and pay nothing while you’re not using it. It’s still small on purpose: about 1400 lines of C++ for the router and 2800 of Python for the controller.

Building on ClickHouse instead of around it gives you most of a database service for free. The protocol, the parser, settings, sessions, access control and the storage itself are all ClickHouse’s code. ClickHamlet mostly decides where a query goes and when a server should be running.

plain_rewritable still has important limits. One writer per bucket is the big one, and these disks also reject schema changes like ALTER ... ADD COLUMN and mutations. ClickHouse 26.10 can lift the second with enable_hard_links, off by default and not used by ClickHamlet yet, and the first has work in progress upstream.

What’s still open:

• More than one writer, once ClickHouse supports it, or at least one writer per table instead of per service.
• Schema changes and mutations, with readers re-attaching tables whose definition changed.
• Autoscaling.
• Faster cold starts, now that backends run on an autoscaled cluster, where a start may wait for a new node.
• A real writer lease, once there’s more than one controller.

If you want to try it, all you need is clickhouse client:

clickhouse client --host cloud.clickhamlet.com --secure --user <service> --password <password>

Footnotes

1. The main blocker was (and still is) not being able to have multiple writers into a single table. There is a work in progress PR to solve this. ↩

2. Let’s make bots (and humans) happy: an agent can create a database without anyone signing up. ↩

3. There’s a working example you can build and run in this repo. ↩

4. They even have a dialect for this: the 26.8 release post introduces a JSON AST dialect. ↩

5. See footnote 4. There are plans to improve this, but the current version is as simple as it can be. ↩

6. We can later make things more complex and increase granularity to table level. ↩

7. The controller pings every backend every minute and replaces the ones that stop answering, but a node that goes silent is still a failure point in the setup. ↩

8. On paid plans this is configurable: anything from 5 minutes to a day, or keep your writer always running. ↩