High-Velocity Time-Series Analytics with ClickHouse on a Single VPS

Modern observability, fraud detection, and analytics systems generate massive volumes of append-only events. Storing billions of log rows in traditional row-oriented relational databases quickly leads to disk saturation, bloated B-tree indexes, and slow aggregation queries. ClickHouse, an open-source columnar database management system, solves this bottleneck by storing data column-by-column, applying vectorised execution, and achieving 5x to 10x LZ4/ZSTD compression on high-velocity data.

On a single Linux VPS equipped with dedicated Intel Xeon Platinum cores and High-Speed SSD storage in Lucknow DC01, ClickHouse can easily ingest over 500,000 rows per second while answering analytical queries across hundreds of millions of records in under 100 milliseconds.

Architectural Fundamentals of Columnar Storage

Traditional row-based databases store entire tuples consecutively on disk. When executing a query like SELECT avg(duration_ms) FROM logs, the storage engine must read the entire table into memory—including large text fields like URLs, user-agent headers, and payload bodies—just to extract a single floating-point column.

In contrast, ClickHouse stores each column in its own separate file compressed with algorithms tailored to the data type (e.g., Gorilla for floating-point numbers, DoubleDelta for chronological timestamps, and Dictionary/LowCardinality for repeated strings). Furthermore, ClickHouse processes data in SIMD-vectorized batches rather than row-by-row function calls, allowing modern Intel Xeon processors to process millions of records per CPU clock cycle.

Installing ClickHouse on Ubuntu 24.04

ClickHouse provides an official quick installation script that verifies CPU instruction sets (including AVX2 and SSE 4.2) and configures the clickhouse-server systemd daemon:

# Install ClickHouse server and client binaries
sudo apt-get install -y apt-transport-https ca-certificates dirmngr curl
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-get update
sudo apt-get install -y clickhouse-server clickhouse-client

# Start and enable the service
sudo systemctl enable --now clickhouse-server
sudo clickhouse-client --query "SELECT version();"

ClickHouse defaults to listening on 127.0.0.1 on port 9000 (native TCP) and port 8123 (HTTP interface).

Designing the MergeTree Table Schema

The core workhorse of ClickHouse is the MergeTree engine family. MergeTree writes incoming batches into immutable columnar parts on disk, which are asynchronously merged in the background. Choosing an effective primary key and partitioning strategy is crucial for fast analytics:

• **PARTITION BY**: Determines physical directory splitting. Partitioning by month (toYYYYMM) ensures query pruning without creating thousands of micro-partitions that exhaust filesystem inodes.
• **ORDER BY**: Defines the physical sort order within each data part. The primary key is a sparse index constructed from this sort order, maintaining an index mark every 8,192 rows.

CREATE DATABASE IF NOT EXISTS telemetry;

CREATE TABLE telemetry.api_logs
(
    event_timestamp DateTime64(3, 'UTC') CODEC(DoubleDelta, LZ4),
    event_date Date DEFAULT toDate(event_timestamp),
    client_ip String CODEC(ZSTD(1)),
    http_method LowCardinality(String),
    endpoint LowCardinality(String),
    status_code UInt16 CODEC(T64, LZ4),
    duration_ms Float32 CODEC(Gorilla, LZ4),
    bytes_sent UInt64 CODEC(T64, LZ4),
    user_agent String CODEC(ZSTD(3))
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (endpoint, http_method, status_code, event_timestamp)
TTL event_date + INTERVAL 90 DAY
SETTINGS index_granularity = 8192;

Notice the column-specific codecs: LowCardinality creates dictionary encodings for strings with few distinct values, DoubleDelta efficiently packs chronological timestamps, and Gorilla compresses floating-point durations.

Expand

High-Throughput Batch Ingestion

ClickHouse is optimized for batched inserts. Ingesting single rows via individual INSERT statements degrades performance because each write attempts to construct a separate data part. Instead, batch records in chunks of 5,000 to 50,000 rows, or use the HTTP interface with streaming NDJSON:

# Stream batched JSON records over the HTTP interface (port 8123)
curl -s -X POST 'http://127.0.0.1:8123/?query=INSERT+INTO+telemetry.api_logs+FORMAT+JSONEachRow'   --data-binary @- << 'EOF'
{"event_timestamp":"2026-09-12 10:14:02.100","client_ip":"198.51.100.42","http_method":"POST","endpoint":"/api/v1/checkout","status_code":200,"duration_ms":38.4,"bytes_sent":1024,"user_agent":"Mozilla/5.0"}
{"event_timestamp":"2026-09-12 10:14:02.140","client_ip":"198.51.100.99","http_method":"GET","endpoint":"/api/v1/inventory","status_code":200,"duration_ms":12.1,"bytes_sent":4096,"user_agent":"NetbayBot/1.0"}
EOF

# Verify row counts and active data parts
clickhouse-client --query "SELECT count(), formatReadableSize(sum(data_compressed_bytes)), formatReadableSize(sum(data_uncompressed_bytes)) FROM system.parts WHERE table = 'api_logs' AND active;"

Running Real-Time Aggregation Queries

With data indexed and sorted, execute real-time aggregations that calculate percentiles, unique IP visits, and error distributions across millions of records:

SELECT
    endpoint,
    http_method,
    count() AS total_requests,
    round(quantile(0.50)(duration_ms), 2) AS p50_ms,
    round(quantile(0.95)(duration_ms), 2) AS p95_ms,
    round(quantile(0.99)(duration_ms), 2) AS p99_ms,
    uniqExact(client_ip) AS unique_clients
FROM telemetry.api_logs
WHERE event_date >= today() - 7
GROUP BY endpoint, http_method
ORDER BY total_requests DESC
LIMIT 10;

Because only the required columns are loaded from disk, query execution avoids reading unused strings and completes in milliseconds. Tune max_server_memory_usage in /etc/clickhouse-server/config.d/memory.xml to allocate 80% of available RAM, giving ClickHouse ample headroom for vectorised hash tables.

Deploy a high-performance ClickHouse analytics instance on Netbay in under 60 seconds with High-Speed SSD and Intel Xeon Platinum cores in Lucknow DC01 — netbayhosts.in.