I have a table of entities. All entities have required attributes, one of them is bucket_id, and also user can define custom attributes (attribute_name + value) for entity.
I need the following operations with these entities:
- Filter entities in one bucket by any combination of attributes (must be fast, only equality)
- Get list of names of custom attributes for all entities in one bucket (must be fast)
- All custom attributes and some required can be updated (can be slow)
Required attributes can be table columns. I found two ways to store custom attributes:
- store json and use GIN-index for fast filtering
- store attribute names in separate table and values with (entity_id, custom_attribute_id) key in many-to-many table, but in this case i need to do join on condition like (attribute_id = ? AND entity_id = entity.id) for any new custom attribute in query as i can see
Which one of these approaches is better or can you suggest the better?
New contributor
Максим is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.