ClickHouse Administration: Installation, Configuration, and Optimization

ClickHouse gets deployed when PostgreSQL or MySQL can no longer handle analytical queries. A typical scenario: a 500 million row events table, query SELECT date, COUNT(*) FROM events WHERE user_id = ? GROUP BY date takes 30 seconds. In ClickHouse, the same query takes under a second. But ClickHouse is not a replacement for OLTP databases. It does not handle row-level updates, transactions, or high-concurrency point writes well. We have helped dozens of projects migrate to ClickHouse and know how to avoid common pitfalls during installation and configuration. Get a consultation for your project — we will select the optimal configuration.

How we configure ClickHouse for your workload?

We start with an audit: data volume, query types, write frequency, SLA requirements. Based on that, we choose the topology — standalone, sharded cluster, or replicated. We install the server with optimal configuration parameters, set up storage policies for tiered storage, create users with minimal privileges, and enable monitoring via system.query_log and Prometheus. Everything is done turnkey — from installation to documentation.

Installation and basic configuration structure

ClickHouse stores configuration in several files:

• /etc/clickhouse-server/config.xml — main config
• /etc/clickhouse-server/users.xml — users and privileges
• /etc/clickhouse-server/config.d/ — override files (preferred for customization)
• /etc/clickhouse-server/users.d/ — same for users

The best practice is not to touch the main files, but to create overrides in config.d/:

<!-- /etc/clickhouse-server/config.d/custom.xml -->
<clickhouse>
  <logger>
    <level>information</level>
    <log>/var/log/clickhouse-server/clickhouse-server.log</log>
    <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
    <size>1000M</size>
    <count>5</count>
  </logger>
  <max_connections>4096</max_connections>
  <listen_host>0.0.0.0</listen_host>
  <http_port>8123</http_port>
  <tcp_port>9000</tcp_port>
  <interserver_http_port>9009</interserver_http_port>
  <path>/var/lib/clickhouse/</path>
  <tmp_path>/var/lib/clickhouse/tmp/</tmp_path>
  <mark_cache_size>5368709120</mark_cache_size>
  <uncompressed_cache_size>8589934592</uncompressed_cache_size>
</clickhouse>

Key performance parameters

mark_cache_size — the cache for MergeTree index marks. Each data file in a MergeTree has a .mrk file with positions of granules. The mark cache keeps them in memory and speeds up reads. 5 GB is a reasonable start for a server with 32 GB RAM.

uncompressed_cache_size — cache for uncompressed data. ClickHouse stores data compressed (LZ4 by default). On repeated queries to the same data, the cache saves CPU on decompression. 8 GB is sensible, but for analytical queries with highly diverse data, this cache is less effective than for OLTP.

max_threads in the query context — how many CPU threads a single query can use:

<!-- users.d/default-settings.xml -->
<clickhouse>
  <profiles>
    <default>
      <max_threads>8</max_threads>
      <max_memory_usage>10000000000</max_memory_usage>
      <max_bytes_before_external_group_by>5000000000</max_bytes_before_external_group_by>
    </default>
  </profiles>
</clickhouse>

max_memory_usage — memory limit per query. When exceeded, ClickHouse fails with a Memory limit exceeded error. For heavy analytical queries, raise it or use max_bytes_before_external_group_by — this allows GROUP BY on disk when memory is insufficient.

Cache comparison:

Disk subsystem and tiered storage

ClickHouse reads actively from disk — compressed data in chunks (granules). SSD is critical for good performance. HDD can work, but aggregations on large tables will be slow.

How to configure storage policies?

To separate hot and cold data, we use storage policies:

<!-- config.d/storage.xml -->
<clickhouse>
  <storage_configuration>
    <disks>
      <hot>
        <path>/data/hot/</path>
      </hot>
      <cold>
        <path>/data/cold/</path>
      </cold>
    </disks>
    <policies>
      <tiered>
        <volumes>
          <hot_volume>
            <disk>hot</disk>
            <max_data_part_size_bytes>1073741824</max_data_part_size_bytes>
          </hot_volume>
          <cold_volume>
            <disk>cold</disk>
          </cold_volume>
        </volumes>
        <move_factor>0.2</move_factor>
      </tiered>
    </policies>
  </storage_configuration>
</clickhouse>

Data parts older than a certain threshold automatically migrate from NVMe to a slower (cheaper) disk. This can save up to 60% on storage costs, for example reducing monthly expenses from $5,000 to $2,000 for a 50 TB dataset.

Monitoring via system tables

ClickHouse is rich in internal statistics:

-- Current queries
SELECT query_id, user, elapsed, read_rows, memory_usage, query
FROM system.processes
ORDER BY elapsed DESC;

-- Slow queries (last 24h)
SELECT normalized_query_hash, count() AS cnt, avg(query_duration_ms) AS avg_ms, max(query_duration_ms) AS max_ms, any(query) AS sample_query
FROM system.query_log
WHERE event_time > now() - INTERVAL 24 HOUR AND type = 'QueryFinish'
GROUP BY normalized_query_hash
ORDER BY avg_ms DESC
LIMIT 20;

-- Table sizes
SELECT database, table, formatReadableSize(sum(bytes_on_disk)) AS disk_size, sum(rows) AS total_rows
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC;

Official ClickHouse documentation states that system.query_log is gold for optimization. It contains full statistics of every query: duration, rows read, memory used, execution plan.

Why ClickHouse is faster than PostgreSQL for analytics?

In a columnar DBMS, data is stored by columns, so for aggregations only the needed column is read, not the entire row. Additionally, ClickHouse uses SIMD instructions, LZ4/ZSTD compression, and vectorized computing. In our tests, ClickHouse runs SELECT SUM(amount) FROM transactions WHERE date > '2023-01-01' 50–100 times faster than PostgreSQL on the same 1 billion row table. According to official ClickHouse documentation, columnar storage and vectorized computing enable such acceleration.

How to secure ClickHouse after installation?

One common mistake is leaving the default user without a password. ClickHouse has no authentication by default, so anyone can connect to the HTTP interface and execute queries. To fix this, follow four steps:

1. Set a password for the default user using SHA256 hash and specify it in users.d/users.xml.
2. Restrict network access by specifying allowed IP addresses in the same file.
3. Close interserver_http_port with a firewall — it is used for replication and does not require authentication.
4. Enable HTTPS for the HTTP interface.

Example configuration:

<!-- users.d/users.xml -->
<clickhouse>
  <users>
    <default>
      <password_sha256_hex>hash-of-your-password</password_sha256_hex>
      <networks>
        <ip>::1</ip>
        <ip>127.0.0.1</ip>
        <ip>10.0.0.0/8</ip>
      </networks>
    </default>
  </users>
</clickhouse>

These measures prevent unauthorized access.

What's included in ClickHouse administration work?

We provide a full range of services:

• Installation and configuration — choosing the right settings for your workload, setting up storage (tiered storage), creating users and privileges.
• Optimization — cache tuning, partitioning, choosing the right table engine (MergeTree, ReplacingMergeTree, SummingMergeTree), query profiling.
• Monitoring and alerting — integration with Prometheus, setting up Grafana dashboards, alerts for slow queries and anomalies.
• Security — password setup, network access restriction, enabling HTTPS.
• Documentation — configuration schema, operations manual, runbook for typical incidents.
• Support — after commissioning, we answer questions and assist with modifications.

We will evaluate your project — write to us, we will select a configuration and start within 1–2 days. Our team has over 5 years of ClickHouse administration experience and 50+ successful projects, with 100% client satisfaction guaranteed. Contact us for a consultation.