Deploy a ClickHouse Server on PandaStack for Fast Analytics (2026)

Postgres is a wonderful transactional database and a mediocre analytics engine. The moment you write SELECT count(*) ... GROUP BY day over 200 million events and watch it think for 40 seconds, you've discovered why column stores exist. ClickHouse (https://clickhouse.com) is the open-source column store that eats that query for breakfast — it's built for INSERT-heavy, UPDATE-rare, aggregate-everything analytical workloads.

This guide runs a single-node ClickHouse server on PandaStack as a container app with persistent storage, sets a real password, and connects an app to it. Single-node handles a genuinely large amount of data before you ever need a cluster; don't over-build.

When ClickHouse is the right tool

Use ClickHouse for: product analytics, event tracking, logs, metrics, time-series, and any dashboard that aggregates huge tables. Do not use it as your primary application database — it's not built for row-level updates, foreign keys, or transactional integrity. The standard pattern is Postgres for app state, ClickHouse for analytics, and a pipe between them.

PandaStack's managed databases are PostgreSQL, MySQL, Redis, and MongoDB, so ClickHouse runs as a container app with a volume, same as any self-hosted store.

Step 1: Dockerfile with a mounted data path

The official image is clickhouse/clickhouse-server. ClickHouse stores data under /var/lib/clickhouse, which is exactly what we'll put on a persistent volume.

FROM clickhouse/clickhouse-server:24-alpine

# Config + users are provided via mounted files / env at runtime.
# Data and logs live on the persistent volume (see step 3).
EXPOSE 8123 9000

Ports: 8123 is the HTTP interface (easy to curl, used by most language clients), 9000 is the native TCP protocol (faster, used by the CLI and high-throughput drivers). Expose 8123 publicly through PandaStack; keep 9000 for internal/CLI use.

Step 2: Set a password — do not ship the default

Out of the box the default user has no password. A public ClickHouse with an empty password is an open invitation to have your compute mined for cryptocurrency. Provide a users config. Create users.d/custom.xml:

<clickhouse>
  <users>
    <default>
      <password_sha256_hex>REPLACE_WITH_SHA256</password_sha256_hex>
      <networks><ip>::/0</ip></networks>
      <profile>default</profile>
      <quota>default</quota>
    </default>
  </users>
</clickhouse>

Generate the hash locally:

PASSWORD=$(openssl rand -hex 16); echo "password: $PASSWORD"
echo -n "$PASSWORD" | sha256sum | awk '{print $1}'

Bake the file into the image under /etc/clickhouse-server/users.d/:

COPY users.d/custom.xml /etc/clickhouse-server/users.d/custom.xml

Keep the actual password out of Git — put the plaintext in a PandaStack env var and only commit the hash, or template the file at startup.

Step 3: Deploy with a persistent volume

1. 1Push the repo to GitHub.
2. 2Dashboard → New App → Container App, connect the repo, container port 8123.
3. 3Attach a persistent volume mounted at /var/lib/clickhouse. This is non-negotiable — it's where every row you ingest lives.
4. 4Deploy. PandaStack builds and serves it at https://your-clickhouse.pandastack.io.

Step 4: Create a table with the right engine

ClickHouse performance is 80% table-engine choice. For most analytics, MergeTree with a sensible ORDER BY is the answer:

CREATE TABLE events
(
    event_time  DateTime,
    user_id     UInt64,
    event_type  LowCardinality(String),
    url         String,
    country     LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time);

Two performance levers that matter enormously:

• LowCardinality(String) for columns with few distinct values (event types, countries, plans) — it dictionary-encodes them and shrinks storage dramatically.
• ORDER BY is your primary index. Put the columns you filter on most first. Get this wrong and scans stay slow no matter how much hardware you throw at it.

Run it over HTTP:

curl "https://your-clickhouse.pandastack.io/" \
  --user "default:$PASSWORD" \
  --data-binary @create_events.sql

Step 5: Insert data efficiently

ClickHouse hates tiny inserts. One row at a time creates a new "part" per insert and forces constant background merges. Batch your inserts — thousands to millions of rows per statement:

curl "https://your-clickhouse.pandastack.io/?query=INSERT%20INTO%20events%20FORMAT%20JSONEachRow" \
  --user "default:$PASSWORD" \
  --data-binary @events.jsonl

From Node with the official client:

import { createClient } from '@clickhouse/client'

const ch = createClient({
  url: 'https://your-clickhouse.pandastack.io',
  username: 'default',
  password: process.env.CLICKHOUSE_PASSWORD,
})

await ch.insert({
  table: 'events',
  values: batchOfRows,          // an ARRAY — batch, don't loop single inserts
  format: 'JSONEachRow',
})

If your app produces events one at a time, buffer them in Redis (PandaStack managed Redis) and flush in batches from a cronjob. Your ClickHouse will thank you.

Step 6: Query at speed

Now the payoff. A daily-active-users query over that events table:

SELECT toDate(event_time) AS day, uniq(user_id) AS dau
FROM events
WHERE event_time >= now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day;

uniq() is an approximate distinct-count that's absurdly fast; use uniqExact() only when you truly need exact numbers. On a properly ordered table this returns in milliseconds over hundreds of millions of rows.

Step 7: Feed it from Postgres

The common pattern: your app writes to managed Postgres, and a scheduled job copies new events into ClickHouse. A PandaStack cronjob on */5 * * * * that reads WHERE id > last_synced_id from Postgres and batch-inserts into ClickHouse is the whole ETL for most products. Store last_synced_id in Redis.

Step 8: TTL so it doesn't grow forever

Analytical tables grow relentlessly. Add a TTL to auto-drop old partitions:

ALTER TABLE events MODIFY TTL event_time + INTERVAL 180 DAY;

Now data older than 180 days is reclaimed automatically. Combined with monthly partitioning, drops are cheap metadata operations.

Troubleshooting

• "Too many parts" errors — you're inserting too frequently in tiny batches. Batch harder.
• Queries scan the whole table — your ORDER BY doesn't match your WHERE. Reorder the key.
• Data gone after redeploy — the /var/lib/clickhouse volume isn't attached.
• Open-password compromise — you shipped the default user without a password. Rotate immediately and add the users config.

Wrap-up

ClickHouse turns 40-second aggregate queries into 40-millisecond ones, and on PandaStack it's a container app with a volume, a real password, and a cronjob feeding it from Postgres. Keep it single-node until data volume genuinely forces a cluster. Docs: https://docs.pandastack.io. Start free at https://dashboard.pandastack.io.