Hi team, we have a multi-tenant hybrid table where each row has a

tenant_id

(ULID). The column is low cardinality, and most queries include a

tenant_id

predicate. What’s the best way to index this column?

Sort on tenant_id. If you are looking for high read qps, also partition on it

Thanks for the quick response. Since the column is low cardinality, would an inverted index make more sense here compared to sorting?

How low are we talking and what’s the read latency you want? Also; how many rows on avg per tenant

We have fewer than 20 tenants in total. A couple of hot tenants (~2–3) have >10M rows each, while the rest are <1M rows. Our latency requirement is <50 ms.

I think better to sort for locality

Do we need Kafka upstream to be sorted or partitioned by tenant id?

Partition yes, sort no.

Peak 400QPS steady is around 200QPS

If you have hot partitions, may be don't partition on tenant_id, just sort.

Since we have hot tenants, does that mean upstream partitioning isn’t a strict requirement, and we could just sort on

tenant_id

without partitioning?

Yes, partitioning is not a strict requirements. It helps with scaling high throughput (say 500 or more qps). It can suffer from hot partitions, which is why you want to use high cardinality key (not the csse for you).