3 ms·
great read, but may I also suggest Array? being able to have a field like: ingredients VARCHAR[] and index like: USING GIN (ingredients) and using an o
by jerrysievert 9y ago
great read, but may I also suggest Array?
being able to have a field like:
ingredients VARCHAR[]
and index like:
USING GIN (ingredients)
and using an operator like @>
SELECT * FROM table WHERE ingredients @> ARRAY['mushrooms', 'sour cream']
gives you such amazing flexibility and speed, it's not even funny.
also, while I was sad to not see PLV8 up there with PostGIS (an amazing extension, btw), I was still happy to see it mentioned with such gusto.
- pvg 9y agoThat's mentioned in the write-up from five years ago of which this is an update.
- rkv 9y agoCan you give me an example where an array would be more beneficial than having another table with {id, name}? I personally have never found a use for them.
- cygned 9y agoI could think of denormalization; e.g. you have an entitiy that can have 0..n tags and you want to speed up the retrieval, I could think of arrays being a faster way.
- jerrysievert 9y agoQuite a bit faster, actually, depending on the dataset.
- jerrysievert 9y agosimplicity, for one - write a quick query to retrieve all recipes that contain all of any number of ingredients. SELECT * FROM recipes r INNER JOIN ingredients i ON (r.id = i.recipe_id) WHERE i.ingredient IN ('mushrooms', 'sour cream') load up some data and run explain analyze on both schemas/queries.