ClickHouse on NVMe: Crushing Big Data Latency for Real-Time Analytics

If I see one more developer try to run a SELECT COUNT(*) on a 50-million row PostgreSQL table during peak traffic, I might just unplug the server myself. It’s 2021. We are past the point where we can pretend that OLTP (Online Transaction Processing) databases can double as OLAP (Online Analytical Processing) engines without severe consequences.

I’ve spent the last month migrating a Norwegian logistics client from a bloated MySQL cluster to ClickHouse. The result? Reports that took 45 seconds now take 120 milliseconds. But speed isn't magic. It requires raw I/O throughput, specific CPU instruction sets, and a storage backend that doesn't choke when ClickHouse starts its aggressive background merges.

This is not a tutorial on how to install a database on your laptop. This is about deploying a production-ready analytics engine on a VPS in Norway, ensuring your data stays compliant with Datatilsynet while serving requests fast enough to keep your dashboard users happy.

The Architecture of Speed: Why Columnar Wins

Traditional row-oriented databases (MySQL, Postgres) store data like this:

To calculate the average 'Value', the disk has to read the ID, Name, and Date for every single row, just to throw them away. That is wasted I/O. ClickHouse, being columnar, stores data like this:

It only reads the 'Value' column file. This reduces disk I/O by orders of magnitude. However, this architecture relies heavily on the underlying filesystem performance. This is where most generic VPS providers fail. If you are running ClickHouse on spinning rust (HDD) or network-throttled SATA SSDs, you are driving a Ferrari with the handbrake on.

Deploying ClickHouse 21.8 on Ubuntu 20.04

Let's get our hands dirty. We are using Ubuntu 20.04 LTS because the kernel support for modern IO_uring (though ClickHouse primarily uses its own threading model) is stable. We will stick to the stable release from Yandex (now Altinity/community managed repos are gaining traction, but official builds are safer for now).

1. The Setup

First, add the repository. Do not use the default apt repos; they are often ancient.

sudo apt-get install -y apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4

echo "deb https://repo.clickhouse.tech/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

Once installed, we need to adjust the open files limit. ClickHouse eats file descriptors for breakfast.

# /etc/security/limits.d/clickhouse.conf
clickhouse      soft    nofile  262144
clickhouse      hard    nofile  262144

Optimizing for NVMe: The Config Tweaks

Here is where the hardware reality checks in. ClickHouse achieves its speed through Log-Structured Merge-trees. It writes data rapidly and then merges it in the background. These merges generate massive I/O spikes.

Pro Tip: On CoolVDS instances, we expose raw NVMe interfaces directly to the KVM guest. This prevents the "noisy neighbor" effect where another tenant's bad code steals your IOPS. For ClickHouse, you absolutely need high random write speeds.

Edit your config.xml to optimize for high-speed storage. We want to maximize the number of threads doing background merges without starving the query threads.

    
    16

    
    
        
            10000000000
            0.01
            zstd
        
    

Schema Design: The MergeTree Engine

The MergeTree family is the core of ClickHouse. Unlike MySQL where you normalize everything, here we denormalize. Wide tables are fine. Let's create a table for tracking web events—a classic use case.

CREATE TABLE analytics.page_views (
    EventDate Date,
    EventTime DateTime,
    UserID UInt64,
    URL String,
    Referrer String,
    UserAgent String,
    Duration Float32,
    Country FixedString(2)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192;

Critical Analysis:
1. Partitioning: By month (toYYYYMM). Dropping old data becomes as simple as DROP PARTITION, which is an instant filesystem operation rather than a slow `DELETE` query.
2. Ordering: The ORDER BY clause acts as your primary index. Ordering by Date then UserID allows for incredibly fast time-range queries filtered by user.
3. Granularity: 8192 rows per index mark. This is the default and usually optimal for NVMe storage.

Ingesting Data at Scale

Don't insert row-by-row. That’s a rookie mistake. ClickHouse prefers batches of at least 1,000 rows. If you insert one row at a time, you will create too many small data parts on disk, causing the merge process to thrash your CPU.

For a quick test, let's generate 10 million rows of dummy data directly inside the engine:

INSERT INTO analytics.page_views
SELECT
    toDate('2021-01-01') + number / 100000 as EventDate,
    now() - number as EventTime,
    rand() as UserID,
    'https://coolvds.com/blog/' || toString(number % 100) as URL,
    'Google' as Referrer,
    'Mozilla/5.0...' as UserAgent,
    rand() % 100 as Duration,
    'NO' as Country
FROM numbers(10000000);

On a standard CoolVDS instance (4 vCPU, 8GB RAM, NVMe), this insertion completes in roughly 2-3 seconds. Try that on a standard shared hosting plan and watch your connection time out.

Compliance and Latency: The Norwegian Context

Since the Schrems II ruling last year, sending user IP addresses or behavioral data to US-owned cloud providers has become a legal minefield. Datatilsynet (The Norwegian Data Protection Authority) is closely watching transfers to non-adequate countries.

Hosting your analytics database on Norwegian soil isn't just about latency—though pinging 195.x.x.x from Oslo in 2ms is nice—it's about risk mitigation. When your storage drives are physically located in Oslo, you remove an entire layer of cross-border data transfer compliance headaches.

Performance Benchmark: CoolVDS vs. Generic Cloud

I ran a standard aggregation query: calculating the average session duration grouped by URL for 50 million rows.

The difference is the I/O latency. Networked storage adds a tax to every single file read. Local NVMe eliminates it.

Conclusion

ClickHouse is a beast, but it demands respect. It needs a filesystem that can keep up with its hunger for throughput. If you pair it with sluggish storage, you are simply moving the bottleneck from the CPU to the disk. For dev teams in Norway dealing with high-volume logs or event data, the combination of ClickHouse and local NVMe storage is the only architecture that makes sense in 2021.

Don't let slow I/O kill your analytics dashboards. Spin up a high-frequency NVMe instance on CoolVDS today and watch your query times drop.