The eight gaps ClickHouse documents for object storage outside its cloud

ClickHouse's guide to running its open-source build on object storage calls the setup more complicated and recommends its cloud. The docs then list what is missing, in pieces. We collected eight items, and for each one we say what it means on a running node and what we are doing about it, with the config or the command.

ClickHouse’s guide to running its open-source build on object storage describes the layout as “more complicated compared to standard ClickHouse deployments” and recommends ClickHouse Cloud instead. Cloud runs SharedMergeTree, and the RFC that asked for the open-source path to be fixed records that the engine will stay closed. Outside their cloud, that documentation is also the list of what you have to build yourself.

We read that documentation, the Altinity RFC and blog post that expand on it, a community literature review and Cloudflare’s own pages, and came away with eight gaps.

Our node holds every intent table in a Cloudflare R2 bucket behind a local NVMe cache, keeps the part-to-object map on its local disk, runs a single-node Keeper, and uses Replicated table engines without a second replica. The architecture article has the disk layers and the table definitions.

1. Users that live outside configuration management

ClickHouse’s access control page describes two ways to define a user, either as XML in the server configuration or as a SQL statement stored under the server’s access path, and adds that “You can’t manage the same access entity by both configuration methods simultaneously.”

A user created with CREATE USER lives in one directory on one machine, and ours were all created that way. A metadata backup that skips that directory restores a node with the default user and nobody else, so every service that connects fails at login.

We are moving every fleet user into XML under users.d/, rendered by the configuration playbook from password hashes kept in the secret store, with the grants written into the same file as GRANT statements without a grantee:

<clickhouse>
  <users>
    <intent_user>
      <password_sha256_hex>{{ hash_from_secret_store }}</password_sha256_hex>
      <networks><ip>::/0</ip></networks>
      <profile>default</profile>
      <grants>
        <query>GRANT SELECT ON intent.*</query>
        <query>GRANT SELECT ON system.parts</query>
        <query>GRANT SELECT ON system.disks</query>
        <query>GRANT SELECT ON system.query_log</query>
      </grants>
    </intent_user>
  </users>
</clickhouse>

The playbook computes the hashes when it runs, so they never land in git. Moving a host over takes one maintenance window. We render the XML and then drop the SQL users.

2. The part map on the local disk

Alex Zaitsev’s RFC (ClickHouse issue 54644, 2023-09-14) puts the problem in one sentence: “Data is stored in two places: local metadata files and S3 objects.” You can’t attach a table from the bucket without those local files, and every change writes to two places with no transaction between them.

The bucket holds objects with opaque names, and the small local files that map each part to its objects are the only index into it. Our nightly backup saves that map. It skips Keeper’s state, and without that state a restored Replicated table comes up read-only until someone runs SYSTEM RESTORE REPLICA on it, table by table.

We’re moving the backup job to every six hours, and before it syncs it will ask Keeper for a fresh snapshot and copy that up alongside the part map and the table definitions. The prefix it writes to gets a Cloudflare bucket lock, introduced on 2025-03-06, which prevents “the deletion and overwriting of objects” for a set period.

# in the backup job, before the sync: ask Keeper for a fresh snapshot.
# Keeper answers with the last committed log index of the snapshot it scheduled.
echo csnp | nc 127.0.0.1 9181

# once, on the bucket: lock the backup prefix, and only that prefix.
# positional arguments: bucket, rule name, prefix
npx wrangler r2 bucket lock add <bucket> metadata-14d _metadata-backup/ --retention-days 14

The lock goes on the backup prefix only. Merges delete superseded parts as a matter of course, and a lock on the data prefix would stop ClickHouse from cleaning up after itself.

Two numbers matter if this machine dies. The first is how much recent data a rebuild could miss, and with a snapshot every six hours that is at most six hours, which the feed loads again on its own because it remembers where it left off. The second is how long the signals are unavailable while we rebuild, and we haven’t measured that yet.

3. Zero-copy replication

The external disks page says zero-copy replication “is not ready for production” and that it has been disabled by default since 22.8. The RFC calls it unreliable and known for bugs, and Robert Hodges of Altinity (2024-05-30) writes that Altinity does not recommend it “unless you are skilled in ClickHouse and understand the risks of use”.

