SquirrelDB Storage Backends

SquirrelDB can store its time series in Cassandra or in ClickHouse. The backend is selected with a single option:

backend: clickhouse # or "cassandra", the default

The backend owns everything that must be durable: the points, the index, the mutable labels and the telemetry. It is the source of truth, and it serves every read.

Everything that is not storage stays identical between the two backends: the Prometheus remote read and write endpoints, the PromQL endpoints, multi-tenancy, the TTL headers and the mutable labels API all behave the same way.

Cassandra or ClickHouse

	Cassandra	ClickHouse
Short term store	In-memory batcher, or Redis	Write-ahead log in Redis (in memory when Redis is not configured)
Write visibility	Immediate (read-your-writes)	After the write-ahead log is flushed, about 5 seconds
Pre-aggregation	Daily batch job, 5-minute resolution, throttled	Continuous, 15-minute buckets, computed by ClickHouse on insert
Availability	Cassandra cluster with a replication factor of 3	ClickHouse cluster (ReplicatedMergeTree) and ClickHouse Keeper
Node discovery	Automatic, from any reachable node	None: every server node must be listed in clickhouse.addresses
Overwriting a point	Last write wins	Last write wins, if the two writes are at least one hour apart
Data migration	—	Supported from Cassandra

Cassandra remains fully supported and is still the default. ClickHouse is the better fit for large deployments: it stores points column by column, each with its own compression codec, and keeps its pre-aggregate up to date continuously instead of in a nightly batch. The trade-off is a short delay before a freshly written point becomes queryable.

Requirements

• ClickHouse, reachable from SquirrelDB on its native protocol (port 9000 by default). The HTTP interface is not used.
• A database. SquirrelDB creates its own tables, but not the database that holds them: create it beforehand, or point clickhouse.database at an existing one.
• Redis, for anything more than a single SquirrelDB instance. The write-ahead log lives in Redis, which is what makes the instances stateless and the buffered points durable.

A minimal configuration looks like this:

backend: clickhouse

clickhouse:
  addresses:
    - "clickhouse:9000"
  database: squirreldb
  username: squirreldb_user
  password: mypassword

Every ClickHouse option is described in the configuration reference.

Supported deployments

Which deployment you run is decided by the ClickHouse server configuration plus a single SquirrelDB option, clickhouse.cluster_name.

Deployment	ClickHouse	cluster_name	Table engines
Single node	1 server, no Keeper	empty	MergeTree
Single-node cluster	1 server, 1 Keeper	set	ReplicatedMergeTree
Multi-node cluster	2+ servers, 3 Keepers	set	ReplicatedMergeTree

Single node is the simplest and stays fully supported: no Keeper, no cluster configuration. Use it to evaluate SquirrelDB, and in production wherever one server meets your requirements.

Single-node cluster is not more available than a single node — one server is still one point of failure — but the replication machinery is already in place, so adding a second server later is just starting it. If there is any chance you will grow, start here: it costs one Keeper and saves you the migration described below.

Multi-node cluster is the highly available setup, described on the high availability page.

Note

Replication is not free compared to a plain single node: replicated tables need Keeper. While Keeper is unreachable they go read-only and SquirrelDB cannot write, where a plain MergeTree would keep accepting writes. And a Keeper sharing the disk of the server dies with it.

Growing a deployment

Go single node → single-node cluster → multi-node cluster, in that order. The first move is a data migration, and it is much easier to reason about with exactly one ClickHouse server. The second move needs no migration of your own: the tables are already replicated, so ClickHouse itself copies the data to the new server.

Single node to single-node cluster

1. Configure ClickHouse

Server-side configuration is out of scope here (see the ClickHouse documentation); what SquirrelDB needs is:

