ClickHouse®

A managed database based on ClickHouse 26.8 for analytics and event data: one server, backed up every night, attached to your apps as CLICKHOUSE_URL.

What you get

Each ClickHouse database is ClickHouse 26.8 LTS, from the unmodified upstream image. It is a single instance: one server, with no live copy on another server (see Availability). It has one database, app, and one user, app. It runs in its own microVM, reachable only by the apps you attach it to; it has no public address.

$ deplane clickhouse create events
$ deplane clickhouse attach events --app web

The first start takes about 15 seconds. An app has one ClickHouse database, a ClickHouse database may serve several of your apps (and their workers and cron jobs), and an app may also have a Postgres database, a Redis and a MySQL. Pull request previews do not get the ClickHouse database.

In the dashboard, open Databases and find ClickHouse.

Sizes

A ClickHouse database needs 2 GB of your plan's memory, and counts as one large instance against your plan, like an app instance. There is no smaller size. The Beta plan has 1.5 GB of memory, so it cannot hold one: deplane clickhouse list and the dashboard say "Upgrade to use ClickHouse", and a create is refused with limit_memory.

In our tests a large ClickHouse database used about 600 MB at rest and at most about 680 MB while it took in 50,000 events and served an analytics dashboard. A query that groups or sorts more than about 560 MB of data spills to the disk instead of failing; a query stops after 5 minutes.

Connect

Attaching sets CLICKHOUSE_URL on the app and rolls out a new release with it. It is a URL for ClickHouse's HTTP interface:

http://app:PASSWORD@ch--NAME:8123/app

The parts:

Deplane shows the password nowhere else. Traffic to the database never leaves Deplane's private network, so it is plain HTTP, without TLS. If your app wants the parts as separate settings, read them from CLICKHOUSE_URL when it starts. A template can set them for you with ${clickhouse.url}, ${clickhouse.host}, ${clickhouse.http_port}, ${clickhouse.native_port}, ${clickhouse.user}, ${clickhouse.password} and ${clickhouse.database}.

A template that needs a ClickHouse database creates and attaches one for you, named after the app (APP-clickhouse, the app name cut to 19 characters). Deleting the template deploy ("Delete all of it") destroys that ClickHouse database with its data, unless another app uses it by then.

A client on your own computer cannot connect, because the database has no public address. Run SQL through one of your apps with deplane run instead.

Client notes

The official clients speak HTTP and take the URL as it is, for example createClient({ url: process.env.CLICKHOUSE_URL }) with @clickhouse/client for Node, or clickhouse_connect.get_client(dsn=os.environ["CLICKHOUSE_URL"]) in Python. A client that ignores the database in the path still lands in app, the user's default database.

clickhouse-client uses the native port, 9000. Give it the password in a config file, not on the command line, where other programs can read it. With the client in your app's image:

$ deplane run --app web -- sh -c 'p=${CLICKHOUSE_URL#http://app:}; printf "<config><user>app</user><password>%s</password></config>" "${p%%@*}" > /tmp/ch.xml; clickhouse-client --config-file /tmp/ch.xml --host ch--events --database app --query "SHOW TABLES"'

deplane run shows only the first 10 MB of output, so use it for queries and small exports, not for copying a large database.

The fixed password

The password is fixed and cannot be changed from SQL. Users are set by Deplane, not in the database, so ALTER USER, CREATE USER and password changes are refused. Deplane keeps the same password for the life of the database, because apps such as Plausible save it inside their data (in ClickHouse dictionaries) and a new one would break them.

The one exception is a restore from another ClickHouse database's backup. The data then holds that one's password, so this one takes the other one's password too, and its attached apps restart with the new CLICKHOUSE_URL. If that backup was taken before its password last changed (after an earlier restore from yet another database), the restore still runs, but the app's dictionaries may fail to sign in until a newer backup is restored. The same goes for a backup taken after such a restore stopped before it replaced the data: the data still holds the old password. While an attached app is suspended, such a restore is refused; detach the app first.

What app may and may not do

The app user may create, change and drop tables, views, materialized views and dictionaries in the app database, create and drop SQL functions, read the system tables (for example, to see how much space the data takes), and run the SYSTEM commands that act on its own tables, such as SYSTEM STOP MERGES app.events.

It may not:

• create or change users, roles or passwords;
• create other databases;
• read or write outside the database: url(), s3(), remote(), file() and postgresql() are refused, and so are tables that read from a URL. A dictionary may name an HTTP source, but the database reaches nothing outside your team's own apps, so it cannot load from the internet;
• run SYSTEM commands that act on the whole server, such as SYSTEM SHUTDOWN or SYSTEM RELOAD CONFIG.

There is no default user. Backups and restores use separate accounts of Deplane's, which you cannot sign in as.