Zero-copy lets two replicas share one set of objects in the bucket. Ours had the setting on, though with a single replica it never ran, so turning it off costs nothing today. It is one setting in the server configuration, and each table picks it up at the next restart:

<merge_tree>
  <allow_remote_fs_zero_copy_replication>0</allow_remote_fs_zero_copy_replication>
</merge_tree>

With it off, each replica writes its own objects to its own bucket, so storage doubles per replica. We accept that over the alternatives. The s3_plain_rewritable disk keeps its metadata in the bucket, but the docs say “mutations and replication of tables are not supported”, and the literature review adds that it allows a single writer. SharedMergeTree stays closed.

With this setting on, two servers would share one set of files, and one of them could delete a file the other still needs. We’ve turned it off, so each server keeps its own full copy of the signals and contacts. It costs us twice the storage, and we accept that.

4. No alert on stale data

No vendor page names this one. The feed that fills our largest table can stop while the server stays up and every health check stays green.

The exporter we’re writing checks the data rather than the service. It reports the age of the newest row in the signals table and of the newest part in the crawl table, and fires when either passes the feed’s normal lag plus a margin. It carries a dead-man alert on itself, so a silent exporter pages someone too. The value in the output is illustrative:

-- the freshness query. EVENT_DATE is the partition key, so max() reads
-- part metadata, not rows.
SELECT dateDiff('second', max(EVENT_DATE), now()) AS staleness_seconds
FROM intent.optimized_signals_v1;

-- ┌─staleness_seconds─┐
-- │            190800 │
-- └───────────────────┘

5. Bucket operations without a meter

Cloudflare’s R2 pricing page (updated 2026-08-07) meters Class A operations, and PutObject, CreateMultipartUpload, UploadPart and CompleteMultipartUpload are all on that list. A table part is several objects, a large object is several multipart chunks, and a merge writes every one of them again. ClickHouse’s counters in system.events show what one server asked for, and only the bucket’s own metrics show what was billed. We were reading neither number.

A second exporter reads the bucket’s operation and storage counts from Cloudflare’s GraphQL analytics every fifteen minutes and publishes them as r2_ops_total{bucket,action} and r2_bucket_bytes{bucket}. The number we want from it is bytes uploaded per byte ingested, so write amplification becomes a dashboard line with an alert on it.

6. Recovery time

Nothing in the guide says how long a node takes to come back. One of our commit messages estimates a rebuild in minutes, and nobody has timed it.

The drill runs on a fresh machine with the latest metadata snapshot and the bucket, and a stopwatch that runs from the first command until the proxy is repointed and the first warm query answers. The restore drill is the account of it.

7. One copy under one key

The RFC notes that “backups are not trivial since two different sources need to be backed up separately”. Our metadata has a backup and our objects have none. Everything sits in a single bucket in a single Cloudflare account, and one token can delete it. One person holds the client-side encryption key. A mistaken delete with that token, or a lost key, would lose the dataset.

The second copy comes from ClickHouse’s native BACKUP, written to a bucket in a second Cloudflare account with its own billing and its own administrators, as a weekly base plus a nightly incremental against it. The server runs the backup itself, so it copies a table’s parts and definition as one set. The docs say nothing about parts merged away mid-run, so we don’t claim that. A bucket-to-bucket sync copies keys with no knowledge of which parts a table needs while merges delete some of them underneath it. Egress from R2 is free, so the copy costs only the operations and storage in the second account:

-- weekly base
BACKUP DATABASE intent
TO S3('<second-account endpoint>/dr/base-2026w37', '<key>', '<secret>');

-- nightly, against the latest base
BACKUP DATABASE intent
TO S3('<second-account endpoint>/dr/inc-2026-09-11', '<key>', '<secret>')
SETTINGS base_backup = S3('<second-account endpoint>/dr/base-2026w37', '<key>', '<secret>');

The backup settings in ClickHouse’s source carry a decrypt_files_from_encrypted_disks switch that defaults to false, so BACKUP copies the files as they sit on the encrypted disk, still ciphertext, and a restore from the second bucket needs the same key. We’ll confirm that on the first run. It also means the second copy is only as safe as the key, so the key gets an escrow copy, sealed and held by a second officer.

