I have a multi-tenant service with a table like this:
project_id | user_id | user_properties
Each project belongs to a different customer and customers can freely attach metadata to their users. A project may have millions of users.
Then a customer may want to find some users inside his project filtering with the
user_properties (e.g. age grater than X, favorite music equal to Y, etc.)
user_properties can be an arbitrary json of key-value pairs and a customer can run arbitrary queries on the
user_properties. The json is not nested (only key-value pairs).
Since a query may return many results it would also be useful to use some sort of pagination (e.g. order by user_id + limit). But pagination, together with arbitrary filters, seems an additional issue for performance...
Is it possible to handle that case in PostgreSQL? Is EAV the only solution?
I see that MongoDB supports wildcard indexes: does PostgreSQL offer anything similar?