Resolving PostgreSQL Analytical Aggregation Bottlenecks Up to 100x Faster with a ClickHouse + Node.js Pipeline

When you accumulate page views, button clicks, and purchase funnel data in PostgreSQL RDS, there will inevitably come a point where daily aggregation queries exceed 30 seconds. Even after adding indexes and applying partitioning, once rows surpass hundreds of millions, GROUP BY query response times can no longer be reduced through simple optimization. PostgreSQL remains excellent for OLTP, but for analytical aggregation specifically, the storage structure itself points in a different direction.

This article covers a pipeline that connects ClickHouse with a Node.js backend to collect and aggregate clickstream events in real time. We'll explore how to complete aggregations at insert time using Materialized Views, automatically manage data lifecycle with TTL, and horizontally scale billions of rows with Distributed tables — all with concrete SQL and TypeScript code.

For large-scale GROUP BY aggregations at the billions-of-rows scale, response times up to 100× faster than PostgreSQL have been reported. The exact multiplier varies depending on the number of rows being aggregated and query complexity; the typical range is 10–100×. HighLevel reduced storage by 88% and brought query response times under 200ms using this approach, while Wingify (VWO) cut costs by 80%.

This is not an argument to abandon PostgreSQL. A hybrid architecture where PostgreSQL remains the primary OLTP store and only analytical aggregations are offloaded to ClickHouse is the standard approach for mature teams. Let's look at how to wire that combination together with Node.js.

Core Concepts

Why ClickHouse Is Fast — Columnar Storage and Vectorized Execution

PostgreSQL stores data row by row. Even a query that needs only two columns — like SELECT event_type, COUNT(*) FROM events GROUP BY event_type — reads every column from disk. ClickHouse stores data column by column, so it scans only the columns required by the query.

On top of that, its vectorized execution engine leverages SIMD (Single Instruction Multiple Data) instructions to process hundreds of millions to billions of rows per second per core. LZ4/ZSTD compression is applied column by column, so similar values are grouped together and compression ratios are far better. A 5–10× reduction in storage compared to PostgreSQL is a natural consequence.

The MergeTree Family — Your Choice of Storage Engine Determines Performance

The core storage engine in ClickHouse is MergeTree. Inserted data is written to disk as "parts," and background merges take place to complete sorting and aggregation. The key is choosing the right engine from within the family based on your use case.

Materialized Views — Aggregate at Insert Time, Not Query Time

ClickHouse Materialized Views work completely differently from their PostgreSQL counterparts. PostgreSQL MVs re-aggregate all data when you run REFRESH, but ClickHouse MVs fire as a trigger the moment a row is inserted into the source table. They immediately aggregate incoming data and write it to the destination table.

Billions of raw events are compressed down to thousands of aggregated rows for storage. When you run an aggregation query, you only need to read the already-aggregated results.

One important caveat: ClickHouse MVs only fire on INSERT and do not retroactively process existing data. When you create a new MV, historical data must be backfilled separately with INSERT INTO ... SELECT .... Accounting for this upfront saves a lot of surprises later.

TTL — Declaratively Manage Data Lifecycle at the Database Level

There's no need to retain raw clickstream events forever. TTL lets you automatically delete old data or move it to lower-cost storage (HDD, S3).

-- Delete after 90 days
TTL event_time + INTERVAL 90 DAY DELETE
 
-- Tiered movement: Hot → Warm → Cold
TTL event_time + INTERVAL 7 DAY TO DISK 'warm',
    event_time + INTERVAL 30 DAY TO VOLUME 'cold'

A three-tier TTL policy of Hot (SSD) → Warm (HDD) → Cold (S3) is a production-validated standard pattern. ClickHouse Cloud offers the same policy as a fully managed feature.

Sharding and Distributed Tables — Breaking Through Single-Node Limits

When data outgrows a single node, you combine ReplicatedMergeTree local tables with Distributed tables. The Distributed table holds no data itself; it acts only as a proxy that routes reads and writes across multiple shards. ClickHouse Keeper (or ZooKeeper) coordinates synchronization between replicas.

Async Insert — Preventing Part Explosion from Small, High-Frequency Inserts

ClickHouse performance degrades when small inserts are performed at high frequency because too many parts are created. Enabling the async_insert setting lets the server internally batch and process inserts, which greatly mitigates the part explosion problem that occurs when inserting individual events one at a time from Node.js. While newer ClickHouse versions are moving toward enabling this by default, it's safer to set it explicitly regardless of version.

Practical Implementation

