When Your PostgreSQL Aggregate Queries Hit a Wall — A Practical Guide to ClickHouse + Node.js

You remember that query. The moment the dashboard API response exceeds 30 seconds and timeout errors start piling up in Sentry. You run EXPLAIN on SELECT COUNT(DISTINCT user_id) FROM events WHERE created_at >= ... and it's already using the best possible index — but once the table crosses 100 million rows, it just can't keep up.

This post is about solving that problem with ClickHouse. It covers why columnar storage is better for aggregations, how to connect with the official Node.js client, production patterns like batch INSERT, schema design, and Kafka integration, and when to keep PostgreSQL.

Why Columnar Storage Is Better for Analytical Queries

In a row-based DB like PostgreSQL, a single row (id, user_id, event_type, properties, created_at) is stored in a contiguous area on disk. When you run SELECT COUNT(*) GROUP BY event_type, even though you only need the event_type column, every page containing that column must be read from disk.

ClickHouse is the opposite. The user_id column is stored in its own user_id file, and the event_type column in its own event_type file. Since only the columns needed for aggregation are read, I/O is fundamentally different.

Three additional mechanisms compound this advantage.

Block-level vectorized execution: ClickHouse doesn't process rows one at a time — it batches them into blocks of 65,536 rows by default (max_block_size default). This dramatically reduces per-row interpreter overhead.

SIMD utilization: A single CPU instruction processes multiple values in parallel. This requires values of the same type to be laid out contiguously in memory — a condition that columnar storage naturally satisfies.

High compression ratio: Storing values of the same type consecutively yields excellent compression efficiency. Monotonically increasing values like timestamps benefit especially from DoubleDelta encoding, which achieves particularly high compression ratios. Higher compression means less data to read from disk, which translates directly into faster queries.

MergeTree: The Default Engine for Event Logs

ClickHouse's core storage engine is MergeTree. It is optimized for append-only data like event logs. Specifying a sort key with ORDER BY physically sorts and stores the data, making range scans fast.

The index structure is also different. PostgreSQL's B-tree index has an entry for every row, but ClickHouse's sparse index has one index entry per 8,192 rows. Because the index itself is small, it always fits in memory — making it actually more efficient for large-scale scans.

It's worth knowing the derived engines as well.

Separating Roles Between PostgreSQL and ClickHouse

Adding ClickHouse doesn't mean dropping PostgreSQL. You need to divide responsibilities.

Single-record point lookups, transactions, and foreign key constraints are things PostgreSQL does far better. ClickHouse takes on the role of aggregating event logs generated from that data.

There are two paths to send events to ClickHouse: the app publishes directly to Kafka, or a CDC tool like Debezium captures PostgreSQL changes and streams them to Kafka. Many teams use both paths simultaneously. This dual-DB architecture is the production standard.

Installation and Basic Connection

You can spin up a local environment with Docker first.

docker run -d \
  --name clickhouse-server \
  -p 8123:8123 \
  -p 9000:9000 \
  clickhouse/clickhouse-server:latest

Install the official client in your Node.js project.

npm install @clickhouse/client

The basic connection looks like this.

import { createClient } from '@clickhouse/client';
 
const client = createClient({
  url: 'http://localhost:8123',
  username: 'default',
  password: '',
  database: 'default',
});

Event Log Table Schema Design

Schema design determines half of ClickHouse performance. The choice of ORDER BY key order is especially critical — a common mistake is to simply remember "put low-cardinality columns first." The correct principle is to put columns frequently used as WHERE filters first, with lower cardinality ones earlier among those. Placing a column that isn't used as a filter at the front provides no sparse index benefit, regardless of its cardinality.

