Category: Databases
Managed ClickHouse: real-time analytics in your own account
Queries over billions of rows in seconds, on a ClickHouse that lives in your cloud account, with replicas, backups that restore and a table design built around your real queries.
- Where it runs
- In your account, in your name
- Backups
- With tested restores
- Updates
- Planned and documented
- Licence
- Open source, Apache 2.0
In short
ClickHouse is an open source, column-oriented analytical database that answers queries over large volumes of data in real time. We install and run it in your cloud account: MergeTree table design, replicas with ClickHouse Keeper, S3 backups restored in tests, TTL-based retention, resource limits, metrics and updates, all as code in your repository.
What do we look after?
ClickHouse is very fast when the tables are well designed and very frustrating when they are not. A good part of our work is that design; the rest is what any production database needs: replicas, backups, limits and upgrades.
MergeTree tables
Engine, ORDER BY and partitions chosen to suit your queries.
Replicas and Keeper
ReplicatedMergeTree coordinated by ClickHouse Keeper on three nodes.
Backups
Full and incremental BACKUP to S3, with a test restore.
Limits
Profiles, quotas and maximum memory per query and per user.
Metrics
System tables and a metrics endpoint for Prometheus.
As code
Configuration and schemas versioned in your repository.
How do we design the tables?
In the MergeTree family, ORDER BY defines how data is sorted on disk and the sparse index that lets whole blocks be skipped. We put first the columns you filter on most, from lowest to highest cardinality, and the date near the end. Partitioning, usually by month, is for deleting or moving old data in one go, not for speeding up queries: partitions that are too fine cause problems.
Depending on the case, we use variants: ReplacingMergeTree to keep the latest version of each row, AggregatingMergeTree with materialised views for precomputed totals, or TTL to delete or move to cold storage the data that is no longer queried every day.
Which architecture do we recommend?
| Situation | What we set up | Why |
|---|---|---|
| Internal analytics, a short outage is tolerable | One server and daily backups to S3 | Fast and simple; recovery is tested |
| Production that cannot stop | Two replicas and ClickHouse Keeper on three nodes | If one replica goes down, the other keeps answering |
| Volume that does not fit on one server | Several replicated shards and a Distributed table | Spreads data and queries across servers |
Keeper always runs on an odd number of nodes, three at least, and preferably on machines separate from ClickHouse when the load is high. A replica does not protect you from an accidental DROP TABLE: the deletion is replicated, so backups are independent of replicas.
Which problems do we come across most often?
- Too many parts: small, constant inserts that overwhelm the merges and end up rejecting writes.
- ORDER BY copied from another database: a unique identifier first, and every query reads the whole table.
- Queries with no memory limit: a report with a huge JOIN leaves the server, and everyone else, out of memory.
- Keeper on a single node: if it goes down, the replicated tables become read-only.
- No retention: the disk fills up with years-old data that nobody queries.
- Backups that have never been restored: the official documentation warns that, if you have not tested it, it probably will not work when you need it.
How do we handle backups and upgrades?
We use ClickHouse’s BACKUP command with an S3 destination: a periodic base backup and incremental backups that only store what is new. The storage sits in another account or with another provider, with rules that prevent it from being deleted early. Every so often we run RESTORE on a separate server, compare row counts with production and note how long it takes.
ClickHouse publishes releases every month and, twice a year, LTS versions with longer support. For production we recommend LTS. We upgrade one replica at a time, check that it catches up and move on to the next. Beforehand we review each version’s breaking changes and run your real queries against a copy.
How does it fit with what you already have?
ClickHouse is fed by what your system already produces: application events, queues, PostgreSQL exports or logs. We set up batch ingestion, monitor the merges and connect Grafana for the dashboards. If the data comes from a relational database, we manage it as in managed PostgreSQL, and ClickHouse’s metrics go to monitoring with Grafana and Prometheus.
Where do we set it up?
In your account, with the provider you already use or the one that fits best. If the data has to stay in the EU, Hetzner and OVHcloud are the European options.
Providers we set it up on
- Hetzner
- AWS
- Google Cloud
- Azure
- DigitalOcean
- OVH
- Own server
| Provider | How we set it up | Things to bear in mind |
|---|---|---|
| Hetzner | Dedicated or cloud servers with ClickHouse and ClickHouse Keeper on a private network. Backups and cold storage on Hetzner Object Storage, which is S3-compatible. | No managed service; dedicated servers with NVMe disks give a lot of analytical performance for the cost. |
| AWS | EC2 with gp3 or local NVMe disks, Keeper on three instances and backups to S3. | AWS has no ClickHouse of its own; ClickHouse Cloud is contracted separately, with its own account. |
| Google Cloud | Compute Engine with persistent disks, Keeper on three machines and backups to Cloud Storage through its S3-compatible interface. | No service of Google's own; ClickHouse Cloud is also offered on Google Cloud. |
| Azure | Virtual machines with managed disks and backups to Azure Blob Storage, which ClickHouse supports as a destination. | No service of Microsoft's own; ClickHouse Cloud is available on Azure. |
| DigitalOcean | Droplets with block storage and backups to Spaces, which is S3-compatible. | No managed ClickHouse; a good fit for moderate volumes. |
| OVH | Managed ClickHouse from OVHcloud Public Cloud, or instances and dedicated servers with ClickHouse set up by us. | |
| Own server | ClickHouse on your servers with fast disks, Keeper on three machines and backups to external S3 storage. | ClickHouse makes good use of your own hardware; the backup has to leave the building. |
What is included
- ClickHouse in your cloud account or on your servers, with the configuration in Terraform or Ansible in your repository.
- Table design or review: the right MergeTree engine, ORDER BY based on your queries and sensible partitions.
- Replicas with ReplicatedMergeTree and ClickHouse Keeper on three nodes, and shards when the volume calls for it.
- Backups with BACKUP to S3 storage, full and incremental, and a timed test restore.
- Retention with TTL: old data deleted or moved to cheap storage.
- Users, profiles and quotas per application so that one query cannot bring the server down.
- Metrics in Prometheus and alerts for parts, lagging replicas, memory and disk.
- Upgrades to LTS versions, one replica at a time, rehearsed first on a copy.
Frequently asked questions
ClickHouse Cloud or ClickHouse in our own account?
ClickHouse Cloud is convenient and separates storage from compute. In your own account the data is where you decide, with no extra third party in the chain, and with a steady volume you usually pay less. In return, someone has to run replicas, backups and upgrades, which is what we do.
Can we migrate from ClickHouse Cloud to our own account?
Yes. We recreate the tables, swapping the Cloud-specific engine for ReplicatedMergeTree, and copy the data partition by partition with the remoteSecure function, checking row counts. Meanwhile the load keeps arriving at Cloud, and at the end we switch the destination of the inserts.
Why does the too many parts error appear?
Almost always because rows are inserted one at a time or in very small batches: each insert creates a new part and the merges cannot keep up. We fix it by batching inserts, enabling asynchronous inserts or revisiting a partitioning scheme that is too fine.
Is ClickHouse suitable for logs and metrics?
Yes, it is one of its most common uses: it stores large volumes compressed and answers time-based queries quickly. We combine it with TTL so that old data is deleted or moved to cheap storage.
Tell us about your case
In a free 20-minute call we look at where you are and where to start. No commitment.
Official documentation consulted
The technical details on this page are based on:
Reviewed on