• a Keeper. One is enough if you stay on a single-node cluster; go straight to three if you intend to add a second ClickHouse server. Never two: a two-node Keeper needs both to be up, which is worse than one. It can be embedded in clickhouse-server (<keeper_server>) or run separately.
• the cluster declaration in <remote_servers> — it can start with this server as its only replica. Its name is what you will put in cluster_name.
• the shard and replica macros, used by the Keeper path of the tables.

Restart ClickHouse and check all three:

SELECT cluster, host_name, is_local FROM system.clusters WHERE cluster = 'main_cluster';
SELECT macro, substitution FROM system.macros;
SELECT count() FROM system.zookeeper WHERE path = '/';  -- proves Keeper answers

2. Point SquirrelDB at the cluster

Set the option and restart:

clickhouse:
  cluster_name: main_cluster

SquirrelDB starts and keeps serving reads and writes, but it now sees tables whose engine contradicts the configuration, and says so:

• an error log per table, at startup;
• the host is excluded from the ready set: squirreldb_clickhouse_ready_nodes drops to 0 and the reason shows in curl -s http://squirreldb:9201/debug/clickhouse/host.

Nothing is replicated in this state — it is a transition, not a destination.

3. Look at the plan

curl -XPOST 'http://squirreldb:9201/debug/clickhouse/migrate_to_cluster?dry_run=1'

This reports the ClickHouse host holding the data, and for each table what will happen and which partitions (and how much disk) will move. It changes nothing, so run it as often as you like.

4. Migrate

curl -XPOST -N http://squirreldb:9201/debug/clickhouse/migrate_to_cluster

Per table, it renames the single-node table to <table>_pre_cluster, creates the replicated table under the original name, and attaches the partitions of the source to it. Attaching uses filesystem hardlinks, so no data is copied whatever the volume.

What to expect while it runs:

• writes to ClickHouse are blocked, by design. Incoming points accumulate in the write-ahead log (Redis, or memory without Redis) and land in ClickHouse once the migration finishes. Clients keep succeeding until the log passes its backpressure thresholds, where SquirrelDB slows them down, then blocks them until it drains.
• reads fail for the moments a table is between its rename and its creation.
• run it with a single SquirrelDB instance if you can. Others keep working, but their reads and writes error the same way, and one restarting mid-migration re-creates the write path: the migration still finishes correctly, but writes are back in the replicated tables, so a rollback would lose everything written since.
• it is re-callable. If it fails, times out, or you interrupt it, call it again: it works out what is left from ClickHouse itself and resumes. Interruption is only ever honoured between two partitions. A partition interrupted mid-attach is the one case a re-call can attach data twice; that only skews the pre-aggregate, which POST /debug/clickhouse/agg/rebuild?from=&to= recomputes over the affected range.
• on failure, writes stay down. That is deliberate: letting writes into the new replicated tables would strand them if you then rolled back. The response tells you the two ways out (finish, or roll back).

5. Check, then clean up

curl -s http://squirreldb:9201/debug/clickhouse/host

The host must be back to ready, which means SquirrelDB found every table with the engine this mode expects. At this point SquirrelDB is fully operational; query some history — a dashboard over a range older than the migration — to confirm the migrated data is served.

The migration leaves each <table>_pre_cluster in place, emptied (its data is under the detached/ directory of the table), as a rollback net. Dropping them is a separate, explicit call:

curl -XPOST 'http://squirreldb:9201/debug/clickhouse/migrate_to_cluster?cleanup=1'

Caution

Do it once you have checked your data, and do not forget it: right after the migration the sources cost almost no disk (they share it with the migrated tables through the hardlinks), but that sharing ends as merges and TTL rewrite the migrated tables, so they grow into a full duplicate of your pre-migration data.

If you do not want the net at all — a test instance, a small dataset — pass ?cleanup=1 on the migration call itself and it drops them at the end.

Rolling back

Rolling back is possible as long as the <table>_pre_cluster tables are still there, so until you run ?cleanup=1. The cost is the points written to the replicated tables since the migration started: they stay behind. That cost is nil while writes are still down (a failed migration you decide not to finish), and grows once they have resumed.

