Re: Index for range queries on JSON (user defined fields)

On Fri, 4 Dec 2020 at 15:39, Marco Colli <collimarco91(at)gmail(dot)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.