How to Install ClickHouse Columnar Database on Ubuntu VPS for Real-Time Big Data Analytics

Why Traditional Row-Oriented Databases Fail at Big Data Analytics

Traditional relational databases like MySQL and PostgreSQL store data on disk row by row. While row-oriented storage is optimal for transactional updates (OLTP) involving individual records, executing analytical queries (OLAP)—such as calculating average page load times across 500 million web analytics rows or filtering monthly financial transactions—requires reading entire rows from disk into RAM. Under heavy analytical datasets, queries take minutes to execute and consume gigabytes of memory.

ClickHouse is a revolutionary open-source column-oriented database management system. By storing data columns contiguously on disk with advanced compression (LZ4/ZSTD) and leveraging SIMD CPU vectorization, ClickHouse scans and filters hundreds of millions of rows per second on a single virtual CPU core, returning complex aggregations in sub-milliseconds.

In this comprehensive enterprise tutorial, we will install official ClickHouse on Ubuntu 24.04/22.04 LTS, tune memory and disk storage settings, create high-speed MergeTree tables, and connect ClickHouse to Grafana for real-time visualization.

Step 1: Adding Official ClickHouse APT Repository

# Install prerequisite tools
sudo apt update && sudo apt install -y apt-transport-https ca-certificates dirmngr curl

# Import ClickHouse GPG signing key
curl -fsSL https://packages.clickhouse.com/packages/keys/clickhouse.gpg | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg

# Add official ClickHouse APT repository
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

# Install ClickHouse Server and Client
sudo apt update && sudo apt install -y clickhouse-server clickhouse-client

Step 2: Configuring and Starting ClickHouse Server

Enable and start the ClickHouse daemon:

# Start and enable the systemd service
sudo systemctl enable --now clickhouse-server

# Verify service health
sudo systemctl status clickhouse-server --no-pager

# Launch interactive CLI
clickhouse-client --password

Step 3: Creating High-Speed MergeTree Analytics Tables

The MergeTree engine is the core workhorse of ClickHouse, supporting massive real-time data ingestion and background index merging:

-- Create web analytics event database
CREATE DATABASE IF NOT EXISTS analytics;

-- Create high-performance columnar table
CREATE TABLE analytics.web_events (
    event_time DateTime DEFAULT now(),
    domain LowCardinality(String),
    visitor_ip String,
    country LowCardinality(String),
    http_status UInt16,
    response_time_ms Float32,
    user_agent String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (domain, event_time, http_status);

Step 4: Inserting and Benchmarking High-Speed Aggregations

Insert 1 million synthetic benchmark records in 2 seconds:

-- Insert 1,000,000 synthetic event records instantly
INSERT INTO analytics.web_events (event_time, domain, visitor_ip, country, http_status, response_time_ms, user_agent)
SELECT
    now() - rand() % 86400,
    ['cpanelfree.com', 'blog.example.com', 'api.example.com'][rand() % 3 + 1],
    concat(toString(rand() % 255), '.', toString(rand() % 255), '.1.1'),
    ['US', 'IN', 'DE', 'GB', 'SG'][rand() % 5 + 1],
    [200, 200, 200, 404, 502][rand() % 5 + 1],
    (rand() % 250) / 10.0,
    'Mozilla/5.0 (Windows NT 10.0; Win64; x64)'
FROM numbers(1000000);

Execute sub-millisecond aggregation query:

-- Calculate 95th percentile latency and error rates across 1M rows
SELECT
    domain,
    count() AS total_requests,
    quantiles(0.95)(response_time_ms) AS p95_latency,
    countIf(http_status >= 400) AS error_count
FROM analytics.web_events
GROUP BY domain;
-- Result returned in: 0.008 sec (8 milliseconds)!

Row-Oriented vs Column-Oriented Storage Comparison

Integrating ClickHouse with Grafana for Real-Time Dashboards

Install the official ClickHouse Grafana plugin to build visual telemetry charts for web traffic, latency heatmaps, and error rates:

# Install official ClickHouse plugin in Grafana
sudo grafana-cli plugins install grafana-clickhouse-datasource
sudo systemctl restart grafana-server

In Grafana, configure the data source with Server Address: 127.0.0.1:8123 (HTTP), Protocol: Native HTTP, Database: analytics, and enter your secure user credentials.

ClickHouse Partition Pruning & TTL Retention Policies

Automate storage lifecycle management by defining column and table-level Time-To-Live (TTL) expiration rules directly in your table schema:

-- Automatically delete web analytics data older than 90 days
ALTER TABLE analytics.web_events MODIFY TTL event_time + INTERVAL 90 DAY;

-- Automatically compress partitions older than 7 days with ZSTD(9)
ALTER TABLE analytics.web_events MODIFY TTL event_time + INTERVAL 7 DAY RECOMPRESS CODEC(ZSTD(9));

ClickHouse Performance Tuning Best Practices

• Batch Inserts (Minimum 1,000 to 100,000 rows): Never issue single-row inserts in ClickHouse; always buffer and bulk insert to maximize MergeTree write throughput.
• Use LowCardinality Data Types: Replace standard strings with LowCardinality(String) for columns with fewer than 10,000 unique values (e.g., HTTP status codes, countries, browser names) to save 80% RAM.

ClickHouse Production Architecture & Query Optimization Guidelines

To extract maximum performance from ClickHouse on a Linux VPS, adhere to the following architectural design principles:

• Choose Primary Sorting Keys Wisely: Order your MergeTree tables by the exact columns used most frequently in WHERE and GROUP BY clauses (e.g. ORDER BY (domain, event_time)).
• Utilize Materialized Views: Pre-calculate complex hourly or daily rollups using Materialized Views to reduce query scan times to sub-millisecond speeds.
• Tune Max Execution Memory: Set max_server_memory_usage_to_ram_ratio = 0.8 in /etc/clickhouse-server/config.xml to prevent Out-Of-Memory kernel panics.

-- Example Materialized View for Hourly Traffic Summaries
CREATE MATERIALIZED VIEW analytics.hourly_traffic_mv
ENGINE = SummingMergeTree()
PRIMARY KEY (domain, hour)
AS SELECT
    domain,
    toStartOfHour(event_time) AS hour,
    count() AS total_hits,
    sum(response_time_ms) AS total_latency
FROM analytics.web_events
GROUP BY domain, hour;