Hacker News Reader

Comment by pmontra | Hacker News Reader

Nothing special about it: you have an array in your code and you store it as an array in the database without having to split it into records. I've been using it in a Django project for a model field of type array of strings. There is a django.contrib.postgres.fields.ArrayField for that.

A caveat: I used it only for small arrays (about 10 elements maximum.) I don't know how the ORM/driver/database combination would perform with thousands or more elements.

View on HN

In some cases they make sense, tags being a good example. But the moment you find yourself writing extra code to do multiple updates in order to keep things in sync, that's a sign a normalised option might be a better approach.

I don't feel like tags are a particularly good example. For example, how do you efficiently get a list of all tags and how many X there are per tag? Easy for a relation (SELECT tags.name, COUNT(posts) FROM tags NATURAL JOIN posts GROUP BY tags.name), not so easy for an array (SELECT x.tag, COUNT(posts) FROM (SELECT DISTINCT tag FROM unnest(posts.tags)) AS x JOIN posts ON posts.tags @> [x.tag] GROUP BY x.tag).

select region, count(*) from projects, unnest(region_ids) as region GROUP BY 1;

The issue (iirc) with that sort of query is that it's essentially a join, so you'll get count(projects)*sum(length(regions)) intermediate rows that need to be grouped back together.

Reply on news.ycombinator.com