Been thinking about this one for a while and figured I'd actually ask instead of just assuming our answer is right.
The problem: a multi-tenant product catalog where each tenant can define their own fields on a category — any name, any type, decided at runtime by the store owner, not by us. A car gets "Mileage." A t-shirt gets "Size" and "Color." Nothing hardcoded per vertical.
We ended up with JSONB as the source of truth plus a separate typed index table (text/number/bool/date columns) that gets fully rebuilt from the JSONB on every save, inside the same transaction. It works, but it's a double write on every save, and the index table has no FK back to products (partitioned table, so that's off the table anyway).
The other paths we looked at and didn't pick:
- Dynamic
ALTER TABLEper tenant field — ruled out fast, migrations-as-a-constant-event doesn't scale once tenants are inventing dozens of fields. - Push it to Elasticsearch/Algolia and let it own filtering entirely — probably right at a much bigger scale, but didn't want a sync pipeline and eventual-consistency window before the feature even proved itself.
- JSONB with just a GIN index — fine for equality lookups, falls apart for numeric/date ranges on keys you don't know ahead of time.
So, genuine question for anyone who's solved this before: did you land somewhere different? Per-key expression indexes once a field gets used enough to justify one? CDC into something columnar? Or did pushing to a search engine turn out to be less painful than I'm assuming?
Not trying to sell anything here — just comparing notes on a problem I suspect more people than us have hit.
Top comments (4)
We've seen a middle path work for this: keep JSONB as the source of truth, and only promote a field to an expression index (for example ((attrs->>'mileage')::numeric)) once its usage shows up in slow-query stats, ideally as a partial index scoped to that tenant or category. That avoids rebuilding a full typed index table on every save for fields nobody filters on. The double write is the bit I'd question: have you measured how much of the save cost is the index-table rebuild, versus the filters that actually need range queries?.
We've seen a middle path work for this: keep JSONB as the source of truth, and only promote a field to an expression index (for example ((attrs->>'mileage')::numeric)) once its usage shows up in slow-query stats, ideally as a partial index scoped to that tenant or category. That avoids rebuilding a full typed index table on every save for fields nobody filters on. The double write is the bit I'd question: have you measured how much of the save cost is the index-table rebuild, versus the filters that actually need range queries?
Appreciate the feedback!
The reason we stick with the index table over reactive expression indexes comes down to day-one performance and range-query safety:
No Cold-Start Penalty: E-commerce catalogs are read-heavy. Waiting for slow-query logs to trigger an index means the first users filtering a new tenant field suffer poor performance.
Avoiding Dynamic DDL: Letting tenants create/delete fields on the fly while dynamically managing CREATE/DROP INDEX CONCURRENTLY in production adds messy operational overhead.
Type Safety for Ranges: Typed columns prevent brittle query plans and runtime casting errors (::numeric) if malformed data slips into JSONB.
Since writes are infrequent compared to reads, the transaction overhead of the update is negligible. How do you handle cleaning up stale expression indexes when tenants delete custom fields?
Thanks for explaining the reasoning, the day-one performance and range-safety points are fair for read-heavy catalogs. To answer your question, we'd track expression indexes in a small registry keyed by tenant and field, and a periodic job would drop any whose field is gone using DROP INDEX CONCURRENTLY. We'd also check pg_stat_user_indexes for idx_scan staying at zero before dropping. Your index-table approach avoids that cleanup entirely, which is a real operational advantage.