Scenario: E-Commerce Clickstream Pipeline

Assume a service receiving 10,000–100,000 page_view, add_to_cart, and purchase events per second. The goals are: ① store raw events ② real-time aggregation (views per page, conversion rate per funnel) ③ 90-day TTL ④ cluster scaling.

Step 1: Define the Source Table and Materialized View

Before defining the table, let's clarify the difference between PARTITION BY and ORDER BY. PARTITION BY toYYYYMM(event_time) physically separates data into monthly files (parts), reducing the cost of deleting old data. ORDER BY determines the sort order within a part and creates a sparse index. ORDER BY is what directly affects query speed. Placing low-cardinality columns (event_type) first allows sparse indexes to be used effectively in filter queries.

-- Raw events table
CREATE TABLE clickstream_events
(
    event_id    UUID           DEFAULT generateUUIDv4(),
    user_id     UInt64,
    session_id  String,
    event_type  LowCardinality(String),  -- page_view / add_to_cart / purchase
    page_path   String,
    revenue     Decimal(10, 2) DEFAULT 0,
    event_time  DateTime64(3)  DEFAULT now64()
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, page_path, event_time)
TTL event_time + INTERVAL 90 DAY DELETE;

LowCardinality(String) applies dictionary encoding to low-cardinality columns (those with only tens of distinct values, like event_type), improving both storage space and query speed.

Before creating the destination table and MV for aggregation results, one point about the AggregateFunction type: *State() functions like countState() and uniqState() return a serialized intermediate state without completing the aggregation. The reason AggregateFunction(...) types are needed instead of plain UInt64 is precisely to store these intermediate states. Later, *Merge() functions combine the intermediate states from multiple parts into a final result.

-- Destination table to receive MV aggregation results
CREATE TABLE clickstream_hourly
(
    event_hour    DateTime,
    event_type    LowCardinality(String),
    page_path     String,
    event_count   AggregateFunction(count),
    unique_users  AggregateFunction(uniq, UInt64),
    total_revenue AggregateFunction(sum, Decimal(10, 2))
)
ENGINE = AggregatingMergeTree
ORDER BY (event_hour, event_type, page_path);
 
 
-- Materialized View — aggregates on insert-time trigger
CREATE MATERIALIZED VIEW clickstream_mv
TO clickstream_hourly
AS
SELECT
    toStartOfHour(event_time)  AS event_hour,
    event_type,
    page_path,
    countState()               AS event_count,
    uniqState(user_id)         AS unique_users,
    sumState(revenue)          AS total_revenue
FROM clickstream_events
GROUP BY event_hour, event_type, page_path;

Use *Merge() suffix functions when querying aggregation results.

SELECT
    event_hour,
    event_type,
    page_path,
    countMerge(event_count)    AS views,
    uniqMerge(unique_users)    AS unique_users,
    sumMerge(total_revenue)    AS revenue
FROM clickstream_hourly
WHERE event_hour >= now() - INTERVAL 24 HOUR
GROUP BY event_hour, event_type, page_path
ORDER BY event_hour DESC;

Step 2: Configure the Sharded Cluster

The most commonly overlooked aspect of cluster configuration is where to attach the MV. Since Distributed tables hold no data directly, attaching an MV to one will not fire the trigger. MVs must be attached to the local table on each shard.

The correct pattern is to structure both the raw events and aggregation result layers as local+Distributed pairs, with MVs connecting local tables to local tables.

<!-- /etc/clickhouse-server/config.d/cluster.xml -->
<clickhouse>
  <remote_servers>
    <events_cluster>
      <shard>
        <replica><host>ch-node-1</host><port>9000</port></replica>
        <replica><host>ch-node-2</host><port>9000</port></replica>
      </shard>
      <shard>
        <replica><host>ch-node-3</host><port>9000</port></replica>
        <replica><host>ch-node-4</host><port>9000</port></replica>
      </shard>
    </events_cluster>
  </remote_servers>
  <macros>
    <cluster>events_cluster</cluster>
    <shard>01</shard>        <!-- Set differently per node -->
    <replica>ch-node-1</replica>
  </macros>
</clickhouse>

