The problem
Scouq's email engine tracks every send, open, click, reply, and bounce. At modest scale — 35 callers, 16K leads, multiple active campaigns — that's still hundreds of thousands of events. Querying that data for campaign health dashboards needs to be fast: under 200ms for most aggregate queries.
We evaluated two options seriously: ClickHouse and TimescaleDB (Postgres extension).
Why not just use Postgres?
Plain Postgres works fine up to a point. But aggregate queries across millions of rows with GROUP BY on timestamp columns get slow without significant index tuning. Once you're running multiple simultaneous queries from a dashboard, you start seeing lock contention. We wanted something designed for this from the start.
ClickHouse
ClickHouse is a columnar database built for analytical queries. It stores data column-by-column rather than row-by-row, which makes GROUP BY, SUM, and COUNT queries dramatically faster on large datasets.
Key advantages for our use case:
- Compression — email event data compresses well in columnar format. Our 16K leads' worth of events fits in a fraction of the space we'd expect from Postgres.
- Query speed — aggregating a full campaign's opens and clicks by day takes under 50ms even without pre-computed materialized views.
- HTTP interface — ClickHouse ships with an HTTP interface on port 8123. This lets us query from Vercel serverless functions without a persistent connection pool. One less thing to manage.
Tradeoffs:
- Not a drop-in Postgres replacement. No foreign keys. No transactions in the traditional sense.
- Requires some care around data types — ClickHouse's DateTime handling is different from Postgres.
- Schema changes are more work than running
ALTER TABLEin Postgres.
TimescaleDB
TimescaleDB extends Postgres with time-series optimizations — hypertables, continuous aggregates, and compression policies. If your team already runs Postgres, it's a lower lift: same query syntax, same tooling, same connection pooling.
For us, the issue was that we already had ClickHouse running for another purpose (lead deduplication across 1B+ rows on a separate service). Adding TimescaleDB would mean managing a second Postgres instance alongside our existing one.
What we chose
ClickHouse. The query performance was meaningfully better for our specific access patterns, and the HTTP interface eliminated the serverless connection problem entirely. The operational overhead of running it on a VPS (CPX31) has been low.
If you're starting from scratch with Postgres already in your stack and you don't have 100M+ events, TimescaleDB is a reasonable choice. The continuous aggregates feature is genuinely well-designed.
The numbers
On our dataset, the same campaign-level aggregate query (total sends, opens, clicks, replies by day for a 90-day window):
- Postgres (plain): ~800ms
- TimescaleDB with continuous aggregates: ~120ms
- ClickHouse: ~35ms
At dashboard scale with multiple concurrent users, those differences compound.