ClickHouse Performance Tuning

Overview

Optimize ClickHouse API performance with caching, batching, and connection pooling.

Prerequisites

• ClickHouse SDK installed
• Understanding of async patterns
• Redis or in-memory cache available (optional)
• Performance monitoring in place

Latency Benchmarks

Caching Strategy

Response Caching

import { LRUCache } from 'lru-cache';

const cache = new LRUCache<string, any>({
  max: 1000,
  ttl: 60000, // 1 minute
  updateAgeOnGet: true,
});

async function cachedClickHouseRequest<T>(
  key: string,
  fetcher: () => Promise<T>,
  ttl?: number
): Promise<T> {
  const cached = cache.get(key);
  if (cached) return cached as T;

  const result = await fetcher();
  cache.set(key, result, { ttl });
  return result;
}

Redis Caching (Distributed)

import Redis from 'ioredis';

const redis = new Redis(process.env.REDIS_URL);

async function cachedWithRedis<T>(
  key: string,
  fetcher: () => Promise<T>,
  ttlSeconds = 60
): Promise<T> {
  const cached = await redis.get(key);
  if (cached) return JSON.parse(cached);

  const result = await fetcher();
  await redis.setex(key, ttlSeconds, JSON.stringify(result));
  return result;
}

Request Batching

import DataLoader from 'dataloader';

const clickhouseLoader = new DataLoader<string, any>(
  async (ids) => {
    // Batch fetch from ClickHouse
    const results = await clickhouseClient.batchGet(ids);
    return ids.map(id => results.find(r => r.id === id) || null);
  },
  {
    maxBatchSize: 100,
    batchScheduleFn: callback => setTimeout(callback, 10),
  }
);

// Usage - automatically batched
const [item1, item2, item3] = await Promise.all([
  clickhouseLoader.load('id-1'),
  clickhouseLoader.load('id-2'),
  clickhouseLoader.load('id-3'),
]);

Connection Optimization

import { Agent } from 'https';

// Keep-alive connection pooling
const agent = new Agent({
  keepAlive: true,
  maxSockets: 10,
  maxFreeSockets: 5,
  timeout: 30000,
});

const client = new ClickHouseClient({
  apiKey: process.env.CLICKHOUSE_API_KEY!,
  httpAgent: agent,
});

Pagination Optimization

async function* paginatedClickHouseList<T>(
  fetcher: (cursor?: string) => Promise<{ data: T[]; nextCursor?: string }>
): AsyncGenerator<T> {
  let cursor: string | undefined;

  do {
    const { data, nextCursor } = await fetcher(cursor);
    for (const item of data) {
      yield item;
    }
    cursor = nextCursor;
  } while (cursor);
}

// Usage
for await (const item of paginatedClickHouseList(cursor =>
  clickhouseClient.list({ cursor, limit: 100 })
)) {
  await process(item);
}

Performance Monitoring

async function measuredClickHouseCall<T>(
  operation: string,
  fn: () => Promise<T>
): Promise<T> {
  const start = performance.now();
  try {
    const result = await fn();
    const duration = performance.now() - start;
    console.log({ operation, duration, status: 'success' });
    return result;
  } catch (error) {
    const duration = performance.now() - start;
    console.error({ operation, duration, status: 'error', error });
    throw error;
  }
}

Instructions

Step 1: Establish Baseline

Measure current latency for critical ClickHouse operations.

Step 2: Implement Caching

Add response caching for frequently accessed data.

Step 3: Enable Batching

Use DataLoader or similar for automatic request batching.

Step 4: Optimize Connections

Configure connection pooling with keep-alive.

Output

• Reduced API latency
• Caching layer implemented
• Request batching enabled
• Connection pooling configured

Error Handling

Examples

Quick Performance Wrapper

const withPerformance = <T>(name: string, fn: () => Promise<T>) =>
  measuredClickHouseCall(name, () =>
    cachedClickHouseRequest(`cache:${name}`, fn)
  );

Resources

• ClickHouse Performance Guide
• DataLoader Documentation
• LRU Cache Documentation

Next Steps

For cost optimization, see clickhouse-cost-tuning.