-- Raw events local table (per shard)
CREATE TABLE clickstream_events_local ON CLUSTER events_cluster
(
    event_id    UUID           DEFAULT generateUUIDv4(),
    user_id     UInt64,
    session_id  String,
    event_type  LowCardinality(String),
    page_path   String,
    revenue     Decimal(10, 2) DEFAULT 0,
    event_time  DateTime64(3)  DEFAULT now64()
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/clickstream_events',
    '{replica}'
)
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, page_path, event_time)
TTL event_time + INTERVAL 90 DAY DELETE;
 
 
-- Aggregation results local table (per shard)
CREATE TABLE clickstream_hourly_local ON CLUSTER events_cluster
(
    event_hour    DateTime,
    event_type    LowCardinality(String),
    page_path     String,
    event_count   AggregateFunction(count),
    unique_users  AggregateFunction(uniq, UInt64),
    total_revenue AggregateFunction(sum, Decimal(10, 2))
)
ENGINE = ReplicatedAggregatingMergeTree(
    '/clickhouse/tables/{shard}/clickstream_hourly',
    '{replica}'
)
ORDER BY (event_hour, event_type, page_path);
 
 
-- MV: local raw → local aggregation (fires independently on each shard)
CREATE MATERIALIZED VIEW clickstream_mv ON CLUSTER events_cluster
TO clickstream_hourly_local
AS
SELECT
    toStartOfHour(event_time)  AS event_hour,
    event_type,
    page_path,
    countState()               AS event_count,
    uniqState(user_id)         AS unique_users,
    sumState(revenue)          AS total_revenue
FROM clickstream_events_local
GROUP BY event_hour, event_type, page_path;
 
 
-- Raw events Distributed table (write entry point)
CREATE TABLE clickstream_events ON CLUSTER events_cluster
AS clickstream_events_local
ENGINE = Distributed(
    'events_cluster',
    currentDatabase(),
    'clickstream_events_local',
    cityHash64(user_id)
);
 
 
-- Aggregation results Distributed table (read entry point)
CREATE TABLE clickstream_hourly ON CLUSTER events_cluster
AS clickstream_hourly_local
ENGINE = Distributed(
    'events_cluster',
    currentDatabase(),
    'clickstream_hourly_local',
    rand()
);

When you INSERT into Distributed clickstream_events, the shard is selected based on cityHash64(user_id). Data for the same user_id always lands on the same shard, making per-user aggregation efficient. The MV fires on the local table of each shard where the data arrives, so aggregation is processed in a distributed manner. Reads through Distributed clickstream_hourly automatically merge results from all shards.

Step 3: Implement Batch Inserts in Node.js

npm install @clickhouse/client kafkajs

// clickhouse-client.ts
import { createClient, ClickHouseClient } from '@clickhouse/client';
 
export const chClient: ClickHouseClient = createClient({
  url: process.env.CLICKHOUSE_URL ?? 'http://localhost:8123',
  username: process.env.CLICKHOUSE_USER ?? 'default',
  password: process.env.CLICKHOUSE_PASSWORD ?? '',
  database: 'analytics',
  clickhouse_settings: {
    async_insert: 1,
    // 0: respond immediately once the server receives data in memory — maximizes throughput
    // batches not yet written to disk may be lost on server crash
    // set to 1 if data loss is not acceptable
    wait_for_async_insert: 0,
  },
});

// event-ingestion.ts
import { chClient } from './clickhouse-client';
 
export interface ClickstreamEvent {
  user_id: number;
  session_id: string;
  event_type: 'page_view' | 'add_to_cart' | 'purchase';
  page_path: string;
  revenue?: number;
  event_time?: string;
}
 
// Batch insert — recommended: 1,000–100,000 rows per batch
export async function insertEvents(events: ClickstreamEvent[]): Promise<void> {
  await chClient.insert({
    table: 'clickstream_events',
    values: events.map(e => ({
      ...e,
      revenue: e.revenue ?? 0,
      event_time: e.event_time ?? new Date().toISOString(),
    })),
    format: 'JSONEachRow',
  });
}
 
// Type-safe aggregation query — using named parameters
export async function getHourlyStats(pagePathPrefix: string, hours: number) {
  const result = await chClient.query({
    query: `
      SELECT
        event_hour,
        event_type,
        page_path,
        countMerge(event_count)   AS views,
        uniqMerge(unique_users)   AS unique_users,
        sumMerge(total_revenue)   AS revenue
      FROM clickstream_hourly
      WHERE event_hour >= now() - INTERVAL {hours:UInt32} HOUR
        AND page_path LIKE {path_prefix:String}
      GROUP BY event_hour, event_type, page_path
      ORDER BY event_hour DESC
    `,
    query_params: {
      hours,
      path_prefix: `${pagePathPrefix}%`,
    },
    format: 'JSONEachRow',
  });
 
  return result.json<{
    event_hour: string;
    event_type: string;
    page_path: string;
    views: string;
    unique_users: string;
    revenue: string;
  }>();
}

