Hacker News Reader

Comment by andreareina | Hacker News Reader

Anybody who's used the array types care to share their experience? It's something that caught my eye reading the docs, but wasn't sure how it holds up in actual use.

View on HN

Very convenient, I often use them. There's really no reason not to if your data structure is appropriate.

The only drawback for me is that you can't have arrays of foreign keys. (You could of course just store an array of the key values, but without the integrity checks).

Isn't an associative table the better option here? It would take a lot of the headaches out of dealing with arrays and constraints (ie, duplicates, limits on relationships) while keeping joins simple.

They are solid and easy to use and particularly good for early prototyping. I've found they work best for passive data, if you have attributes tied to a structure that are required by the application but bear little significance to the database then they're solid.

If your data is more complex then they tend to break down a bit, but at that point you should probably be looking at a joining table or other construct that supports foreign keys and such.

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.

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.

What do you want to know... SQL support is excellent including creating arrays during GROUP BY, unnesting them in SELECT, converting to strings and passing to/from user defined functions in JS/python/etc.

Arrays scale as you'd expect, i.e. even if it's allowed, I don't recommend 1MM values in an array because some/many of the aforementioned features aren't tested to scale. AFAIK array operations are single threaded and not vectorized or parallelized.

Works well, only issue I ran into is that multidimensional arrays must all have the same number of elements. Had missed this originally when reading the documentation https://www.postgresql.org/docs/10/static/arrays.html#ARRAYS...

I've used them for storing time series data. I was storing one value per row and ran into performance problems. Worked really well.

I tend to avoid them for storage, but I've found them to be really useful in committed queries. I CSN aggregate a bunch of values into an array on a single record and perform all sorts of useful calculations on them

I find it best to think of them as sets. A lot of operators treat them that way (e.g. containment), and you can use GIN indexing to accelerate access to them using those operators.

Reply on news.ycombinator.com