Why self-host ClickHouse on a VPS
ClickHouse excels at analytical (OLAP) queries: thanks to its columnar storage and aggressive compression, it can scan terabytes on modest hardware where a classic relational database would collapse. Self-hosting it on a VPS spares you the per-query-volume costs of managed offerings (ClickHouse Cloud, BigQuery) and gives you full control over the schema, table engines, and retention. It's particularly relevant for ingesting your own application logs, product metrics, or analytics events without sending your data to a third party. Since ClickHouse often compresses data by a factor of 5 to 10, a VPS with a reasonable SSD disk can store hundreds of millions of events. You also keep control over latency: your dashboards query an instance close to your users rather than a remote service.
The concrete benefits of self-hosting ClickHouse
- Extremely fast analytical queries: aggregations over hundreds of millions of rows in under a second.
- Columnar compression of 5x to 10x that drastically reduces the storage cost of your logs and events.
- Massive ingestion: hundreds of thousands of rows per second on a single well-tuned node.
- SQL-compatible and compatible with many connectors (Grafana, Metabase, HTTP and native clients).
- Analytical sovereignty: your logs and product metrics stay on your infrastructure.
- No per-query billing: a fixed VPS cost for a high analytical volume.
Hardware and software prerequisites
ClickHouse loves RAM and fast disk. For serious analytical use, aim for 4 GB of RAM minimum, but 8 GB offer a much healthier margin for large aggregations and marks (mark cache). On the CPU side, 2 to 4 vCPU allow queries to be parallelized. The disk is critical: favor NVMe SSD with at least 50 to 100 GB depending on your volume and retention, knowing that compression reduces the actual footprint. You'll need Docker, a domain if you expose the HTTP interface, and particular attention to the max_memory_usage parameter to prevent a query from bringing down the server. Avoid exposing the native 9000 port directly on the Internet.
Deploying ClickHouse step by step
Prepare the VPS
Install Docker, increase the system limits (ulimit -n to 262144) because ClickHouse opens many file descriptors. Disable excessive swap and check that the disk is indeed NVMe SSD.
Launch ClickHouse via Docker
Use the official clickhouse/clickhouse-server image. Mount three persistent volumes: /var/lib/clickhouse (data), /var/log/clickhouse-server (logs), and a configuration folder /etc/clickhouse-server/config.d. Add ulimits: nofile: 262144 in the compose.
Create a user and a password
Never leave the default user without a password. Create a users.d/custom.xml file with a dedicated user, a hashed password (echo -n motdepasse | sha256sum), and quotas. Restrict <networks> to the authorized IPs.
Tune the memory and the profile
In config.d, set max_server_memory_usage_to_ram_ratio to 0.8 and define a per-query max_memory_usage suited to your VPS to avoid OOMs. Enable partition retention with a TTL on your tables (TTL date + INTERVAL 90 DAY).
Expose the HTTP interface behind a reverse proxy
ClickHouse exposes an HTTP API on port 8123. Put Nginx or Caddy in front for TLS and additional authentication, and don't open the native 9000 port to the public. Reserve native access to the internal network or via an SSH tunnel.
Connect a visualization tool
Connect Grafana or Metabase to the HTTPS endpoint to build your dashboards. Create your tables with the MergeTree engine, choose a relevant ORDER BY, and partition by month for efficient queries and purging.
The choice of the MergeTree engine's sort key (ORDER BY) is the decisive performance factor: put first the columns most filtered in your queries (often a date and an identifier). Use Buffer tables or batch inserts of several thousand rows rather than row-by-row insertions, which create a multitude of small partitions and trigger expensive merges. Monitor system.merges and system.parts to detect excessive fragmentation.
