JSONB, Dynamic Columns, or a Search Engine — How Do You Store Tenant-Defined Fields?

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 TABLE per 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.