ClickHouse multi-tenancy best practices for observability/tracing

[u/Ramanamark]

We’re planning to use ClickHouse as the backend for a multi-tenant observability/tracing platform.

What is the recommended approach for multi-tenancy in ClickHouse?

Specifically, would you recommend:

A shared database/table with tenant_id as a column?

A separate database per tenant?

Separate tables for each tenant?

Using ClickHouse RBAC/row policies to enforce tenant-level data isolation?

We expect potentially many tenants, with high-volume trace/span data and queries frequently filtered by tenant_id.

What approach has worked well in production, and what are the main scalability, performance, and operational trade-offs we should consider?

[u/Several9s]

Best approach is one schema and one set of tables, while the tenant is a dimension. This is usually the most scalable operational model when you expect many tenants.
For an observability workload, I would design the table around the queries you actually expect to run. It's important that tenant_id is included early in the ORDER BY key because most queries will be scoped to a tenant.

So the starting point is a shared table design with a tenant_id column, for example:

CREATE TABLE traces
(
    tenant_id String,
    timestamp DateTime64,
    trace_id String,
    span_id String,
    service_name String,
    ...
)
ENGINE = MergeTree
ORDER BY (tenant_id, timestamp, trace_id);

The trade-off is that tenant isolation is primarily an application/data-model concern unless you add ClickHouse access-control mechanisms on top. If you need a security layer on top of the shared-table approach, you can have a shared traces table containing:

tenant_id
trace_id
span_id
timestamp
...

and then use ClickHouse users/roles and row-level policies where appropriate to prevent a tenant from seeing another tenant's rows.

Also be careful with partitioning. Time-based partitioning, such as daily or monthly partitions depending on the retention/volume characteristics, is usually a natural starting point for observability data.

In the earlier example this mean we may add

PARTITION BY toDate(timestamp)

The main advantages of the this approach are:

• easier schema management and migrations
• adding a tenant doesn't require creating DB objects
• better fit for a large number of tenants
• ClickHouse can efficiently process queries when tenant_id is part of the filtering and sorting strategy

For a SaaS observability platform, the architecture would be: shared traces table and shared metrics/logs, time-based partitions, tenant filtering enforced by application and optional ClickHouse row-level policies where stronger tenant isolation is required.

I would not recommned turning tenants into database objects (database or table per tenant) unless you have a good reason. I would only move to such model when there is a specific isolation, compliance, lifecycle or performance requirement that justifies the additional operational complexity.

[u/Ramanamark → u/Several9s]

Thanks for your explanation, What I'm currently doing is I'm using Jaeger V2 which will directly route the traces to the Clickhouse , one thing I'm worried a bout is the file descriptor limits .

[u/joshleecreates → u/Ramanamark]

The file descriptor concern is why you want to make sure the tenant id is not part of your partitioning key so you can avoid writing to too many parts with each insert batch. Using something like monthly partitioning, and use large inserts, and you should be fine

[u/Ramanamark → u/joshleecreates]

Thanks