Install ClickHouse on VPS — High-Performance Analytics Database

September 2026 · 10 min read

ClickHouse is a column-oriented (columnar) database management system developed by Yandex, purpose-built for online analytical processing (OLAP). It can query billions of rows in seconds on a standard VPS. Unlike traditional row-oriented databases, ClickHouse stores each column separately, enabling queries that only read the specific columns they need — dramatically reducing I/O and leveraging advanced compression algorithms. It excels at log analytics, web analytics, time-series data, business intelligence dashboards, and any workload that requires aggregating large datasets fast.

System Requirements

Install ClickHouse on Ubuntu/Debian

# Add the official ClickHouse repository
sudo apt install -y apt-transport-https ca-certificates curl gnupg

curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg

echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] https://packages.clickhouse.com/deb stable main" \
  | sudo tee /etc/apt/sources.list.d/clickhouse.list

sudo apt update
sudo apt install -y clickhouse-server clickhouse-client

During installation, you will be prompted to set a password for the default user. Choose a strong password. Leaving it empty is not recommended on production servers.

Start ClickHouse and Connect

# Start and enable the service
sudo systemctl start clickhouse-server
sudo systemctl enable clickhouse-server

# Verify service status
sudo systemctl status clickhouse-server

# Connect with the native client
clickhouse-client --password

Create Your First Database and Table

-- Create a database
CREATE DATABASE myanalytics;
USE myanalytics;

-- Create a web access log table
CREATE TABLE access_log (
    event_time   DateTime,
    client_ip    IPv4,
    http_method  LowCardinality(String),
    request_uri  String,
    status_code  UInt16,
    bytes_sent   UInt32,
    response_ms  UInt32
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, client_ip);

-- Inspect the schema
DESCRIBE TABLE access_log;

INSERT and Query Data

-- Insert sample rows
INSERT INTO access_log VALUES
    (now(), '203.0.113.5', 'GET', '/index.html', 200, 1024, 45),
    (now(), '198.51.100.2', 'POST', '/api/login', 200, 512, 120),
    (now(), '192.0.2.10', 'GET', '/static/app.js', 304, 0, 8);

-- Count requests grouped by status code
SELECT status_code, count() AS hits, avg(response_ms) AS avg_ms
FROM access_log
GROUP BY status_code
ORDER BY hits DESC;

-- Top 10 most-requested URIs
SELECT request_uri, count() AS cnt
FROM access_log
GROUP BY request_uri
ORDER BY cnt DESC
LIMIT 10;

Configure Users and Permissions

-- Create a read-only analyst user
CREATE USER analyst IDENTIFIED WITH sha256_password BY 'SecurePass!2026';
GRANT SELECT ON myanalytics.* TO analyst;

-- Create an application writer user
CREATE USER app_writer IDENTIFIED WITH sha256_password BY 'AppWrite!2026';
GRANT INSERT ON myanalytics.* TO app_writer;

-- List all users
SHOW USERS;

Materialized Views for Pre-Aggregation

-- Create a summary table
CREATE TABLE hourly_stats (
    hour         DateTime,
    status_code  UInt16,
    request_cnt  UInt64,
    avg_ms       Float32,
    total_bytes  UInt64
) ENGINE = SummingMergeTree()
ORDER BY (hour, status_code);

-- Create a Materialized View that auto-updates on INSERT
CREATE MATERIALIZED VIEW mv_hourly_stats
TO hourly_stats
AS SELECT
    toStartOfHour(event_time) AS hour,
    status_code,
    count()          AS request_cnt,
    avg(response_ms) AS avg_ms,
    sum(bytes_sent)  AS total_bytes
FROM access_log
GROUP BY hour, status_code;

-- Query the pre-aggregated table (much faster)
SELECT hour, sum(request_cnt), avg(avg_ms)
FROM hourly_stats
GROUP BY hour
ORDER BY hour DESC
LIMIT 24;

Network Binding and Firewall

# Edit the config to accept network connections
sudo nano /etc/clickhouse-server/config.xml

# Find and update this line (default listens on ::1 / localhost only)
# <listen_host>0.0.0.0</listen_host>   <!-- accept all IPs -->
# Or restrict to a specific IP:
# <listen_host>10.0.0.5</listen_host>

# Allow port 8123 (HTTP) and 9000 (native) via UFW
sudo ufw allow from 10.0.0.0/8 to any port 8123
sudo ufw allow from 10.0.0.0/8 to any port 9000

# Restart ClickHouse
sudo systemctl restart clickhouse-server

Backup with clickhouse-backup

# Install clickhouse-backup
wget https://github.com/Altinity/clickhouse-backup/releases/latest/download/clickhouse-backup-linux-amd64.tar.gz
tar xzf clickhouse-backup-linux-amd64.tar.gz
sudo mv clickhouse-backup /usr/local/bin/

