ClickHouse in WSL: MergeTree Parts and Local Analytics Workflows

ClickHouse is useful in WSL when a developer needs a local analytical database to test schemas, ingestion code, SQL, and query behavior against Linux. A local install is best treated as a learning or application-development environment. The fact that ClickHouse packages install on a Linux distribution does not establish that every WSL configuration is an officially supported production platform; consult the current supported-platform policy for deployment decisions.

The operational idea worth learning early is the MergeTree storage model. Inserts create data parts that are later merged in the background according to table and partition behavior. A system that accepts inserts and answers a query can still accumulate too many active parts, insufficient disk capacity, or expensive merge work. Local tests should inspect those mechanics instead of merely confirming that a row was returned.

Install from the current Linux instructions

Use the official Debian or Ubuntu instructions for the WSL distribution and CPU architecture in use. Do not pin an old repository URL or copy a key-management command from an unrelated tutorial. Package and service names can change; keep the chosen instructions with the project’s environment notes and record the actual installed version.

If systemd is configured for the distro, inspect the package unit and service logs through the package’s documented path. First verify the server and client tools exist, then check readiness:

clickhouse-server --version
clickhouse-client --version
systemctl status clickhouse-server --no-pager
sudo systemctl start clickhouse-server
clickhouse-client --query 'SELECT version()'

For a non-systemd distro, use the current ClickHouse installation guide’s supported startup method. Avoid starting a foreground instance while the system service already owns the configured data directory. Two processes writing the same storage path can turn a simple local test into data corruption.

Start with a MergeTree table that has an intentional key

The ORDER BY expression is a physical sort key, not a relational uniqueness constraint. It determines the ordering used by the MergeTree primary index and should reflect common filters and data access patterns. The following small schema is suitable for a disposable local experiment:

CREATE DATABASE IF NOT EXISTS lab;

CREATE TABLE IF NOT EXISTS lab.events
(
    tenant_id UInt32,
    event_time DateTime,
    event_type LowCardinality(String),
    value Float64
)
ENGINE = MergeTree
ORDER BY (tenant_id, event_time);

INSERT INTO lab.events VALUES
    (7, now(), 'started', 1.0),
    (7, now(), 'completed', 1.0);

SELECT tenant_id, event_type, count()
FROM lab.events
WHERE tenant_id = 7
GROUP BY tenant_id, event_type
ORDER BY event_type;

This is a schema and query exercise, not a universal recommendation. The sort key should be chosen from query patterns and data volume. Do not infer that the leading key makes each row unique, or that a primary-key lookup behaves like a transactional OLTP index. Test representative data distributions and query predicates.

Observe parts and merge work directly

Each insert may create one or more parts per partition and data-writing path. Small, frequent inserts can create many parts. Background merges combine eligible parts asynchronously; they consume disk I/O, CPU, and temporary space. A successful insert does not mean those parts have already been compacted.

Inspect active parts for the table:

SELECT
    table,
    count() AS active_parts,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS disk
FROM system.parts
WHERE database = 'lab' AND active
GROUP BY table;

When a merge is running, the system.merges table exposes current merge activity:

SELECT database, table, elapsed, progress, num_parts
FROM system.merges
WHERE database = 'lab';

Use these as observability queries, not as hard-coded acceptance thresholds. Part counts depend on ingestion shape, partitioning, and background activity. A single local observation cannot predict production merge capacity. Avoid routinely forcing OPTIMIZE TABLE FINAL to conceal a poor insert pattern: it can be resource-intensive and does not replace a sustainable ingestion design.

Batch related rows where application semantics permit, and partition only when the partition key supports lifecycle operations such as retention or data management. Excessively granular partitions multiply metadata and parts. A high-cardinality partition key is not a substitute for a useful sort key.

Do not interpret a part as a single incoming row. A part is a storage unit for a partition and can contain many rows, depending on how data was inserted and subsequently merged. When investigating growth, compare active parts, rows, and bytes over a consistent interval, and record the partition expression and insert batch size. A sudden part increase after a migration may come from a changed partition key or many tiny writes rather than a server defect.

Merge activity can also compete with query and ingest work for local resources. WSL memory and CPU limits, Windows host pressure, antivirus scanning, and filesystem placement can all affect a benchmark. Keep an experiment reproducible by capturing the row count, insert batch size, query, server version, VM resource configuration, and whether the test began with a warm cache. Otherwise a single impressive timing is not a useful comparison.

Keep data placement and disk capacity visible

WSL’s Linux filesystem lives within the distribution’s virtual disk. ClickHouse can write substantial data even when the dataset appears small because merges need working space and logs, metadata, and temporary files also consume capacity. Measure free space in the distro, database disk use, and WSL VHDX growth separately. Removing rows or files from Linux does not necessarily shrink the Windows-side virtual disk automatically.

Keep the active ClickHouse data path under package ownership. Do not move it to /mnt/c solely to make the files visible from Windows; database storage workloads depend on filesystem semantics and I/O behavior that differ across the WSL boundary. If Windows must inspect a result set, export a bounded artifact or use the client protocol rather than manipulating active database files.

Before experimenting with retention or disk pressure, use disposable data and a known backup/restore method. A copy of a live database directory during writes is not a verified backup. Test restoring into a separate instance and run validation queries. For a local disposable service, deleting and recreating the dataset may be appropriate, but make that explicit and never automate broad filesystem deletion against an uncertain path.

Separate readiness from query correctness

ClickHouse service startup, SQL connectivity, schema existence, successful ingestion, and query correctness are separate checks. A minimal acceptance sequence should test each layer and name the expected database:

systemctl is-active clickhouse-server
clickhouse-client --query 'SELECT 1'
clickhouse-client --query 'SHOW TABLES FROM lab'
clickhouse-client --query 'SELECT count() FROM lab.events'

For application tests, create the schema using migration or provisioning code, insert a representative batch, query with the same client library and settings as the app, and verify result invariants. Test a cold start as well as a warm query; cache state can make a local benchmark misleading. Capture the server version, effective config, dataset size, and query text with measurements.

If the server starts but a client fails, inspect the configured interface and port, server logs, and whether the client runs in WSL or Windows. Do not change the bind address to all interfaces merely to bypass a local route problem. If a query is slow, inspect its plan and data shape before attributing the cause to WSL or ClickHouse.

WSL is the development boundary

An enabled systemd unit can start ClickHouse when the distribution starts, but it cannot keep a stopped WSL distribution available. Host sleep, Windows restart, WSL update, or distribution removal interrupts or removes the local service. A persistent database path inside the VHDX is still only one local copy and one lifecycle domain.

A clean setup is accepted when documented installation steps produce one server, the readiness and schema checks pass, a representative write/read test succeeds, active parts and disk use can be inspected, and the stop/restart procedure is understood. Keep analytical experiments small enough not to exhaust the host or distro disk. For shared production analytics, select a supported deployment environment and test ingestion, merge capacity, backup, recovery, and upgrades there.

Related:

Sources: