I am using postgresql and I am storing recipe data.
Each recipe could have up to 200 nutrients associated with it, but it must have these nutrients not null – kcal, carbs, fats, protein other than that it might or might not have other nutrients.
At the moment I have made carbs, fats, protein, kcal as a columns, because I think that this is what users will be querying most of the time and left other nutrients in the jsonb column.
To make things simpler, if I was to store all nutrients in single jsonb column, would it make the queries slower or there would be no difference?
2
Answers
It mostly depends on how many rows you’re expecting to have. A few dozen or hundreds of recipes it likely won’t make much difference whether you have static columns or jsonb entries for carbs, fats, protein, kcal.
Where it gets a little more complicated is if you expect to have many more rows (tens of thousands, hundreds of thousands, etc.) At that point it will come down to your indexing strategy. It’s simpler to index and query a single column with a btree index. You can index individual fields in your jsonb data as well, but you’ll need to be careful of how you query to use the index correctly.
I would think most queries against this type of data will be inequalities. Not many people want to find a recipe with exactly 376.3 kcal, or 17.3 mcg of selenium. But GIN index over JSONB data does not support inequality, only exact match. On the other hand, btree or gist index queries on inequality are often not very efficient even when it does use the index. The inequality would need to be very selective.