# 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](#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.

```sh
$ 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](databases.md), a [Redis](redis.md) and a [MySQL](mysql.md). Pull request previews do not get the ClickHouse database.

In the dashboard, open **Databases** and find **ClickHouse**.

## Sizes

| Size | Memory | CPU | Memory per query | Queries at once | Storage |
|---|---|---|---|---|---|
| large (the only size) | 2 GB | 2 vCPU | about 840 MB | 32 | 10 GB |

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](errors.md#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:

| Part | Value |
|---|---|
| Host | `ch--NAME`, for example `ch--events` |
| HTTP port | 8123 (in `CLICKHOUSE_URL`) |
| Native port | 9000 (for `clickhouse-client` and native drivers) |
| User | `app` |
| Password | 32 random letters and digits, only inside `CLICKHOUSE_URL` |
| Database | `app` (also the user's default database) |

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](templates.md) 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`](cli.md#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:

```sh
$ 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](#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](errors.md#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:

```sql
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](mysql.md), [Redis](redis.md) and [app disks](disks.md). It is below Postgres [databases](databases.md#live-copy-and-failover), 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](errors.md#invalid_clickhouse_size), [limit_size](errors.md#limit_size), [limit_instances](errors.md#limit_instances) and [limit_memory](errors.md#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](errors.md#limit_clickhouse).
- ClickHouse databases per app: 1. See [clickhouse_attached_elsewhere](errors.md#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](errors.md#database_capacity). If the servers are nearly full, it is refused with [capacity_full](errors.md#capacity_full).
- An unknown name, or another team's ClickHouse database, gets [clickhouse_not_found](errors.md#clickhouse_not_found).
- An [AI tool](mcp.md) 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](errors.md#mcp_clickhouse_url)) are for people only.

See also [Limits](limits.md#clickhouse).

## 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.