For every <table>_pre_cluster still present, drop the replicated table, re-attach the detached partitions of the source, and put the source back under the original name:

DROP TABLE <table> ON CLUSTER main_cluster SYNC;
ALTER TABLE <table>_pre_cluster ATTACH PARTITION ID '<id>';   -- for each detached partition
RENAME TABLE <table>_pre_cluster TO <table>;

Both lists come from ClickHouse:

SELECT name FROM system.tables WHERE database = currentDatabase() AND name LIKE '%\_pre\_cluster';
SELECT DISTINCT partition_id FROM system.detached_parts WHERE table = '<table>_pre_cluster';

Then restart SquirrelDB with cluster_name removed and run:

curl -XPOST http://squirreldb:9201/debug/clickhouse/create_tables

Single-node cluster to multi-node cluster

There is no data migration to run here — you are adding a replica to tables that are already replicated.

1. Go to three Keepers. A single Keeper is a single point of failure, which defeats the purpose of the second server.

2. Start the new ClickHouse server with the same cluster configuration and its own replica macro, and add it to <remote_servers> on every server.

3. Create the tables on it. Ask any SquirrelDB to re-run the schema creation (CREATE ... IF NOT EXISTS ON CLUSTER, a no-op on the existing server):

   curl -XPOST http://squirreldb:9201/debug/clickhouse/create_tables

   ClickHouse then replicates the existing data to it on its own.

4. Add its address to clickhouse.addresses in every SquirrelDB and restart them. The order of steps 3 and 4 does not matter: SquirrelDB excludes a host that is missing tables or whose replica is still catching up, and includes it once ready.

5. Watch it catch up:

   curl -s http://squirreldb:9201/debug/clickhouse/host

   SELECT database, table, absolute_delay FROM system.replicas WHERE absolute_delay > 0;

Recovering a lost ClickHouse node

As long as a server is only stopped and started, ClickHouse replication catches it up on its own — there is nothing to do. SquirrelDB health-checks each host and routes only to ready ones (reachable, holding the tables, replica not lagging), so a node that is down or still resyncing is excluded until it catches up, then re-included: no address edit, no load-balancer change.

The procedure below is only for a server that lost its data and is recreated empty (the ClickHouse process runs, but with no tables). Run the SQL on a healthy node, and replace replica01 with the replica macro of the recreated node.

1. Drop the metadata of the dead replica from Keeper, for every replicated table. This stale registration is the one thing blocking re-creation; dropping it is kept manual on purpose, since SYSTEM DROP REPLICA is destructive. It only affects the inactive replica: it refuses to drop a live one.

   -- for each table returned by:
   --   SELECT name FROM system.tables
   --   WHERE database = 'squirreldb' AND engine LIKE 'Replicated%'
   SYSTEM DROP REPLICA 'replica01' FROM TABLE squirreldb.<table>;

2. Recreate the tables. Ask any SquirrelDB instance to re-run the schema creation, which re-attaches the rebuilt node now that its stale replica is gone. SquirrelDB never recreates tables on restart (only at first bootstrap), and uses name-based Keeper paths, so re-creating a table re-attaches to the existing data instead of forking it.

   curl -XPOST http://squirreldb:9201/debug/clickhouse/create_tables

3. Nothing else to do: SquirrelDB re-includes the node once it has resynced. Watch the per-host view SquirrelDB uses for routing, or the squirreldb_clickhouse_ready_nodes and squirreldb_clickhouse_reachable_nodes metrics:

   curl -s http://squirreldb:9201/debug/clickhouse/host

   And watch the replica catch up directly (no rows means it is caught up):

   SELECT database, table, absolute_delay FROM system.replicas
   WHERE is_session_expired OR absolute_delay > 0;

Note

If you skip step 1 and recreate the table first, it fails with Replica ... already exists. This is a safe error, not a split-brain: run step 1 and retry.