Hosting ClickHouse on a VPS

ClickHouse excels at analytical queries (OLAP): thanks to its column storage and aggressive compression, it can scan terabytes on modest hardware where a classic relational database would collapse. Self-hosting on a VPS saves you the per-query costs of managed offerings (ClickHouse Cloud, BigQuery) and gives you full control over schema, table engines, and retention. It is especially 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 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.

ClickHouse loves RAM and fast storage. The absolute minimum is 2 GB of RAM for a test or development environment, but 4 GB is the recommended floor for light production workloads, and 8 GB provides a much healthier margin for large aggregations and mark cache. Below that threshold, you will encounter OOM errors as soon as you query several million rows. On the CPU side, 2 vCPU allow ClickHouse to exploit parallelism on simple queries; 4 vCPU become necessary for aggregations or joins on dense tables. Disk is the most critical factor: an NVMe SSD is mandatory — ClickHouse generates intense I/O during partition merges and scan queries; a spinning HDD immediately caps performance even on small volumes. Plan for at least 50 GB to start, and scale according to your retention knowing that compression reduces the actual footprint by a factor of 5 to 10. Two ports matter: port 8123 for the HTTP interface (used by Grafana, Metabase, curl) and port 9000 for the native TCP protocol (used by Go, Python, or clickhouse-client). Never expose port 9000 directly to the internet without network filtering.

Once ClickHouse is running, three configuration steps consolidate security and performance. First, create a dedicated database via CREATE DATABASE analytics; in the SQL console or via the HTTP API. Avoid writing directly to the default database — a clean namespace simplifies backups, quotas, and permission management. Next, define your users in a users.d/production.xml file rather than in the main users.xml: this split allows you to reload users without restarting the server (SYSTEM RELOAD USERS). For each user, specify a profile, a quota, and a network list. Finally, enable LZ4 compression in config.d/compression.xml: it is ClickHouse's default codec for columns, but defining it explicitly ensures it applies to all tables including those created before the change. LZ4 offers a good balance between compression ratio and decompression speed at read time — for even more compact storage at the cost of slightly higher CPU usage, ZSTD (levels 1 to 3) is a viable alternative for rarely queried columns.

ClickHouse is a natural alternative to Elasticsearch for analytical log pipelines. Where Elasticsearch indexes every field of every JSON document and requires significant JVM memory, ClickHouse stores each column separately and compresses repetitive values (log level, hostname, service) extremely efficiently. For a log pipeline, create a logs table with a MergeTree engine partitioned by day (PARTITION BY toDate(timestamp)) and sorted by (service, timestamp). Ingest via the HTTP API in batch INSERT (see the performance section below) or via Vector.dev, Fluent Bit, or Filebeat, all of which have a native ClickHouse output. For time-series metrics, the MergeTree engine with an ORDER BY (metric_name, timestamp) is suitable for moderate volumes. At larger scale, the AggregatingMergeTree engine pre-aggregates data during merges, further reducing the footprint and accelerating trend queries. A typical query over 500 million rows filtered by a time range and grouped by service runs in 2 to 8 seconds on a 4 vCPU / 8 GB VPS depending on the index — often 10 to significantly faster than OLTP databases for analytics than a standard relational database.

ClickHouse's write behavior is fundamentally different from OLTP databases. Each INSERT creates a new part on disk, and ClickHouse merges these parts in the background. If you insert row by row or in small batches (fewer than 1,000 rows), you flood the system with small parts — merges can't keep up, performance degrades, and you get the Too many parts error (beyond 300 active parts per partition). The golden rule is to insert in batches of 10,000 to 100,000 rows minimum, and space insertions at least one second apart if inserting continuously from multiple sources. From a Python script, use clickhouse-driver or clickhouse-connect with insert_block_size to control block size. From Node.js, the official @clickhouse/client exposes a streaming API that builds batches automatically. Monitor part health in real time with SELECT table, count() as parts, sum(rows) FROM system.parts WHERE active GROUP BY table ORDER BY parts DESC — if a table exceeds 100 active parts, that is a warning sign.

Three errors come up regularly when deploying ClickHouse on a VPS. Too many parts: you are inserting in batches that are too small; group your inserts, add an application-level buffer, or use the Buffer engine as an intermediate table that automatically flushes to the main table. OOM error (Killed, Memory limit exceeded): the query consumed more RAM than max_memory_usage allows. Increase this parameter in the user profile, add a LIMIT clause to exploratory queries, or move to a VPS with more RAM. A query without a filter on a table with billions of rows can consume several GB in one second. Permission denied on /var/lib/clickhouse: the clickhouse system user (UID 101 in the official Docker image) does not have write rights on the mounted volume. Before the first start, run chown -R 101:101 /your/path/clickhouse-data on the host. If ClickHouse has started but is not responding on port 8123, check logs with docker logs <container-name> — the permission error is explicitly shown there.

Everything above can be done manually, and it is useful to know how. If you prefer not to, ClickHouse is listed on the ServOrbit Marketplace and installs on your VPS in one click. The template deploys the pinned 26.3 LTS version, mounts persistent volumes for data and logs, raises the file descriptor limit to 262144 as ClickHouse requires, and creates an analytics database on first boot. Most importantly, it closes the most common trap of manual deployment: the upstream image ships a default account with no password, and the installation removes it at startup and replaces it with an admin account whose password is generated for your server and shown in your client area. Only port 8123 is published, and only on the loopback interface: attach a domain and the built-in SQL console responds at https://<your-domain>/play with its TLS certificate; without a domain, use a tunnel ssh -L 8123:127.0.0.1:8123 root@<vps-ip> then http://localhost:8123/play.