Self-hosted ClickHouse for your analytics, on a server that is all yours

Will one server handle your analytics?

For many teams starting out, yes. The sizing guide advises scaling servers up before adding replicas, and points out that ClickHouse does not shard data automatically, so splitting it later is costly. It stores each column compressed, so your data usually takes less room on disk than it did in the source. What it wants from the box is plenty of memory, so frequently read data stays cached, and storage that can feed many reads at once.

The official recommendations give a clear floor. They recommend 32 GB of RAM or more. Below 16 GB you can expect memory exceptions, because the default settings assume more; ClickHouse will start with only 2 GB, but only with careful tuning and a slow trickle of inserts. If your compressed data stays under roughly 200 GB, the docs suggest matching RAM to the data itself so everything sits in memory.

How much memory for how much data

Once data outgrows memory, the sizing guide talks in ratios of RAM to stored, compressed data. Pick the row that sounds like your workload:

The middle example is our own multiplication; the last is printed in the guide. For processor cores, the same guide suggests about 4 GB of memory per core for general use and 8 GB per core for data-warehouse style ad-hoc queries.

Not sure how big your data will be? The install pages give a quick method: load a slice of real data into a test table, note the average row length and how far it compresses, then scale that up to the rows you plan to keep. Clickstream data, their example, usually shrinks 6 to 10 times.

Why the disk decides how fast it feels

Every query that misses the cache reads from disk, so the docs say plainly: use SSD if your budget allows, and 7200 RPM hard disks only if it does not. NVMe drives connect over PCIe rather than SATA, which matters when several people open dashboards at the same moment and each query reads different parts of a table. Our comparison of NVMe, SSD and hard disks explains the difference.

• Use ext4 with the noatime mount option, or XFS. Avoid filesystems that compress or encrypt: the database already does both itself.
• Keep TRIM running on flash drives (fstrim, usually a systemd timer).
• Turn swap off, set transparent huge pages to madvise and the CPU governor to performance, as the recommendations ask.
• A mirror keeps you running when a drive dies. The docs lean on replication between servers for safety, so on a single server a mirror plus off-site backups is the sensible pairing.

The server that fits

Our NVMe dedicated servers give ClickHouse the whole machine: every core for merges and queries, all the memory for cache, and drives no other customer touches. Your port is unmetered and not shared with anyone at the access switch, so loading a large backfill or pulling reports out costs nothing extra in traffic. If a drive or any other part fails, it is replaced within four hours of your call, and software RAID runs on three cartridge types (m510, m710x, m750), so two drives can mirror each other.

Two lines ship with NVMe drives as standard: the Xeon D-1500, with up to 128 GB of memory, and the Xeon E3-1585L v5, with up to 64 GB. If your data will grow past what one or two drives hold, the enterprise blade and rack servers take up to 24 drives behind a hardware RAID controller.

A machine that is already racked is handed to you about 30 minutes after your order is approved; a custom build usually takes 4 to 24 hours, longer when parts must be bought in. Pick the city closest to the people and services that query it on our data center locations page.

Up and running in an evening

ClickHouse recommends its official pre-compiled packages. On AlmaLinux, offered on most of our lines, these are the steps from the RPM install page:

sudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
sudo yum install -y clickhouse-server clickhouse-client
sudo systemctl enable clickhouse-server
sudo systemctl start clickhouse-server
sudo systemctl status clickhouse-server
clickhouse-client --password

1. If the installer asks you to set up a password for the default user, give it a long random one. It stores only a SHA-256 hash, in a file named default-password.xml inside the server's users.d folder.
2. If it never asked, or you pressed Enter, that user keeps an empty password. Write the same file yourself before going further; the user settings page shows the password_sha256_hex format (we suggest making it readable only by root and the clickhouse service user).
3. Connect with clickhouse-client --password and create your first database.
4. Prefer Ubuntu or Debian? Those instructions use a signed apt repository instead, with identical package names.

Keep strangers out of your data

Out of the box, the server only answers connections from the machine itself, and it should stay that way while the default account, the only one a fresh install creates, has an empty password. The shipped user file lets that account log in from any address, so the localhost-only listener is the one thing protecting it.

Before you let other machines in, make sure every user has a strong password and add a firewall that lets in only your own application servers. Prefer the encrypted ports, 8443 for HTTP and 9440 for the native protocol, over plain 8123 and 9000. They are switched off in the default config, so enable https_port and tcp_port_secure and give the server a TLS certificate first. Then firewall 8123 and 9000 from everything outside the machine.

With that in place, open the listener with a small file in /etc/clickhouse-server/config.d/, then restart the server:

<clickhouse>
    <listen_host>::</listen_host>
</clickhouse>

:: accepts IPv4 and IPv6 connections from every address; on a server with IPv6 disabled use 0.0.0.0 instead. For day-to-day admin from your laptop, an SSH tunnel to localhost avoids opening a port at all.

Backups that get your tables back

ClickHouse has its own BACKUP and RESTORE commands. To write backups to a local folder, declare a backup disk in config.d/backup_disk.xml (the docs show a disk named backups pointing at /backups/, listed under allowed_disk) and restart. Then a weekly full backup and daily incrementals look like this:

-- weekly full backup
BACKUP DATABASE analytics TO Disk('backups', 'week-42.zip');

-- daily incremental, stores only what changed since the full one
BACKUP DATABASE analytics TO Disk('backups', 'week-42-mon.zip')
  SETTINGS base_backup = Disk('backups', 'week-42.zip');

-- restore; the tables must not exist yet or must be empty
RESTORE DATABASE analytics FROM Disk('backups', 'week-42-mon.zip');

Keep the full backup as long as any incremental depends on it. Then get copies off the server: the same command writes straight to an S3 bucket, and a backup sitting next to your data will not help if the whole machine is lost. ZIP backups can also carry a password.

When you will want more than one machine

High availability in ClickHouse means replicas: the sizing guide recommends at least three replicas per shard, and the install pages recommend running ClickHouse Keeper on its own nodes in production. Replicas copy data constantly, which is why the install pages call for a 10 Gbit network once you cluster. There is no private network between our servers, so replica traffic would cross public ports you must firewall and encrypt yourself. For a single analytics server with good backups, one machine is simpler and cheaper to run.

Questions people ask

What are the minimum requirements for running ClickHouse yourself?

The docs recommend 32 GB of RAM or more and note that the program itself takes 2.5 GB or more of disk space. A 2 GB machine can work, but only with tuning and low insert rates. Plan disk from a sample of your own data after compression.

Can I run ClickHouse on hard disks?

Yes. The recommendations accept 7200 RPM SATA disks when SSD is out of budget. Queries that miss the memory cache will be slower, so dashboards used by many people at once benefit most from SSD or NVMe.

Is ClickHouse open to the internet after install?

Not by default. With no listen address configured, it answers only on localhost. It becomes reachable once you add a listen_host such as ::, which is why every user should have a strong password before you do.

How do I back up a self-hosted ClickHouse?

Use the built-in BACKUP command to a configured disk or straight to an S3 bucket, with incremental backups based on a full one. Restore with RESTORE, into tables that do not exist yet or are empty; the allow_non_empty_tables setting overrides that but can duplicate data.

Do I need ClickHouse Keeper on one server?

Keeper coordinates replication and distributed setups. A single server using plain MergeTree tables does not need it. When you do add replicas, the install pages recommend running Keeper on its own nodes in production.

Will traffic between my apps and ClickHouse cost extra?

No. Nothing counts the traffic: there is no allowance to run past and no overage charge, so heavy data loads and exports are covered by the monthly price.