This guide covers the complete ClickHouse setup for web application analytics. When your web application generates over 100 million events, PostgreSQL starts returning results in minutes. We've seen this many times: a client requests a DAU report over 90 days broken down by sources, and the query hangs for 40 seconds. ClickHouse is a powerful OLAP database designed for real-time analytics. It processes billions of rows in seconds. Our experience implementing ClickHouse in 10+ projects over 5 years shows a consistent 10–100x speedup. For top-tier analytical queries like DAU, ClickHouse is 100 times faster than PostgreSQL. ClickHouse doesn't replace transactional databases—it complements them: PostgreSQL for operational data, ClickHouse for analytics. For a typical mid-size project, switching to ClickHouse can save $30,000–$50,000 annually on infrastructure costs. We guarantee: after tuning, you'll forget about timeouts.
ClickHouse saves 5–10x on server costs; for example, one client saved $50,000 per year.
This guide covers ClickHouse setup, web application analytics, ClickHouse table schema design, the MergeTree engine, analytical queries in ClickHouse, materialized views, Laravel ClickHouse integration, Node.js ClickHouse client, OLAP database capabilities, query acceleration, data aggregation, and ClickHouse replication.
What Makes ClickHouse Ideal for Web Application Analytics?
ClickHouse uses columnar storage: each column's data lies separately—the query reads only the needed columns. Vectorized processing allows the CPU to operate on batches of values rather than individual rows. Similar data compresses 5–10 times more efficiently than in PostgreSQL. ClickHouse offers specialized table engines like MergeTree that optimize storage and queries for analytical scenarios.
MergeTree: Structure and Optimization
MergeTree is the primary ClickHouse engine. It sorts data by the ORDER BY key and splits into granules of 8192 rows. On query, ClickHouse prunes entire granules based on the primary index and additional indexes like bloom_filter. This provides block-level pruning, significantly reducing the amount of scanned data.
How to Design an Efficient Table Schema for ClickHouse?
CREATE TABLE events (
event_date Date,
event_time DateTime,
event_type LowCardinality(String),
user_id UInt64,
session_id String,
tenant_id UInt32,
page_url String,
referrer String,
country LowCardinality(String),
device_type LowCardinality(String),
properties String
) ENGINE = MergeTree()
ORDER BY (tenant_id, event_date, event_type, user_id)
PARTITION BY toYYYYMM(event_date);
ALTER TABLE events ADD INDEX idx_session session_id TYPE bloom_filter(0.01) GRANULARITY 4;
ORDER BY is the sort key by which ClickHouse stores data. Queries with filters on tenant_id and event_date use it for pruning. LowCardinality is an optimization for columns with few unique values (~10k)—stored as dictionary encoding.
Efficient Data Insertion in ClickHouse
ClickHouse is optimized for batch inserts. Use buffering as in the example below.
import { createClient } from '@clickhouse/client';
const client = createClient({
host: process.env.CLICKHOUSE_HOST,
username: process.env.CLICKHOUSE_USER,
password: process.env.CLICKHOUSE_PASSWORD,
database: 'analytics',
});
class EventBuffer {
private buffer: EventRow[] = [];
private flushTimer: NodeJS.Timeout;
async push(event: EventRow) {
this.buffer.push(event);
if (this.buffer.length >= 1000) await this.flush();
}
async flush() {
if (!this.buffer.length) return;
const rows = [...this.buffer];
this.buffer = [];
await client.insert({
table: 'events',
values: rows,
format: 'JSONEachRow',
});
}
}
Never insert row by row—for high loads, use Kafka Engine.
Speeding Up Queries
Here are example analytical queries that execute in seconds on 100M rows.
-- DAU over 90 days
SELECT event_date, uniqExact(user_id) AS dau
FROM events
WHERE tenant_id = 42 AND event_date >= today() - 90 AND event_type = 'pageview'
GROUP BY event_date
ORDER BY event_date;
-- Conversion funnel
SELECT
countIf(event_type = 'product_view') AS views,
countIf(event_type = 'add_to_cart') AS cart,
countIf(event_type = 'checkout_start') AS checkout,
countIf(event_type = 'purchase') AS purchases,
round(100.0 * purchases / views, 2) AS conversion_pct
FROM events
WHERE tenant_id = 42 AND event_date BETWEEN '2024-01-01' AND '2024-01-31';
uniqExact gives exact unique count. uniq is approximate (~2% error) but an order of magnitude faster.
Performance Comparison: PostgreSQL vs ClickHouse
ClickHouse vs PostgreSQL Performance
| Query | PostgreSQL (100M rows) | ClickHouse (100M rows) | Speedup |
|---|---|---|---|
| DAU over 90 days | 42 sec | 0.4 sec | ~100x |
| Conversion funnel over a quarter | 18 sec | 0.2 sec | ~90x |
| Cohort analysis | 35 sec | 0.6 sec | ~58x |
Materialized Views: Automatic Pre-aggregation
CREATE MATERIALIZED VIEW daily_metrics
ENGINE = SummingMergeTree()
ORDER BY (tenant_id, event_date, country, device_type)
AS SELECT
tenant_id,
event_date,
country,
device_type,
count() AS events_count,
uniqState(user_id) AS unique_users_state,
uniqState(session_id) AS unique_sessions_state
FROM events
GROUP BY tenant_id, event_date, country, device_type;
SELECT
event_date,
sum(events_count) AS total_events,
uniqMerge(unique_users_state) AS unique_users
FROM daily_metrics
WHERE tenant_id = 42 AND event_date >= today() - 7
GROUP BY event_date;
Materialized views automatically update on insert and store pre-aggregated metrics. This accelerates typical reports by tens of times.
Integration and Operation
Integration with Laravel and Node.js
For Laravel ClickHouse integration, use the package sanchov/laravel-clickhouse. Add a clickhouse connection in config/database.php and query like: DB::connection('clickhouse')->select(..., [$tenantId]). The official Node.js ClickHouse client is @clickhouse/client, which we used in the example above.
Replication and TTL
CREATE TABLE events ON CLUSTER analytics_cluster (
...
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
ORDER BY (tenant_id, event_date, event_type, user_id)
PARTITION BY toYYYYMM(event_date)
TTL event_date + INTERVAL 2 YEAR DELETE;
TTL automatically deletes data older than two years. For compliance, you can configure moving to cold storage.
Common Mistakes and Our Process
Common Mistakes
The most frequent mistake is trying to insert data row by row. ClickHouse is not designed for transactional inserts. The second is choosing the wrong ORDER BY sort key. If you filter by user_id and event_date, the ORDER BY order should match the filter frequency. The third is forgetting Materialized Views for typical reports, leading to full table scans.
What's Included in Our Work
- Analysis of current data model and metrics
- Designing events schema + Materialized Views
- Configuring replication and TTL
- Integration code for Laravel or Node.js
- Documentation of schema and queries
- Team training (1–2 hours)
- 1 month support after implementation
Stages and Timelines
| Stage | Timeline | Cost |
|---|---|---|
| Schema events + Materialized Views + Laravel integration | 1–2 weeks | individually |
| Cohort analysis, retention, replication, Kafka Engine | 2–4 weeks | individually |
Order a free audit of your current analytics schema—our engineer with 10 years of experience will analyze bottlenecks. To discuss details, contact us via the website form. Project payback is 3–6 months due to infrastructure cost savings.