Hacker News Reader

Comment by jerrysievert | Hacker News Reader

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.

View on HN

That's mentioned in the write-up from five years ago of which this is an update.

Can 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.

I 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.

Quite a bit faster, actually, depending on the dataset.

simplicity, 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.

Reply on news.ycombinator.com