Multi-tenant custom fields SAAS app

General
#1

Hello,
We’re building Multi-tenant custom fields SAAS app. It’s a kind of a product catalog, where a customer is able to define own custom fields and eventually query/segment products by built-in, but even by those new custom fields.

Is there any recommendation for this feature implementation? JSON fields seem tempting but indexing seems limited (just 1 index, no compound indexes which include json fields). We’re even thinking about joined table just for custom fields and having custom fields as “real” table fields added by DDL on the fly. Is adding new fields/indexes expensive in CB for existing big tables?

The other question is about multi-tenancy. Pro/Con in CB with having for all tenants: common tables, common db, separate dbs, or hybrid, in terms of scalability and cost of managing.

Thanks!

#2

Hi @rookie. Flexibility with things like custom fields per customer is exactly why we added JSON support! That’s almost certainly the way to go. Do you have any examples in mind of things the indexes cannot cover? It’ll help us in prioritizing work on them in the future.

As for multi-tenancy, CockroachDB wasn’t designed to have the number of tables scale linearly with something like how many customers you have. Check out interleaved tables for a way to keep all the data for one customer located together.

#3

Thanks for the quick response!
So, let’s say all tenants’ products are in one table and there’s foreign key tenant_id. Then we have a json field, custom_data. A tenant might have a custom field price. Then the tenant wants to search all his products where price > 100. An index on the foreign key, possibly compound with other “static” fields will speed up the query. But an index on json field will not be useful in this case, right?

Note that we can expect millions of records per tenant. Classical CRUD operations with custom searching/sorting are expected by UI.

Thanks again!

#4

Ah, you’re right. It looks like this isn’t implemented yet, but there’s no reason it couldn’t be. I’ve filed https://github.com/cockroachdb/cockroach/issues/24960 to track it.