// kafka-consumer.ts
import { Kafka } from 'kafkajs';
import { insertEvents, ClickstreamEvent } from './event-ingestion';
 
const kafka = new Kafka({ brokers: ['kafka:9092'] });
const consumer = kafka.consumer({ groupId: 'clickhouse-ingester' });
 
const BATCH_SIZE = 5000;
const FLUSH_INTERVAL_MS = 1000;
 
let buffer: ClickstreamEvent[] = [];
 
async function flushBuffer(): Promise<void> {
  if (buffer.length === 0) return;
  const batch = buffer.splice(0, buffer.length);
  try {
    await insertEvents(batch);
  } catch (err) {
    console.error('ClickHouse insert failed, re-queuing batch:', err);
    // Push the failed batch back to the front of the buffer for retry on the next flush
    buffer.unshift(...batch);
  }
}
 
// Rejections from async callbacks in setInterval are not automatically propagated, so handle them explicitly
setInterval(() => {
  flushBuffer().catch(err => console.error('Flush interval error:', err));
}, FLUSH_INTERVAL_MS);
 
await consumer.subscribe({ topic: 'clickstream-events' });
await consumer.run({
  eachMessage: async ({ message }) => {
    const event = JSON.parse(message.value!.toString()) as ClickstreamEvent;
    buffer.push(event);
    if (buffer.length >= BATCH_SIZE) {
      await flushBuffer();
    }
  },
});

Pros and Cons

Advantages

Disadvantages and Considerations

Common Mistakes in Practice

Setting ORDER BY too loosely. Using ORDER BY (event_time) means a filter like WHERE event_type = 'purchase' will scan all the data. Always identify your query patterns first and place low-cardinality columns first: ORDER BY (event_type, page_path, event_time).

Using a plain MergeTree as the MV destination table. If you use a plain MergeTree instead of AggregatingMergeTree as the destination, aggregation state functions like countState() won't merge correctly. MV destinations for aggregation must use AggregatingMergeTree or SummingMergeTree.

Attaching MVs to Distributed tables in a cluster environment. Because Distributed tables hold no data directly, MV triggers will not fire. MVs must be attached to the local table on each shard (clickstream_events_local). The aggregation result table must also be structured as a local+Distributed pair.

Calling single-row INSERTs in a loop. Inserting one row at a time causes parts to explode, which backs up merge operations and degrades overall performance. Batch inserts of 1,000–100,000 rows are the baseline.

Closing Thoughts

The core value of ClickHouse lies in the structural advantage created by the combination of insert-time aggregation (Materialized Views) and columnar storage. No matter how much you optimize queries, there's a ceiling on how fast you can make hundred-million-row aggregations work in row-oriented storage. ClickHouse approaches that problem differently at the architecture level.

3 steps you can start with right now:

1. Spin up ClickHouse locally. You can bring it up instantly with Docker: docker run -p 8123:8123 clickhouse/clickhouse-server. Install @clickhouse/client, port one of your existing PostgreSQL analytical queries to ClickHouse, and compare the speed directly.

2. Define one source table and MV pair. Move your most frequently used aggregation query into a Materialized View, and the next time you run that query, the result will already be ready.

3. Set up a PostgreSQL coexistence architecture. You don't need to migrate right away. Using the ClickPipes Postgres connector or PeerDB, you can sync PostgreSQL data to ClickHouse via CDC and gradually transition by offloading only analytical queries first.

A different structure produces different results. The point is to change the architecture, not tune the queries.

References

• ClickHouse Official JavaScript Integration Docs
• Official clickhouse-js GitHub Repository
• Materialized View Rollup Timeseries Guide
• Official Cluster Deployment and Replication Docs
• ClickHouse vs PostgreSQL Official Comparison
• HighLevel: Rebuilding the Data Platform with ClickHouse Cloud
• Wingify: Migrating from Postgres to ClickHouse, 80% Cost Reduction
• Mux: How We Use ClickHouse as a Real-Time Stream Processing Engine
• Inigo: High-Performance Analytics with Materialized Views and ClickHouse
• How to Scale ClickHouse to Billions of Rows
• Hot-Warm-Cold TTL Storage Tiering
• Tinybird: Practical ClickHouse Materialized View Examples
• GlassFlow: How ClickHouse Async Insert Works
• Altinity Deep Dive on ClickHouse Sharding and Replication
• ClickHouse 2025 Annual Roundup