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

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.