8. No second replica, and a bucket in the wrong place

The literature review’s verdict on ReplicatedMergeTree over S3 is that, with the metadata on each server’s disk, it “doesn’t provide any of the cost or efficiency benefits”. Our node has never had a second replica. Its bucket was also created an ocean away from the compute, and a location hint can’t be changed after creation, so every cache miss crosses that distance.

A second server closes both gaps at once. It becomes replica two of every intent table, with its own bucket created in the right region. Keeper grows from one node to three through its reconfig command, which the Keeper docs describe as partial support and gate behind keeper_server.enable_reconfiguration, and the third member is a voting node on a small machine that holds no table data. Because zero-copy is off, the second replica fetches every part from the first and writes it to its own bucket, so the replication doubles as the migration, and afterwards we can rebuild the first node against a bucket in the right region too.

Parts travel from the peer rather than through the bucket, over a 1 Gbit link at roughly 100 MB/s in practice, and every part that arrives is a metered upload on the receiving side. At that rate a terabyte takes about three hours, and the first node’s own ingest, merges and cache misses share the same port, so several terabytes is a day or more. Once system.replication_queue reaches zero, the proxy can send reads to both servers.

Orphaned objects

Robert Hodges’s post describes what happens when the local metadata drifts out of sync with the bucket. Objects that nothing references stay behind and are billed for as long as they exist. A crashed merge or an interrupted bulk load leaves them, and we have no count of ours.

A weekly job lists the data prefix, subtracts every path in system.remote_data_paths (the table that maps local metadata paths to blob paths in the bucket), skips anything younger than two hours so parts in flight are left alone, and reports what remains. It will only delete keys that show up as orphans two weeks in a row, and only after a month of report-only runs.

Check it for yourself

Three queries and one listing tell you which of these gaps you have. The counts in the output are illustrative.

-- Gap 1: where each user is stored. local_directory means SQL-created,
-- on this box only; users_xml means configuration, recreated on rebuild.
SELECT name, storage FROM system.users ORDER BY name;

-- ┌─name────────┬─storage─────────┐
-- │ default     │ users_xml       │
-- │ intent_user │ local_directory │
-- └─────────────┴─────────────────┘

-- Gap 3: zero-copy replication. A value of 1 means on.
SELECT name, value FROM system.merge_tree_settings
WHERE name = 'allow_remote_fs_zero_copy_replication';

-- ┌─name──────────────────────────────────┬─value─┐
-- │ allow_remote_fs_zero_copy_replication │ 1     │
-- └───────────────────────────────────────┴───────┘

-- Housekeeping: how many objects the node references in the bucket
SELECT count() AS referenced_blobs
FROM system.remote_data_paths WHERE disk_name = 's3_main';

-- ┌─referenced_blobs─┐
-- │          1200000 │
-- └──────────────────┘

# ... against what the bucket holds under the data prefix. A single
# list-objects-v2 page stops at 1,000 keys; s3 ls --recursive walks
# every page and prints one line per object.
aws --endpoint-url "$R2_ENDPOINT" s3 ls --recursive "s3://<bucket>/<data prefix>/" | wc -l
#  1210000

Objects in the bucket beyond what the node references, minus parts written in the last few minutes, are orphans.

Still open

Three measurements are missing, and we will publish them: how long the recovery takes, how many orphaned objects the bucket holds, and how many hours a second replica needs to join over a 1 Gbit port.

Further reading

• ClickHouse, Separation of storage and compute.
• ClickHouse, External disks for storing data.
• Alex Zaitsev, RFC: MergeTree over S3 improvements, 2023-09-14.
• Robert Hodges, Altinity, ClickHouse MergeTree on S3: intro and architecture, 2024-05-30.
• kasimeka, ClickHouse compute-storage separation literature review, 2026-01-07.
• ClickHouse docs behind the checks: access control, user settings, system.users, system.remote_data_paths, backup and restore, Keeper.
• Cloudflare, R2 bucket locks and the changelog entry that introduced them.

Treat the vendor’s list of warnings as the list of things you have to build.