Backups and restore

Every ClickHouse database is backed up every night, at a time of its own between 01:00 and 05:00 UTC. A backup is a full copy of the app database and your SQL functions, taken while the database keeps running, compressed, encrypted, with an off-site copy kept 7 days.

Back it up now with deplane clickhouse backup NAME (once an hour, or 5 minutes after a backup failed) and list backups with deplane clickhouse backups NAME. A backup is about as large as the data, so at most 2 are kept: the newest that worked, plus the one before it while it is younger than 3 days (or your plan's backup window, if shorter). Back up now counts toward the 2.

deplane clickhouse restore NAME BACKUP-ID replaces all the data of the ClickHouse database NAME with a backup. It asks for your passkey. The backup can be from any ClickHouse database of your team: its own, another one, or one you destroyed (list those with deplane clickhouse backups --destroyed). The database stops while the restore runs, so its apps cannot connect, and it starts again by itself; data written since the backup is lost. A backup of another ClickHouse database brings that one's password with it (see The fixed password).

How long it takes: in our tests, about 1 GB of data backed up in about 1.5 minutes, and restored in about 70 seconds. At 4.5 GB, each takes about 7 minutes.

The backup is downloaded before the old data is removed. If the download fails, the data is unchanged and the restore's message says so. If a restore fails after it started replacing the data, the database may start empty: restore again or email support@deplane.cloud. If a restore from another ClickHouse database fails before it loaded the data, the data is unchanged but the database already uses the other one's password: restore again.

The 45% rule

Keep data under about 4.5 GB (about 45% of the 10 GB disk). A backup first copies the data files on the same disk, so it needs free space as large as the data. Above that line, a backup is refused with:

The ClickHouse disk is more than about half full, so there is no room for a backup.

The backup shows as failed with that message in deplane clickhouse backups NAME and in the dashboard, and Deplane is alerted. Right after a large write or delete, ClickHouse keeps the old copies of the changed data for a few minutes, so the disk can be fuller than the data for a while. A restore of a backup that holds more than about 4.5 GB is refused with restore_no_room before anything is stopped.

Disk use is shown as of the last backup: deplane clickhouse list, the end of deplane clickhouse backups NAME and the dashboard show the size of the data the last backup found. Before the first backup, it shows nothing yet. To see the size now:

SELECT formatReadableSize(sum(bytes_on_disk)) FROM system.parts WHERE active AND database = 'app';

ClickHouse stores data compressed, so 4.5 GB on disk holds far more events than 4.5 GB of raw data. To make room, drop old partitions or set a TTL on your tables.

How much you can lose

A ClickHouse database is backed up once a night. If its data is lost, you can lose up to about a day of data: there is no point-in-time restore. Back it up now before a risky change, such as a migration or a large delete.

Availability

A ClickHouse database is a single instance. If the server holding its disk goes down, the database is down until that server returns; its apps cannot reach it meanwhile. If that server is lost, restoring a backup is the way out: create a new ClickHouse database, restore the old one's latest backup into it, and attach your apps. This is the same promise as MySQL, Redis and app disks. It is below Postgres databases, which keep a live copy on another server.

Limits

• Size: large (2 GB) only, counted as one instance of its size in your plan's instances and memory. See invalid_clickhouse_size, limit_size, limit_instances and limit_memory.
• Storage: 10 GB, with data kept under about 4.5 GB so that backups have room.
• ClickHouse databases per team: your plan's (Beta: 1, but Beta's memory cannot hold one). See limit_clickhouse.
• ClickHouse databases per app: 1. See clickhouse_attached_elsewhere.
• One database and one user (app). No replicas, clusters, other users or the MySQL and PostgreSQL protocol ports.
• A ClickHouse database uses one of the platform's database disks, which it shares with Postgres and MySQL. If none is free, creating one is refused with database_capacity. If the servers are nearly full, it is refused with capacity_full.
• An unknown name, or another team's ClickHouse database, gets clickhouse_not_found.
• An AI tool can list, create, attach, detach and back up a ClickHouse database. Destroying or restoring one, listing a destroyed one's backups, and setting CLICKHOUSE_URL by hand (mcp_clickhouse_url) are for people only.

See also Limits.

Destroy

deplane clickhouse destroy NAME removes CLICKHOUSE_URL from its apps and deletes the database and its data. It asks for your passkey. Its backups are kept for the backup window (3 days at most), then deleted. Until then you can restore them into another ClickHouse database: deplane clickhouse backups --destroyed lists them.

ClickHouse® is a registered trademark of ClickHouse, Inc. https://clickhouse.com. Deplane is not affiliated with, sponsored or approved by ClickHouse, Inc.

This page is also available as Markdown. AI agents can start at /llms.txt.