From: | Marco Colli <collimarco91(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Index for range queries on JSON (user defined fields) |
Date: | 2020-12-04 15:39:30 |
Message-ID: | CAFvCgN7Q0Ur2Gsrejai+Qsw=iz0pWBWhgZmNeu2455SGbSZzJg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
Algorithmically it seems possible to use a GIN index (based on btree) for a
range query. Also MongoDB seems to support something similar (
https://docs.mongodb.com/manual/core/index-wildcard/)
Are there any plans to add support for range queries to GIN indexes (on
JSON) in the future versions of PostgreSQL?
Marco Colli
Pushpad
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Lewis | 2020-12-04 16:31:59 | Re: PostgeSQL JSONB Column with various type of data |
Previous Message | Riswana Rahman | 2020-12-04 10:45:27 | PostgeSQL JSONB Column with various type of data |