Customers expect analytics inside your product. Not in a separate BI tool. Inside your app, loading fast, showing only their data. ClickHouse is the engine most teams reach for when they need this at scale. PostHog, LaunchDarkly, and Inigo all run customer-facing analytics on it.

What they all discovered: the hard part isn't query performance. It's tenant isolation, and most of the advice online gets the critical details wrong.

Here's what actually works.

Before you continue, full disclaimer, I'm the creator of hypequery the TypeScript SDK for ClickHouse. Check it our if you want to type-safe queries and multi-tenancy APIs.

Start with the right schema

For SaaS with hundreds or thousands of tenants, you want a shared events table. One table, all tenants, isolated by a row policy. This is what every team at scale uses.

The most important decision is where tenant_id sits in the sort key:

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);

tenant_id goes first in ORDER BY. ClickHouse's sparse primary index is based on the sort key. Putting tenant first means per-tenant scans skip other tenants' granules entirely. Without this, every query scans proportionally to total table size, not tenant data size.

Partition by time, not by tenant. PARTITION BY (tenant_id, toYYYYMM(...)) seems intuitive but blows past ClickHouse's recommended part counts once you have more than ~50 tenants. Stick to time-based partitioning and let the primary key handle per-tenant pruning.

Row-level security: the pattern that actually scales

There are two approaches. Only one works for real SaaS.

The naive approach creates one ClickHouse user per tenant with a per-tenant row policy. Simple to understand. Breaks immediately in production because it destroys connection pooling — you need a separate pool per tenant, or you reconnect on every request.

The production approach uses a single shared user with a custom per-query setting:

-- Allow the readonly role to set a custom tenant setting
CREATE ROLE analytics_readonly;
ALTER ROLE analytics_readonly ADD SETTINGS SQL_tenant_id CHANGEABLE_IN_READONLY;
GRANT SELECT ON events TO analytics_readonly;

-- One policy 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;

-- One shared 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 passes the tenant identity 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(),
  },
});

Three reasons this is the right model:

Fail-closed. If your app omits SQL_tenant_id, ClickHouse throws an error. There's no silent path where missing tenant context returns all tenants' data.

Connection pooling works. One pool, one user, all tenants. Tenant identity is at the query level, not the connection level.

Zero DDL to add a tenant. Tenant 10,001 needs no ClickHouse CREATE USER or CREATE POLICY — just a row in your application's tenants table.

One critical detail almost every tutorial misses: CHANGEABLE_IN_READONLY is required on the role. Without it, readonly users can't set the custom setting and every query fails with a permissions error. Also include WHERE tenant_id = ? explicitly in your queries even though the row policy guarantees correctness — the row policy doesn't drive primary key pruning on its own.

Wiring it through TypeScript

The full request path: JWT → extract tenant → ClickHouse query setting → row policy guard.

If you're defining many analytics endpoints, centralising this injection pays off quickly. hypequery's serve API extracts the tenant once in a context callback and passes it to every query through a typed ctx object:

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 } });

Mount in Next.js App Router:

// 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 };

Making it fast

Correct and isolated is the baseline. Sub-100ms is what makes the feature feel like a product.

Aggregate projections are the highest-leverage optimisation. They precompute aggregations inside the same parts as the base table and ClickHouse rewrites queries to use them automatically, no application changes:

ALTER TABLE events ADD PROJECTION hourly_counts
(
  SELECT tenant_id, toStartOfHour(event_time) AS hour, event_type, count()
  GROUP BY tenant_id, hour, event_type
);
ALTER TABLE events MATERIALIZE PROJECTION hourly_counts;

On a 200M-row table, hourly event count queries drop from ~1.4s to ~12ms.

Query Condition Cache (ClickHouse 25.3+) caches which granules matched a WHERE predicate in previous queries. On repeat dashboard loads, common when multiple users at the same company view the same dashboard, this makes responses sub-50ms even without projections. Enable with SET use_query_condition_cache = 1. It's the free win before reaching for heavier optimisations.

Stopping noisy neighbors

Use ClickHouse's built-in settings profiles to tier resource limits by plan:

CREATE SETTINGS PROFILE free_tier
  SETTINGS max_memory_usage = 1073741824,  -- 1 GB
           max_execution_time = 10,
           max_threads = 2;

CREATE SETTINGS PROFILE pro_tier
  SETTINGS max_memory_usage = 4294967296,  -- 4 GB
           max_execution_time = 60,
           max_threads = 8;

For per-tenant rate limiting, ClickHouse's KEYED BY quotas are exactly right — pass quota_key: tenantId in your query settings and ClickHouse tracks each tenant's usage independently:

CREATE QUOTA tenant_quota KEYED BY 'quota_key'
  FOR INTERVAL 1 HOUR MAX queries = 500, read_rows = 10000000000;

GRANT QUOTA tenant_quota TO analytics_readonly;

Set cancel_http_readonly_queries_on_client_close = 1 so abandoned dashboard queries (user navigated away) don't accumulate on the server.

The four things you have to get right

Everything else is optimisation. These four will break you if you get them wrong:

1. tenant_id first in ORDER BY — every query that doesn't have this is scanning too much data.
2. Don't partition by tenant at scale — time-only partitioning keeps part counts manageable.
3. Use the custom-setting row policy — the per-tenant-user approach destroys connection pooling.
4. Include CHANGEABLE_IN_READONLY on the role — without it, every query silently fails.

Get those right, keep your writes batched, and ClickHouse will carry the rest.

If you're building embedded analytics on ClickHouse with TypeScript, hypequery gives you a type-safe query builder, schema introspection, and the tenant-context injection pattern shown above, without reinventing the plumbing for each endpoint.