1

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?

1
  • 1
    In general, you can't create indexes for arbitrary range conditions, only for equality (containment) conditions (e.g. using the @> or ? operators). An EAV model would require a specialized index as well (similar to the one in Laurenz' answer), as your "value" column would not be a date and you need to cast it together with a WHERE condition to make sure you are not casting an attribute that contains something else. – a_horse_with_no_name Nov 24 '20 at 6:45
2

This won't work. In particular, you cannot create a single index that will support conditions like

WHERE (user_properties ->> 'some random property')::date >= '2000-01-01'::date

for arbitrary attributes.

You will have to identify the properties you want to use in comparisons and create specialized indexes on these properties. In the above sample, that would be:

CREATE INDEX ON mytable (((user_properties ->> 'some random property')::date));

It would be better to extract such columns from the JSON and have them be regular table columns.

3
  • Yes, I know that normalization is the best approach in general... but the problem here is that we cannot extract the fields from JSON and have a regular table, because we are dealing with arbitrary attributes defined by customers of our multi-tenant application. – collimarco Nov 24 '20 at 11:04
  • Imagine something like a email service where each customer can add custom columns to his list of contacts... how would you structure that in PostgreSQL? In particular considering that query performance matters (i.e. a search / filter on that contacts, using the custom columns, must be fast) – collimarco Nov 24 '20 at 11:09
  • 1
    I am not telling you if your request is reasonable or not. I am just telling you that you cannot get it. You will have to make concessions, like only allow comparisons with =. – Laurenz Albe Nov 24 '20 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.