# ClickHouse analytical column store

Source: https://docs.quake.ai/resources/iac-templates/clickhouse
Markdown: https://docs.quake.ai/resources/iac-templates/clickhouse.md

---

# ClickHouse analytical column store

This [validated OpenTofu template](/docs/platform/validation#how-infrastructure-templates-are-checked) composes Compute, Network, and Block Storage into a self-hosted analytical column store you run on infrastructure you control.

## What this template does

Provisions a single instance running [ClickHouse](https://clickhouse.com), a fast analytical column store (a self-hosted alternative to the query layer of BigQuery or Snowflake):

- Compute instance that runs ClickHouse in Docker, sized for moderate analytical workloads (2 vCPU and 8 GiB RAM)
- Private network, subnet, router, port, and security group; a floating IP for controlled access
- A block volume mounted at `/var/lib/docker`, so ClickHouse table data lives on a volume you can grow rather than on the boot disk
- cloud-init installs Docker Engine and starts ClickHouse from a compose file on first boot

ClickHouse stores and queries large analytical datasets on a VM you own. Pair it with [self-managed PostgreSQL](/resources/iac-templates/self-managed-postgres) for transactional workloads, or use it as the warehouse layer in a lakehouse stack.

No credential ships with this template. cloud-init generates a database password on first boot and writes it to `/opt/clickhouse/.bootstrap-user` on the instance.

## Parameters

| Parameter | Description | Default |
| --- | --- | --- |
| `key_name` | SSH keypair name (must already exist) | No default |
| `flavor_name` | Instance size (ClickHouse on 2 vCPU / 8 GiB) | `m2a.large` |
| `image_name` | Operating system image | `Ubuntu-24.04` |
| `app_name` | Display name prefix for resources | `clickhouse` |
| `volume_size` | Block volume size in GiB, mounted at `/var/lib/docker` | `50` |
| `external_network` | External network for floating IP allocation | `PublicStatic` |
| `private_cidr` | CIDR for the private subnet | `10.49.0.0/24` |
| `client_allowed_cidr` | CIDR allowed to reach ClickHouse on ports 8123 and 9000 | `10.49.0.0/24` |

## Client access and security

ClickHouse listens on port 8123 (HTTP) and 9000 (native TCP). The security group restricts both ports to `client_allowed_cidr`, which defaults to the private network only, so the raw database stays off the public internet. Reach ClickHouse one of three ways:

- Put a reverse proxy (Caddy or Nginx) in front of the HTTP interface and serve queries over HTTPS on 443. Point the domain's DNS A record at the floating IP. This is the recommended path for routine access.
- Tunnel over SSH: `ssh -L 8123:localhost:8123 -L 9000:localhost:9000 user@FLOATING_IP`, then connect to `localhost`.
- Set `client_allowed_cidr` to `YOUR_IP/32` to reach ports 8123 and 9000 directly from one address.

Ports 80 and 443 stay open for the reverse proxy you put in front; they carry no traffic until you add one.


Snapshot the data volume before you resize or rebuild the host. ClickHouse table data lives on the attached volume; a destroyed volume means unrecoverable datasets unless you have a backup.


## When to use this pattern

Run an analytical column store for aggregations, time-series rollups, and large scan queries on a VM you operate. ClickHouse suits event analytics, log pipelines, and BI backends that outgrow Postgres-as-warehouse. For BI dashboards on top of the warehouse, see Metabase or Apache Superset in the data tooling catalog.

## Estimated cost

<PricingCompanion
  components={[
    { kind: "template", slug: "clickhouse", required: true },
  ]}
/>

## Template source

<TemplateSource slug="clickhouse" />

<TemplateResourceMap template="clickhouse" format="opentofu" />

## Customize this pattern

- [Customize a template's image and flavor](/docs/automation/how-to/customize-template-image-flavor)
- [Add a block volume to a template](/docs/automation/how-to/add-volume-to-template)
- [Parameterize a template with a tfvars file](/docs/automation/how-to/parameterize-template-tfvars)

## See also

- [Deploy ClickHouse](/resources/deployments/deploy-clickhouse-template)
- [self-managed PostgreSQL](/resources/iac-templates/self-managed-postgres)