3 ms·

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

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.

colanderman 8y ago
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.
asah 8y ago
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.
the_duke 8y ago
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).
- petepete 8y ago
  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.
munk-a 8y ago
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.
pmontra 8y ago
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.
- petepete 8y ago
  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.
  - blattimwind 8y ago
    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).
    - Inviz 8y ago
      select region, count(*) from projects, unnest(region_ids) as region GROUP BY 1;
      - blattimwind 8y ago
        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.
mind-blight 8y ago
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
tofflos 8y ago
I've used them for storing time series data. I was storing one value per row and ran into performance problems. Worked really well.
tappleby 8y ago
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-INPUT https://www.postgresql.org/docs/10/static/arrays.html#ARRAYS...

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.

colanderman 8y ago

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.

asah 8y ago

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.

the_duke 8y ago

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

petepete 8y ago
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.

munk-a 8y ago

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.

pmontra 8y ago

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.

petepete 8y ago
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.
- blattimwind 8y ago
  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).
  - Inviz 8y ago
    select region, count(*) from projects, unnest(region_ids) as region GROUP BY 1;
    - blattimwind 8y ago
      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.

mind-blight 8y ago

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

tofflos 8y ago

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

tappleby 8y ago

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-INPUT https://www.postgresql.org/docs/10/static/arrays.html#ARRAYS...