# Generate default config
sudo mkdir -p /etc/clickhouse-backup
sudo clickhouse-backup default-config > /etc/clickhouse-backup/config.yml

# Create a backup
sudo clickhouse-backup create mybackup_$(date +%Y%m%d)

# List available backups
sudo clickhouse-backup list

# Restore a backup
sudo clickhouse-backup restore mybackup_20260911

Verify ClickHouse Is Running Correctly

# Check service status
sudo systemctl status clickhouse-server

# Test via HTTP API
curl http://localhost:8123/?query=SELECT+version()

# Test via native client
clickhouse-client --query "SELECT count() FROM system.tables"

# View running queries
clickhouse-client --query "SELECT query_id, elapsed, query FROM system.processes"

# Check disk usage per table
clickhouse-client --query "
  SELECT database, table, formatReadableSize(sum(bytes_on_disk)) AS size
  FROM system.parts
  WHERE active
  GROUP BY database, table
  ORDER BY sum(bytes_on_disk) DESC
"

Troubleshooting Common Issues

Error: "Code: 516. DB::Exception: default: Authentication failed"

The default user password is incorrect. Reset it via the config file.

# Edit the password config
sudo nano /etc/clickhouse-server/users.d/default-password.xml

# Generate SHA256 hash of your new password
echo -n 'NewPassword' | sha256sum

# Place this content in the file
<clickhouse>
  <users>
    <default>
      <password_sha256_hex>HASH_HERE</password_sha256_hex>
    </default>
  </users>
</clickhouse>

sudo systemctl restart clickhouse-server

Error: "Too many parts (600). Merges are processing significantly slower"

This happens when you insert many small batches. ClickHouse creates a new part per INSERT batch, and background merges can fall behind. Always insert data in larger batches.

-- Instead of inserting one row at a time, insert larger batches
INSERT INTO access_log SELECT * FROM input_buffer;

-- Check current part count
SELECT database, table, count() AS parts
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY parts DESC
LIMIT 10;

-- Force a merge to reduce part count (resource-intensive)
OPTIMIZE TABLE access_log FINAL;

Error: "Memory limit (for query) exceeded"

The query exceeded the configured memory limit. Increase the limit or optimize the query.

-- Raise memory limit for this session
SET max_memory_usage = 10000000000;  -- 10 GB

-- Or set it permanently in users.xml
<max_memory_usage>10000000000</max_memory_usage>

-- Reduce memory usage with sampling
SELECT count() FROM access_log SAMPLE 0.1;  -- scan only 10%

Error: "Connection refused" on port 8123

ClickHouse is binding to localhost only, or the service is not running.

# Check which address ClickHouse is listening on
sudo ss -tlnp | grep clickhouse

# Verify the listen config
grep -i listen /etc/clickhouse-server/config.xml

# Check error logs
sudo tail -50 /var/log/clickhouse-server/clickhouse-server.err.log

Queries Are Slower Than Expected

Likely the ORDER BY key does not match your query pattern, or there is no materialized view covering the aggregation.

-- Inspect the query execution plan
EXPLAIN SELECT * FROM access_log WHERE client_ip = '203.0.113.5';

-- Profile recent queries from the query log
SELECT query, read_rows, read_bytes, elapsed
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY elapsed DESC
LIMIT 10;

Frequently Asked Questions

How is ClickHouse different from PostgreSQL or MySQL?

PostgreSQL and MySQL are row-oriented databases designed for OLTP workloads — web applications that perform frequent INSERT, UPDATE, and DELETE operations on individual rows. ClickHouse is a column-oriented database designed for OLAP workloads — dashboards, reports, and analytics that aggregate millions or billions of rows across a few columns. ClickHouse does not support standard row-level UPDATE/DELETE; mutations are handled with ALTER TABLE ... UPDATE/DELETE, which are asynchronous and best avoided for high-frequency row changes.

Does ClickHouse use a lot of RAM?

ClickHouse does not maintain a persistent in-memory cache daemon like Redis. It relies on the OS page cache during queries. For a production deployment, 8 GB of RAM or more is recommended. For development or datasets under 100 million rows, 2–4 GB is sufficient. Memory usage per query is configurable via max_memory_usage.

Does ClickHouse support standard SQL?

Yes — ClickHouse supports most ANSI SQL syntax including JOINs, subqueries, window functions, and CTEs. Some syntax differs slightly, such as using count() instead of COUNT(*). ClickHouse also adds many built-in functions for analytics: toYYYYMM(), uniqExact(), arrayJoin(), and more. Queries can also be submitted directly over HTTP using URL-encoded SQL, making integration straightforward.