On Fri, 4 Dec 2020 at 15:39, Marco Colli <collimarc...@gmail.com> wrote:
> Hello! > > We have a multi-tenant service where each customer has millions of users > (total: ~150M rows). Now we would like to let each customer define some > custom columns for his users and then let the customer search his users > efficiently based on these columns. > > This problem seems really hard to solve with PostgreSQL: > > https://stackoverflow.com/questions/5106335/how-to-design-a-database-for-user-defined-fields > > In particular the easiest way would be to add a JSON field on the users > table (e.g. user metadata). However the PostgreSQL GIN index only supports > exact matches and not range queries. This means that a query on a range > (e.g. age > 30) would be extremely inefficient and would result in a table > scan. > You could have a table of (tenant, customer, setting_name, setting_value) so that a btree index on (tenant, setting_name, setting_value) would work for "select customer from my_table where tenant=$1 and setting_name='age' and setting_value > 30" That doesn't deal with setting values having a variety of types, but you could have a distinct user defined settings table for each setting value type that you want to support.
