How to Build Scalable Multi-Tenant Dashboards with ClickHouse

Building customer-facing analytics that load instantly and show only the right data is a major challenge for SaaS companies. Many teams turn to ClickHouse for its speed, but the real hurdle isn’t query performance—it’s tenant isolation. Most online advice oversimplifies this problem, leading to dangerous security gaps or scaling bottlenecks. The teams behind PostHog, LaunchDarkly, and Inigo have solved this at scale. Here’s how they do it.

Why Schema Design Matters More Than Query Speed

For SaaS applications serving hundreds or thousands of tenants, a shared events table with row-level isolation is the gold standard. The key lies in how you structure the table’s sort key. Placing tenant_id first in the ORDER BY clause ensures ClickHouse’s sparse primary index skips irrelevant tenant data entirely during scans.

CREATE TABLE events (
  tenant_id UInt32,
  event_id UUID,
  event_time DateTime64(3),
  user_id UInt64,
  event_type LowCardinality(String),
  properties Map(String, String)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_time, event_id);

Placing tenant_id at the start of the sort key is non-negotiable. Without it, every query scans the entire table, regardless of tenant size. Partitioning by time (not tenant) avoids exploding part counts, which can cripple performance as tenant volume grows.

The Right Way to Enforce Tenant Isolation

A common mistake is creating one ClickHouse user per tenant with a dedicated row policy. While simple in theory, this approach destroys connection pooling—requiring a separate pool for each tenant or constant reconnections. The production-grade solution uses a single shared user with a custom query-level setting.

-- Create a role with a mutable tenant ID setting
CREATE ROLE analytics_readonly;
ALTER ROLE analytics_readonly ADD SETTINGS SQL_tenant_id CHANGEABLE_IN_READONLY;
GRANT SELECT ON events TO analytics_readonly;

-- Define a row policy that reads the per-query setting
CREATE ROW POLICY tenant_isolation ON events FOR SELECT
USING tenant_id = getSetting('SQL_tenant_id')::UInt32
TO analytics_readonly;

-- Grant permissions to a single application user
CREATE USER app_analytics IDENTIFIED BY 'strong_password' SETTINGS readonly = 1;
GRANT analytics_readonly TO app_analytics;
SET DEFAULT ROLE analytics_readonly TO app_analytics;

Your application injects the tenant context per query:

await client.query({
  query: `SELECT event_type, count() AS total FROM events 
          WHERE tenant_id = {tenantId:UInt32} AND event_time >= now() - INTERVAL 7 DAY 
          GROUP BY event_type`,
  query_params: { tenantId },
  clickhouse_settings: {
    SQL_tenant_id: tenantId.toString(),
    quota_key: tenantId.toString(),
  },
});

This model offers three critical advantages:

• Fail-safe isolation: Omitting SQL_tenant_id triggers an error, preventing accidental data leaks.
• Efficient pooling: A single user and connection pool serve all tenants, reducing overhead.
• Zero DDL overhead: Adding a new tenant requires no ClickHouse schema changes—just a row in your application’s database.

A critical detail often overlooked is the CHANGEABLE_IN_READONLY modifier. Without it, readonly users can’t set the custom setting, causing permission errors. Always include WHERE tenant_id = ? in queries, even though the row policy enforces isolation—primary key pruning alone won’t suffice.

Streamlining Tenant Context in TypeScript

The flow from authentication to analytics should be seamless. Centralizing tenant extraction and query injection reduces boilerplate and errors. Libraries like hypequery automate this process by extracting the tenant from JWT tokens and injecting it into every ClickHouse query.

import { createQueryBuilder } from '@hypequery/clickhouse';
import { initServe } from '@hypequery/serve';
import { z } from 'zod';
import type { IntrospectedSchema } from './generated-schema';

const db = createQueryBuilder<IntrospectedSchema>({
  host: process.env.CLICKHOUSE_HOST!,
  username: 'app_analytics',
  password: process.env.CLICKHOUSE_PASSWORD!,
});

const { query, serve } = initServe({
  context: ({ req }) => {
    const payload = verifyJWT(req.headers.get('authorization'));
    return { db, tenantId: payload.tenantId };
  },
});

const eventCounts = query({
  description: 'Event counts by type for the last N days',
  input: z.object({ days: z.number().default(7) }),
  query: ({ ctx, input }) => 
    ctx.db
      .table('events')
      .select(['event_type'])
      .count('event_id', 'total_events')
      .where('tenant_id', 'eq', ctx.tenantId)
      .where('event_time', 'gte', `now() - INTERVAL ${input.days} DAY`)
      .groupBy(['event_type'])
      .orderBy('total_events', 'DESC')
      .settings({ SQL_tenant_id: ctx.tenantId })
      .execute(),
});

export const api = serve({ queries: { eventCounts } });

Mounting this in a Next.js app is straightforward:

// app/api/analytics/[[...slug]]/route.ts
import { createFetchHandler } from '@hypequery/serve';
import { api } from '@/analytics/api';

const handler = createFetchHandler(api.handler);

export { handler as GET, handler as POST };

Optimizing for Sub-100ms Response Times

Correct isolation and security are table stakes—performance is what makes analytics feel native to your product. The highest-impact optimization is precomputing aggregations using aggregate projections. These store summary data alongside the base table, allowing ClickHouse to serve queries from cached results instead of raw events.

Other performance levers include:

• Materialized views for common aggregations like daily active users.
• TTL policies to automatically expire stale data.
• Query caching at the application or CDN layer for repeated requests.

The advancements in ClickHouse’s multi-tenancy capabilities have made real-time, isolated analytics achievable for even the most demanding SaaS products. Teams that nail the schema, security, and performance layers can deliver dashboards that feel instant to users—regardless of scale.