ClickHouse on a VPS: Real-Time Analytics Database for Logs, Events, and Time-Series Data

ClickHouse is an open-source columnar database engineered for analytics — it executes aggregate queries on billions of rows in seconds, compresses data 5–10× better than row databases, and ingests millions of events per second. It powers analytics at Cloudflare, Uber, and ByteDance. Self-hosting ClickHouse on a VPS replaces expensive analytics SaaS tools (Amplitude, Mixpanel, Datadog Logs) for organizations that control their own data pipeline.

ClickHouse vs PostgreSQL for Analytics

Step 1: Install ClickHouse

<code"># Add 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 lts main" | \
    sudo tee /etc/apt/sources.list.d/clickhouse.list

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

sudo systemctl enable clickhouse-server
sudo systemctl start clickhouse-server

# Test connection
clickhouse-client --query "SELECT version()"

Step 2: Secure ClickHouse

<code">sudo nano /etc/clickhouse-server/users.d/admin.xml

<code"><clickhouse>
    <users>
        <default>
            <!-- Disable default passwordless access -->
            <password_sha256_hex>
                <!-- Generate: echo -n "YourPassword" | sha256sum | cut -d' ' -f1 -->
                YOUR_SHA256_PASSWORD_HASH
            </password_sha256_hex>
            <networks>
                <ip>127.0.0.1</ip>
                <ip>::1</ip>
            </networks>
        </default>

        <!-- Read-only analytics user for dashboards -->
        <analytics_reader>
            <password_sha256_hex>READER_PASSWORD_HASH</password_sha256_hex>
            <profile>readonly</profile>
            <quota>default</quota>
            <networks>
                <ip>127.0.0.1</ip>
            </networks>
        </analytics_reader>
    </users>
</clickhouse>

<code">sudo systemctl restart clickhouse-server

Step 3: Create Nginx Access Log Analytics Schema

<code">clickhouse-client --password

<code">CREATE DATABASE analytics;

USE analytics;

-- Nginx access logs table (columnar — optimized for time-range queries)
CREATE TABLE nginx_logs (
    timestamp   DateTime,
    host        LowCardinality(String),   -- LowCardinality for repeated values
    method      LowCardinality(String),
    uri         String,
    status      UInt16,
    bytes_sent  UInt64,
    user_agent  String,
    remote_ip   IPv4,
    referer     String,
    duration_ms Float32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)    -- Partition by month for efficient pruning
ORDER BY (host, timestamp)           -- Primary key for range queries
TTL timestamp + INTERVAL 90 DAY;    -- Auto-delete after 90 days

-- Application events table
CREATE TABLE app_events (
    timestamp   DateTime DEFAULT now(),
    event_name  LowCardinality(String),
    user_id     UInt64,
    session_id  String,
    properties  String,              -- JSON string for flexible properties
    country     LowCardinality(String),
    platform    LowCardinality(String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (event_name, timestamp);

SHOW TABLES;

Step 4: Ingest Nginx Logs

<code"># Configure Nginx JSON logging for easy parsing
# /etc/nginx/nginx.conf — http block:
log_format clickhouse_json escape=json
    '{"timestamp":"$time_iso8601",'
    '"host":"$host",'
    '"method":"$request_method",'
    '"uri":"$request_uri",'
    '"status":$status,'
    '"bytes_sent":$bytes_sent,'
    '"user_agent":"$http_user_agent",'
    '"remote_ip":"$remote_addr",'
    '"referer":"$http_referer",'
    '"duration_ms":$request_time}';

access_log /var/log/nginx/clickhouse.log clickhouse_json;

<code"># Import Nginx log file into ClickHouse
cat /var/log/nginx/clickhouse.log | \
    clickhouse-client --password \
    --query "INSERT INTO analytics.nginx_logs FORMAT JSONEachRow"

# For continuous ingestion — use Vector or Filebeat:
# Vector agent (recommended) tails log files and ships to ClickHouse
sudo apt install -y vector

<code"># /etc/vector/vector.yaml
sources:
  nginx_logs:
    type: file
    include: ["/var/log/nginx/clickhouse.log"]

transforms:
  parse_json:
    type: remap
    inputs: ["nginx_logs"]
    source: |
      . = parse_json!(.message)
      .timestamp = parse_timestamp!(.timestamp, "%+")

sinks:
  clickhouse:
    type: clickhouse
    inputs: ["parse_json"]
    endpoint: http://localhost:8123
    database: analytics
    table: nginx_logs
    auth:
      strategy: basic
      user: default
      password: YourPassword

Step 5: Analytics Queries

<code">-- Top 20 pages by request count (last 7 days)
SELECT
    uri,
    count() AS requests,
    countIf(status = 200) AS success,
    avg(duration_ms) AS avg_ms
FROM analytics.nginx_logs
WHERE timestamp >= now() - INTERVAL 7 DAY
GROUP BY uri
ORDER BY requests DESC
LIMIT 20;

-- HTTP status code distribution by hour
SELECT
    toStartOfHour(timestamp) AS hour,
    status,
    count() AS count
FROM analytics.nginx_logs
WHERE timestamp >= now() - INTERVAL 24 HOUR
GROUP BY hour, status
ORDER BY hour, status;

-- Unique visitors per day
SELECT
    toDate(timestamp) AS day,
    uniq(remote_ip) AS unique_visitors,
    count() AS total_requests
FROM analytics.nginx_logs
WHERE timestamp >= now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day;

-- Slowest endpoints (p95 response time)
SELECT
    uri,
    quantile(0.95)(duration_ms) AS p95_ms,
    count() AS requests
FROM analytics.nginx_logs
WHERE timestamp >= now() - INTERVAL 7 DAY
GROUP BY uri
HAVING requests > 100
ORDER BY p95_ms DESC
LIMIT 20;

Step 6: Connect Grafana for Dashboards

<code"># In Grafana → Data Sources → Add → ClickHouse
# URL: http://localhost:8123
# Database: analytics
# Username: analytics_reader
# Password: reader_password
# HTTP method: POST

# Sample Grafana dashboard query:
SELECT
    $__timeInterval(timestamp) AS time,
    count() AS requests,
    countIf(status >= 500) AS errors
FROM analytics.nginx_logs
WHERE $__timeFilter(timestamp)
GROUP BY time
ORDER BY time

Getting Started

ClickHouse needs 2 GB RAM for modest analytics workloads. A 4 GB Ubuntu VPS at VPS.DO handles billions of log rows with fast query performance. NVMe storage is especially beneficial — ClickHouse’s columnar I/O patterns benefit from NVMe sequential read speeds. For organizations generating 1+ million log events daily, ClickHouse provides analytics capabilities that would cost $100–$1,000/month on Datadog, Splunk, or managed analytics services.

Conclusion

ClickHouse on a VPS delivers sub-second aggregation queries on billions of events, 5–10× storage compression, and a SQL interface familiar to any developer — at infrastructure cost only. For Nginx log analytics, application event tracking, and time-series metrics where PostgreSQL is too slow and managed analytics services are too expensive, ClickHouse is the right choice. Pair with Vector for continuous log ingestion and Grafana for dashboard visualization.