> ## Documentation Index > Fetch the complete documentation index at: https://docs.archil.com/llms.txt > Use this file to discover all available pages before exploring further. # ClickHouse > Run open-source ClickHouse with its data directory on an Archil disk, move it to a new server by remounting, and give heavy queries their own copy of the database with a branch. This guide runs open-source [ClickHouse](https://clickhouse.com/) on Linux servers with its whole data directory on an Archil disk. ClickHouse keeps using ordinary `MergeTree` tables on what looks like a local file system, and Archil keeps the files durable in object storage. That gives a self-hosted ClickHouse three properties it doesn't have on local disks: * **One copy of the data, in object storage.** Archil makes a write durable across Availability Zones as soon as `fsync` returns, so you don't need ClickHouse replicas to keep the data safe. * **Replaceable servers.** No table data or metadata lives on the server. When a server dies, another one mounts the disk and starts ClickHouse with every table intact. * **Isolated copies for heavy queries.** A [branch](https://docs.archil.com/concepts/branches-and-checkpoints.md) gives you a writable copy of the whole database in about 50 ms (a 15 ms checkpoint plus a 35 ms branch), whatever its size. Run a long modeling query or a backfill on a second server against the branch, and it never competes with ingest on the primary. ClickHouse can also store parts in S3 with an `s3` disk, but the server then keeps the metadata for those parts on its local disk, and sharing one copy of the data between replicas relies on zero-copy replication, which ClickHouse doesn't recommend for production. With the data directory on Archil, nothing in ClickHouse needs to know about object storage. You'll set up a primary server, load 100 million rows, move the database to a second server, and then run a heavy query on a branch while the primary keeps taking inserts. ## Prerequisites * **Two Linux servers** running Ubuntu 22.04 or later, x86-64 or arm64, in the same cloud region as your disk. This guide calls them `ch-primary` and `ch-analytics`. Any instance with 4 vCPUs and 16 GiB of memory is enough for the examples; we used EC2 `m7i.xlarge` instances in `us-east-1`. Both need [outbound access to Archil](https://docs.archil.com/mounting/linux.md#requirements). * **An Archil disk** for the database, created in the [Archil console](https://console.archil.com). Branches need Archil-managed storage or a flat-block bring-your-own-bucket disk; see [Branches & Checkpoints](https://docs.archil.com/concepts/branches-and-checkpoints.md). * **A disk token** for that disk, which both servers use to mount it. In the console, open the disk, select **Connections**, click **Add User**, and choose **Disk Token**. * **An API key** in the disk's region, for creating branches. On both servers, export the disk's name, its ID, its region, and the disk token: ```bash theme={null} export ARCHIL_DISK=myorg/clickhouse # owner-qualified name, or the disk ID export ARCHIL_DISK_ID=dsk-0123456789abcdef # the disk ID, for branch commands export ARCHIL_REGION=aws-us-east-1 export ARCHIL_MOUNT_TOKEN=... ``` ## Install ClickHouse and the Archil client Run this on **both** servers. It installs the Archil client and ClickHouse from ClickHouse's official package repository, then stops ClickHouse, which still points at its default data directory on the local disk: ```bash install.sh theme={null} set -euo pipefail curl -s https://archil.com/install | sh sudo apt-get update sudo apt-get 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 arch=$(dpkg --print-architecture)] https://packages.clickhouse.com/deb stable main" \ | sudo tee /etc/apt/sources.list.d/clickhouse.list sudo apt-get update sudo DEBIAN_FRONTEND=noninteractive apt-get install -y clickhouse-server clickhouse-client sudo systemctl stop clickhouse-server ``` ## Point ClickHouse at the Archil disk Still on **both** servers, add a configuration file that moves everything ClickHouse needs to restart onto the disk. The `path` setting covers table data, table and database definitions, and ClickHouse's own state. Users and grants created with SQL are stored separately, so `user_directories` moves them too. `tmp_path`, which ClickHouse uses for large sorts and joins that spill out of memory, stays on the local disk: it's scratch space, and it's faster there. ```xml /etc/clickhouse-server/config.d/archil.xml theme={null} /mnt/archil/clickhouse/ /mnt/archil/clickhouse/user_files/ /mnt/archil/clickhouse/format_schemas/ /mnt/archil/clickhouse/access/ /var/lib/clickhouse/tmp/ 1 1 ``` By default, ClickHouse doesn't `fsync` the parts it writes, so an `INSERT` can return before its data is durable. On an Archil disk, writes that haven't been flushed are held by the client on that server, and they're lost if the server dies. The two `merge_tree` settings make every `INSERT` wait until its part is durable, so a server failure never loses an acknowledged insert. They also make sure a [checkpoint](#run-heavy-queries-on-a-branch) contains every insert that finished before it. Each insert now waits for a round trip to Archil, so send data in large batches, as ClickHouse recommends anyway, or turn on [asynchronous inserts](https://clickhouse.com/docs/optimize/asynchronous-inserts). Finally, stop ClickHouse from ever starting while the disk isn't mounted. Without a guard, it would create an empty database in `/mnt/archil` on the local disk: ```bash theme={null} sudo mkdir -p /etc/systemd/system/clickhouse-server.service.d sudo tee /etc/systemd/system/clickhouse-server.service.d/archil.conf <<'EOF' [Service] ExecStartPre=/usr/bin/mountpoint -q /mnt/archil EOF sudo systemctl daemon-reload ``` ## Start the primary On `ch-primary`, mount the disk, hand a directory on it to the `clickhouse` user, and start the server: ```bash theme={null} sudo mkdir -p /mnt/archil sudo --preserve-env=ARCHIL_MOUNT_TOKEN archil mount "$ARCHIL_DISK" /mnt/archil --region "$ARCHIL_REGION" sudo install -d -o clickhouse -g clickhouse /mnt/archil/clickhouse sudo systemctl start clickhouse-server ``` `archil mount` takes the disk in exclusive mode, so `ch-primary` is the only server that can write to it. That's what ClickHouse expects: one server owns a data directory. Create a table and load 100 million rows of synthetic events: ```sql schema.sql theme={null} CREATE TABLE events ( ts DateTime, user_id UInt64, event LowCardinality(String), duration_ms UInt32 ) ENGINE = MergeTree ORDER BY (event, ts); INSERT INTO events SELECT toDateTime('2026-01-01 00:00:00', 'UTC') + intDiv(number, 100) AS ts, cityHash64(number) % 1000000 AS user_id, ['view', 'view', 'view', 'click', 'click', 'purchase'][number % 6 + 1] AS event, cityHash64(number, 'duration') % 5000 AS duration_ms FROM numbers(100000000); ``` ```bash theme={null} clickhouse-client --multiquery < schema.sql clickhouse-client -q "SELECT event, count() AS events, round(avg(duration_ms)) AS avg_ms FROM events GROUP BY event ORDER BY events DESC" --format PrettyCompactMonoBlock ``` ```text theme={null} ┌─event────┬───events─┬─avg_ms─┐ 1. │ view │ 50000001 │ 2499 │ 2. │ click │ 33333333 │ 2500 │ 3. │ purchase │ 16666666 │ 2500 │ └──────────┴──────────┴────────┘ ``` In our runs, the load took about 12 seconds, with every part fsynced. The query took 2.5 seconds right after the load, while ClickHouse was still merging the new parts in the background, and about 0.5 seconds on repeat runs. The table takes about 740 MiB after compression. Every file ClickHouse wrote for it is under `/mnt/archil/clickhouse`. > [!NOTE] > > By default, ClickHouse listens only on `localhost`, and its `default` user has no password, so this guide runs > `clickhouse-client` on the servers themselves. Before you open ClickHouse to the network, set up users and passwords as > described in ClickHouse's [access control guide](https://clickhouse.com/docs/operations/access-rights), then set > `listen_host`. ## Move the database to another server Because the whole database is on the disk, moving it is a remount. On `ch-primary`, stop ClickHouse and unmount the disk. `archil unmount` flushes every outstanding write before it returns: ```bash theme={null} sudo systemctl stop clickhouse-server sudo archil unmount /mnt/archil ``` Then, on `ch-analytics`, mount the disk and start ClickHouse: ```bash theme={null} sudo mkdir -p /mnt/archil sudo --preserve-env=ARCHIL_MOUNT_TOKEN archil mount "$ARCHIL_DISK" /mnt/archil --region "$ARCHIL_REGION" sudo systemctl start clickhouse-server clickhouse-client -q "SELECT count() FROM events" ``` ```text theme={null} 100000000 ``` `ch-analytics` didn't copy anything. It read the table's files from Archil as ClickHouse opened them, and anything already in Archil's server-side cache was served from there. In our runs, unmounting took about 0.15 seconds, mounting on `ch-analytics` 0.1 seconds, and starting ClickHouse about 1 second. The category query from the previous section took 0.9 seconds the first time on `ch-analytics`, against 0.5 seconds on a server that had already read the table. ### When a server dies If a server crashes instead of shutting down, it still holds the disk, and a normal `archil mount` on a replacement fails with `another client has an outstanding delegation to the root of the disk`. Once you know the old server is down, take the disk over with `--force`: ```bash theme={null} sudo --preserve-env=ARCHIL_MOUNT_TOKEN archil mount "$ARCHIL_DISK" /mnt/archil --region "$ARCHIL_REGION" --force sudo systemctl start clickhouse-server ``` `--force` revokes the old server's ownership of the disk, and any writes it hadn't flushed are lost. With `fsync_after_insert` on, those are only inserts that hadn't returned yet, and background merges that ClickHouse redoes on its own. At startup, ClickHouse logs `Removing temporary directory` for each part that was still being written, and deletes it, as it would after a crash on a local disk. ClickHouse commits a large `INSERT` one block at a time, so an insert that was still running can be partly applied. We tested this by force-stopping `ch-analytics` in the middle of an `INSERT ... SELECT`. `--force` mounted the disk on `ch-primary` in 0.1 seconds, ClickHouse started in about 1 second, every row from inserts that had returned was there, and no parts were detached as broken. Move the database back to `ch-primary` the same way before you continue: stop ClickHouse and unmount on `ch-analytics`, then mount and start on `ch-primary`. ## Run heavy queries on a branch A long query on the primary competes with inserts and with every other query for CPU, memory, and I/O. Instead, take a checkpoint of the running database, branch the disk from it, and run the query on `ch-analytics` against the branch. The primary keeps running the whole time. On `ch-primary`, take a checkpoint with a unique name: ```bash theme={null} export CHECKPOINT=analytics-$(date -u +%Y%m%dT%H%M%S) sudo archil checkpoints create /mnt/archil "$CHECKPOINT" ``` A checkpoint captures the disk exactly as it is at that moment, like a crash at that instant without losing any writes. `MergeTree` commits a part by renaming its finished directory into place, so the checkpoint contains only whole parts, and every insert that returned before it. Create a branch from the checkpoint. This needs your API key and the disk ID, and it can run anywhere the `archil` client is installed: ```bash theme={null} export ARCHIL_API_KEY=key-... archil branches --region "$ARCHIL_REGION" create "$ARCHIL_DISK_ID" "$CHECKPOINT" --from-checkpoint "$CHECKPOINT" ``` On `ch-analytics`, mount the branch and start ClickHouse on it. Pass the same `CHECKPOINT` value you used on `ch-primary`: ```bash theme={null} export CHECKPOINT=analytics-... sudo --preserve-env=ARCHIL_MOUNT_TOKEN archil mount "$ARCHIL_DISK" /mnt/archil --region "$ARCHIL_REGION" --branch "$CHECKPOINT" sudo systemctl start clickhouse-server ``` In our runs, the checkpoint took 40 ms and the branch 80 ms, both measured from the command line. ClickHouse logs `Status file ... already exists - unclean restart` when it starts on the branch, because the checkpoint was taken while the primary was running; that's expected. `ch-analytics` now has its own writable copy of the database as of the checkpoint. Run the heavy query there: ```bash theme={null} clickhouse-client -q "SELECT user_id, count() AS events, sum(duration_ms) AS total_ms FROM events GROUP BY user_id ORDER BY total_ms DESC LIMIT 3" --format PrettyCompactMonoBlock ``` ```text theme={null} ┌─user_id─┬─events─┬─total_ms─┐ 1. │ 596401 │ 154 │ 400707 │ 2. │ 761945 │ 139 │ 395268 │ 3. │ 667180 │ 145 │ 394841 │ └─────────┴────────┴──────────┘ ``` Meanwhile, the primary keeps taking inserts. On `ch-primary`, add 10 million rows: ```bash theme={null} clickhouse-client -q "INSERT INTO events SELECT toDateTime('2026-01-01 00:00:00', 'UTC') + intDiv(number, 100), cityHash64(number) % 1000000, ['view', 'view', 'view', 'click', 'click', 'purchase'][number % 6 + 1], cityHash64(number, 'duration') % 5000 FROM numbers(100000000, 10000000)" clickhouse-client -q "SELECT count() FROM events" ``` ```text theme={null} 110000000 ``` The branch on `ch-analytics` still has 100 million rows. Nothing either server does reaches the other: the branch can create tables, run backfills, or drop data without touching the primary. When you're done with the branch, stop ClickHouse and unmount it: ```bash theme={null} sudo systemctl stop clickhouse-server sudo archil unmount /mnt/archil ``` > [!TIP] > > The server on a branch runs background merges like any other ClickHouse server. If you only read from it, run > `SYSTEM STOP MERGES` after it starts, so all of its CPU goes to your queries. > [!WARNING] > > You can't delete branches and checkpoints yet. Use a new name for each branch, as the timestamped `CHECKPOINT` above does. > See [Branches & Checkpoints](https://docs.archil.com/concepts/branches-and-checkpoints.md). Checkpoints also work as instant backups. If someone drops a table on the primary, branch the disk from a checkpoint taken before the drop, start ClickHouse on the branch, and copy the table back with the [`remote` table function](https://clickhouse.com/docs/sql-reference/table-functions/remote). ## Running it in production * **One server per disk or branch.** Two ClickHouse servers must never share one data directory, and exclusive mounts enforce that. To scale writes, shard: give each shard its own disk and query across them with a [`Distributed`](https://clickhouse.com/docs/engines/table-engines/special/distributed) table. * **No replication needed for durability.** Use plain `MergeTree` tables. ClickHouse Keeper and `ReplicatedMergeTree` are only worth running if you need a standby that's already started when the primary fails. * **Failover time grows with the number of parts.** At startup, ClickHouse reads a few small files from every active part. Keep the part count in check with a sensible partition key and batched inserts, and measure a remount on your own data. * **Size the client cache for your working set.** The Archil client caches file data in memory, and by default it uses at most 2 GiB. On a dedicated ClickHouse server, raise it with `--max-cache-mb` when you mount. See [Client caching](https://docs.archil.com/details/caching.md). * **Mount at boot.** The guide's `ExecStartPre` check keeps ClickHouse from starting until the disk is mounted, and the service retries every 30 seconds. Mount the disk from your provisioning or a boot script, and ClickHouse starts once the mount appears. ## Run it in Archil sandboxes Everything above also works in [persistent sandboxes](https://docs.archil.com/compute/sandboxes/introduction.md), and that's the most cost-effective way to run it: reads between a sandbox and its disks are [transfer within Archil](https://docs.archil.com/administration/billing.md#data-transfer), which is free. The official `clickhouse/clickhouse-server` image already includes the `archil` client in a sandbox. Sandboxes don't run systemd, so ClickHouse runs as a [sandbox service](https://docs.archil.com/compute/sandboxes/introduction.md) instead, which restarts if it crashes. This script sets up the primary. It's the configuration from earlier in this guide, with ClickHouse registered as a service: ```bash primary-setup.sh theme={null} set -euo pipefail install -m 644 /root/archil.xml /etc/clickhouse-server/config.d/archil.xml mkdir -p /var/lib/clickhouse/tmp && chown -R clickhouse:clickhouse /var/lib/clickhouse install -d -o clickhouse -g clickhouse /mnt/archil/clickhouse archil-sandbox services create clickhouse -- \ setpriv --reuid=clickhouse --regid=clickhouse --init-groups /usr/bin/clickhouse-server --config-file=/etc/clickhouse-server/config.xml timeout 60 bash -c 'until clickhouse-client -q "SELECT 1" >/dev/null 2>&1; do sleep 0.5; done' ``` Create the primary with the disk mounted at `/mnt/archil`, upload `archil.xml`, `schema.sql`, and the script, and run it: ```typescript primary.ts theme={null} import { readFile } from "node:fs/promises"; import * as archil from "disk"; const [disk] = await archil.listDisks({ name: "clickhouse" }); const sandbox = await archil.createSandbox({ name: "ch-primary", baseImage: "clickhouse/clickhouse-server:26.10", vcpuCount: 4, memSizeMiB: 16384, mounts: [{ disk, path: "/mnt/archil" }], }); for (const file of ["archil.xml", "schema.sql", "primary-setup.sh"]) { await sandbox.files.uploadFile(await readFile(file), `/root/${file}`); } const setup = await sandbox.exec("bash /root/primary-setup.sh && clickhouse-client --multiquery < /root/schema.sql"); if (setup.exitCode !== 0) throw new Error(setup.stderr); ``` ```python primary.py theme={null} import archil disk = archil.list_disks(name="clickhouse")[0] sandbox = archil.create_sandbox( name="ch-primary", base_image="clickhouse/clickhouse-server:26.10", vcpu_count=4, mem_size_mib=16384, mounts=[archil.SandboxMountSpec(disk=disk, path="/mnt/archil")], ) for file in ["archil.xml", "schema.sql", "primary-setup.sh"]: sandbox.files.upload_file(file, f"/root/{file}") setup = sandbox.exec("bash /root/primary-setup.sh && clickhouse-client --multiquery < /root/schema.sql") if setup.exit_code != 0: raise RuntimeError(setup.stderr) ``` Take checkpoints in the primary with `archil checkpoints create /mnt/archil "$CHECKPOINT"`, exactly as on a server, and create branches with `archil branches create` as before. A sandbox's `mounts` attach the disk itself, not a branch, so the analytics sandbox is created without any and mounts the branch itself with `archil mount --branch`. Pass the disk's ID, region, and token, and the checkpoint name, as environment variables: ```bash analytics-setup.sh theme={null} set -euo pipefail mkdir -p /mnt/archil archil mount "$ARCHIL_DISK" /mnt/archil --region "$ARCHIL_REGION" --branch "$CHECKPOINT" install -m 644 /root/archil.xml /etc/clickhouse-server/config.d/archil.xml mkdir -p /var/lib/clickhouse/tmp && chown -R clickhouse:clickhouse /var/lib/clickhouse archil-sandbox services create clickhouse -- \ setpriv --reuid=clickhouse --regid=clickhouse --init-groups /usr/bin/clickhouse-server --config-file=/etc/clickhouse-server/config.xml timeout 120 bash -c 'until clickhouse-client -q "SELECT 1" >/dev/null 2>&1; do sleep 0.2; done' ``` ```typescript analytics.ts theme={null} import { readFile } from "node:fs/promises"; import * as archil from "disk"; const sandbox = await archil.createSandbox({ name: "ch-analytics", baseImage: "clickhouse/clickhouse-server:26.10", vcpuCount: 4, memSizeMiB: 16384, }); for (const file of ["archil.xml", "analytics-setup.sh"]) { await sandbox.files.uploadFile(await readFile(file), `/root/${file}`); } const setup = await sandbox.exec("bash /root/analytics-setup.sh", { env: { ARCHIL_DISK: process.env.ARCHIL_DISK_ID!, ARCHIL_REGION: process.env.ARCHIL_REGION!, ARCHIL_MOUNT_TOKEN: process.env.ARCHIL_MOUNT_TOKEN!, CHECKPOINT: process.env.CHECKPOINT!, }, }); if (setup.exitCode !== 0) throw new Error(setup.stderr); ``` ```python analytics.py theme={null} import os import archil sandbox = archil.create_sandbox( name="ch-analytics", base_image="clickhouse/clickhouse-server:26.10", vcpu_count=4, mem_size_mib=16384, ) for file in ["archil.xml", "analytics-setup.sh"]: sandbox.files.upload_file(file, f"/root/{file}") setup = sandbox.exec( "bash /root/analytics-setup.sh", env={ "ARCHIL_DISK": os.environ["ARCHIL_DISK_ID"], "ARCHIL_REGION": os.environ["ARCHIL_REGION"], "ARCHIL_MOUNT_TOKEN": os.environ["ARCHIL_MOUNT_TOKEN"], "CHECKPOINT": os.environ["CHECKPOINT"], }, ) if setup.exit_code != 0: raise RuntimeError(setup.stderr) ``` In our runs in `aws-us-west-2`, the 100-million-row load took about 7 seconds in a 4 vCPU, 16 GiB sandbox. In the analytics sandbox, `archil mount --branch` took under half a second, ClickHouse was ready about 2 seconds later, and the heavy query from the previous section returned the same result in about 2 seconds. Delete the analytics sandbox when you're done with the branch: it mounts the branch from its setup script, so it isn't meant to be stopped and started again. ## Cost compared with ClickHouse Cloud Running this setup in Archil sandboxes costs **17% to 61% less** than ClickHouse Cloud's published example services. This section compares list prices in AWS `us-east-1` as of November 2026: Archil's Developer and Team rates from [Pricing & Billing](https://docs.archil.com/administration/billing.md), and ClickHouse Cloud's from [its pricing page](https://clickhouse.com/pricing). **One server instead of two.** ClickHouse Cloud's Scale and Enterprise services keep at least two replicas once they're larger than 12 GiB. On Archil, the disk already keeps every write durable across Availability Zones, so one ClickHouse server per shard is enough. Per unit of compute, a sandbox also costs less: | Per 2 vCPU and 8 GiB, per hour | | | - | - | | ClickHouse Cloud Scale | \$0.298 | | ClickHouse Cloud Enterprise | \$0.390 | | Archil sandbox | **\$0.27** | **Storage without a second copy.** Archil stores the data for $0.025 per GB-month, or $25 per TB. ClickHouse Cloud charges $25.30 per TB, and bills its default daily backup as a second copy at the same rate; on Archil, checkpoints take its place. Archil also bills $0.20 per GiB-month for active data, which is data read or written in the last hour, so the total depends on how much of the table your queries touch each hour. Here is the monthly cost of ClickHouse Cloud's published examples, running 24 hours a day, next to one sandbox the size of one of their replicas, with 10% and 100% of the data active: | ClickHouse Cloud example | ClickHouse Cloud | Archil, 10% active | Archil, 100% active | | - | - | - | - | | Scale, 2 × 8 GiB, 1 TB | \$499 | **\$241** (52% less) | **\$409** (18% less) | | Scale, 2 × 16 GiB, 2 TB | \$987 | **\$483** (51% less) | **\$818** (17% less) | | Enterprise, 2 × 64 GiB, 10 TB | \$5,208 | **\$2,017** (61% less) | **\$3,694** (29% less) | The Archil columns are one sandbox (2 vCPU and 8 GiB, 4 vCPU and 16 GiB, or 16 vCPU and 64 GiB) running all month, plus storage. A branch sandbox for heavy queries, like a ClickHouse Cloud warehouse, only costs compute while it runs. To see where your workload falls between the two Archil columns, run it for a day and check the disk's active data on its **Usage** tab in the [Archil console](https://console.archil.com). ## Clean up On each server that's running ClickHouse, stop it and unmount the disk: ```bash theme={null} sudo systemctl stop clickhouse-server sudo archil unmount /mnt/archil ``` Then delete the disk in the Archil console, and terminate both servers. If you ran ClickHouse in sandboxes, stop and delete them instead. ## Next steps > [card] [Branches & Checkpoints](https://docs.archil.com/concepts/branches-and-checkpoints.md) > > How checkpoints and branches work, and their CLI commands. > [card] [Mount on Linux](https://docs.archil.com/mounting/linux.md) > > Requirements, authorization, and mount options for Linux servers. > [card] [Client caching](https://docs.archil.com/details/caching.md) > > Size, warm, and refresh the client's in-memory cache. > [card] [Analytics with DuckDB](https://docs.archil.com/guides/analytics/duckdb.md) > > Query Parquet in S3 in place, without loading it into a database. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.