CREATE TABLE events
(
    event_id     UUID           DEFAULT generateUUIDv4(),
    service_name LowCardinality(String),
    event_type   LowCardinality(String),
    user_id      String,
    session_id   String,
    properties   Map(String, String),
    created_at   DateTime64(3)  CODEC(DoubleDelta, ZSTD(1))
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (service_name, event_type, created_at)
TTL created_at + INTERVAL 90 DAY
SETTINGS index_granularity = 8192;

LowCardinality(String): Applying this to repeated string columns enables dictionary encoding internally, dramatically reducing memory and storage usage.

CODEC(DoubleDelta, ZSTD(1)): Applying DoubleDelta to monotonically increasing timestamps significantly improves compression ratio.

ORDER BY (service_name, event_type, created_at): If your primary query pattern is per-service and per-event-type, this order is correct. Putting the timestamp first like ORDER BY (created_at, service_name) turns WHERE service_name = ? conditions into full scans.

TTL: Automatically deletes data older than 90 days. If your logs have a retention period, setting this from the start makes operations much easier.

Batch INSERT — Never Do Single-Row INSERTs

Looping single-row INSERTs will be slower than PostgreSQL. ClickHouse writes a new part to disk for every INSERT, so repeated single-row INSERTs cause parts to accumulate rapidly. With ClickHouse's default settings, when the number of concurrent parts per partition exceeds 150, INSERT throughput is automatically throttled, and when it exceeds 300, INSERT itself is rejected with a Too many parts error. It doesn't slow down — it stops completely.

Here's a pattern for batch processing with an in-memory buffer in Node.js.

import { createClient } from '@clickhouse/client';
 
interface EventRow {
  service_name: string;
  event_type: string;
  user_id: string;
  session_id: string;
  properties: Record<string, string>;
  created_at: string; // ISO 8601
}
 
class EventBuffer {
  private buffer: EventRow[] = [];
  private flushPromise: Promise<void> = Promise.resolve();
  private readonly flushSize = 5000;
  private readonly flushIntervalMs = 3000;
  private timer: ReturnType<typeof setInterval>;
 
  constructor(private readonly client: ReturnType<typeof createClient>) {
    this.timer = setInterval(() => this.scheduleFlush(), this.flushIntervalMs);
  }
 
  push(event: EventRow): void {
    this.buffer.push(event);
    if (this.buffer.length >= this.flushSize) {
      this.scheduleFlush();
    }
  }
 
  // Serialized via Promise chain — flush runs sequentially even when called concurrently from both the timer and push
  private scheduleFlush(): void {
    this.flushPromise = this.flushPromise.then(() => this.flush());
  }
 
  private async flush(): Promise<void> {
    if (this.buffer.length === 0) return;
 
    const rows = this.buffer.splice(0);
    try {
      await this.client.insert({
        table: 'events',
        values: rows,
        format: 'JSONEachRow',
      });
    } catch (err) {
      console.error('[EventBuffer] INSERT failed, returning data to buffer for retry:', err);
      this.buffer = [...rows, ...this.buffer]; // Return failed rows to the front of the buffer
    }
  }
 
  // Must be called before process exit to drain remaining data
  async destroy(): Promise<void> {
    clearInterval(this.timer);
    await this.flushPromise;
    await this.flush();
  }
}
 
const buffer = new EventBuffer(client);
 
process.on('SIGTERM', async () => {
  await buffer.destroy();
  process.exit(0);
});

It flushes automatically every 3 seconds or when 5,000 events accumulate. The flushPromise chain serializes flush execution, and on INSERT failure, the extracted rows are returned to the front of the buffer to guarantee a retry opportunity. destroy() handles graceful shutdown as well.

Aggregation Queries — Prevent Injection with Parameter Binding

Parameter binding uses the {param:Type} syntax. It prevents SQL injection while also improving query cache efficiency.

async function getDailyActiveUsers(
  serviceName: string,
  startDate: string,
  endDate: string,
): Promise<{ date: string; dau: number }[]> {
  const result = await client.query({
    query: `
      SELECT
        toDate(created_at) AS date,
        uniq(user_id)      AS dau
      FROM events
      WHERE service_name = {service:String}
        AND created_at BETWEEN {start:DateTime64} AND {end:DateTime64}
      GROUP BY date
      ORDER BY date
    `,
    query_params: {
      service: serviceName,
      start: startDate,
      end: endDate,
    },
    format: 'JSONEachRow',
  });
 
  return result.json();
}

uniq() is an approximate aggregation function based on HyperLogLog. It has roughly a 2–3% error margin compared to exact COUNT(DISTINCT), but returns results within tens of milliseconds even on hundreds of millions of rows. Use uniqExact() if you need precision.

Kafka Integration — High-Throughput Pipeline

As traffic grows, an in-memory Node.js buffer alone may not be enough. Placing Kafka in the middle lets ClickHouse's Kafka engine consume directly, providing stable high throughput.

-- Kafka source table
CREATE TABLE events_kafka
(
    service_name String,
    event_type   String,
    user_id      String,
    session_id   String,
    properties   Map(String, String),
    created_at   DateTime64(3)
)
ENGINE = Kafka
SETTINGS
    kafka_broker_list      = 'kafka:9092',
    kafka_topic_list       = 'user-events',
    kafka_group_name       = 'clickhouse-consumer',
    kafka_format           = 'JSONEachRow',
    kafka_num_consumers    = 4,
    kafka_max_block_size   = 65536;
 
-- Insert into the actual table via Materialized View
CREATE MATERIALIZED VIEW events_kafka_mv TO events AS
SELECT * FROM events_kafka;

kafka_max_block_size = 65536 is ClickHouse's default value and a good starting point. The right value depends on message size, number of consumers, and available memory, so consult the official documentation to tune it for your workload.

Pre-Aggregation with Materialized Views

You can pre-compute frequently used aggregations to avoid scanning the full table every time.

-- Pre-aggregation table for hourly event counts
CREATE TABLE events_hourly_agg
(
    service_name LowCardinality(String),
    event_type   LowCardinality(String),
    hour         DateTime,
    event_count  AggregateFunction(count),
    unique_users AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree()
ORDER BY (service_name, event_type, hour);
 
CREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly_agg AS
SELECT
    service_name,
    event_type,
    toStartOfHour(created_at) AS hour,
    countState()              AS event_count,
    uniqState(user_id)        AS unique_users
FROM events
GROUP BY service_name, event_type, hour;

At query time, use Merge functions to combine aggregation states into final values.

SELECT
    hour,
    countMerge(event_count)  AS event_count,
    uniqMerge(unique_users)  AS unique_users
FROM events_hourly_agg
WHERE service_name = 'my-app'
  AND hour >= now() - INTERVAL 7 DAY
GROUP BY hour
ORDER BY hour;

There is an important caveat. Materialized Views are only updated automatically when new data is INSERTed. Data that already existed before the MV was created is not reflected in the aggregation table. If you add a MV to a running system, you must separately backfill existing data using INSERT INTO events_hourly_agg SELECT .... Miss this, and historical metrics will show as 0 on your dashboard.

Pros and Cons Analysis

Where ClickHouse Shines

Cloudflare is known to use ClickHouse to return trillions of events in a single query within seconds. Netflix has also published case studies on adopting ClickHouse for large-scale log processing.

Points to Watch Out For

Three Common Production Mistakes

1. Single-row INSERT loops: Batch processing is mandatory. ClickHouse throttles writes when the number of parts per partition exceeds 150, and rejects INSERT entirely with a Too many parts error above 300. It doesn't "slow down" — it "stops."

2. Wrong ORDER BY key order: Putting the timestamp first like ORDER BY (created_at, service_name) turns per-service filtering queries into full scans. The correct principle is "among columns frequently used as WHERE filters, put the lower-cardinality ones first."

3. Trying to replace PostgreSQL with ClickHouse: The standard architecture is to keep PostgreSQL for transactions, single-record CRUD, and foreign key constraints, while offloading analytics, aggregations, and logs to ClickHouse. Trying to do everything with one system leaves you with the weaknesses of both.

Closing Thoughts

ClickHouse is one of the most proven options for solving analytical query performance problems. In 2025–2026 it is rapidly becoming the observability layer of choice for LLM services — tracking token usage and latency — so you'll likely encounter it more frequently going forward.

Here's the core summary. Columnar storage + block-level vectorized execution + sparse indexes make aggregation queries fast; batch INSERT and ORDER BY key design determine operational performance; and a dual-DB architecture that divides responsibilities with PostgreSQL is the production standard.

Here are three steps you can take right now.

1. Spin up ClickHouse locally with Docker and connect via @clickhouse/client, then port your slowest PostgreSQL aggregation query as-is and compare the performance.
2. Design a fresh event log table and experiment directly with LowCardinality, the DoubleDelta codec, and ORDER BY key order.
3. Attach a batch INSERT buffer and add pre-aggregation with a Materialized View, then measure how response times change.

This isn't about dropping PostgreSQL. It's about handing off the things it's not good at to ClickHouse.

References

• ClickHouse Official Docs — Introduction
• ClickHouse Official JS Client Docs
• ClickHouse JS GitHub
• @clickhouse/client npm package
• ClickHouse Node.js Integration Guide
• ClickHouse vs PostgreSQL Official Comparison
• PostgreSQL Migration Guide
• Kafka Integration Official Docs
• Observability Schema Design Guide
• ClickHouse Best Practices
• Cloudflare ClickHouse Case Study
• MergeTree Engine Deep Dive
• ClickHouse JOIN Limitations and Solutions
• When Not to Use ClickHouse
• Kafka → ClickHouse Pipeline Comparison
• 2026 Real-Time Analytics DB Selection Guide