4 ms·

I have to admit that I'm still not quite sure about arrays in relational databases. Don't get me wrong, I use them all the time, but it kinda feels like when yo

by mhd 11y ago

I have to admit that I'm still not quite sure about arrays in relational databases. Don't get me wrong, I use them all the time, but it kinda feels like when you've got tables that you just know could be normalized more thoroughly. Also: If someone has a good version-control wrapper for stored procedures, that would be swell. And while I'm doing the wishful thinking shtick, maybe a Coffeescript-like preprocessor and a good linter?

timonv 11y ago
We use them a lot where the join table could be over a million rows. Saves a ton of performance and a join. Were talking seconds here on a 3-4s query before, ~1s after.
- pjungwir 11y ago
  Yes, it can make a huge difference. I did this once when I had arrays of ~1 million floats and had to compute statistics on them. I wound up implementing a bunch of stats functions as C stored procedures that operate on arrays, and brought query times down from ~12 seconds to ~20 milliseconds: https://github.com/pjungwir/aggs_for_arrays/ https://github.com/pjungwir/aggs_for_arrays/ Another time arrays are handy is when you don't know how many "columns" you need to return. SQL can't do this, but a variable-length array can. Here is a writeup for one time that came in handy: http://illuminatedcomputing.com/posts/2013/03/fun-postgres-puzzle/ http://illuminatedcomputing.com/posts/2013/03/fun-postgres-p... Another time they are helpful is to throw an `array_agg` into an aggregate query to see what values are getting rolled up. This can be really useful if you're trying to debug weird behavior. Also `(array_agg(...))[1]` is a poor-man's `first` function. :-)
  - Erwin 11y ago
    Thanks -- I had a a query on 9.1 using MEDIAN (implemented in pl/psql) going from 12 seconds to 0.2 seconds by switching to array_to_median(array_agg(the_column)). I found it was faster to actually query a million values and calculate the median in Python than to use that pl/psql MEDIAN so it's nice this can be done easier with array_agg + your thing.
ahachete 11y ago
> I have to admit that I'm still not quite sure about arrays in relational databases. Don't get me wrong, I use them all the time, but it kinda feels like when you've got tables that you just know could be normalized more thoroughly. Why is that? There are many use cases for data (like vector data) which really needs an array. And it would be unwise to store it as columns. Think of, for example, matrix data or a practically unbounded number of double values coming from a sensor. Plus, PostgreSQL has a limit in the number of columns (1600) of a table, of which you could run out soon if representing this kind of data as regular columns rather than array values.
- mhd 11y ago
  That's a nice justification for their inclusion, but I don't really see that as the common actual use case. More often it seems that you'll get arrays where normally a join table would be used (e.g. tags for a blog post, authors for a book).
- hobs 11y ago
  Why wouldnt an unbounded set of values related to a sensor just fit in a one to many model of data? (Instead of a constantly updating row, simply inserting more rows) It seems like you would be trading one fat row for something the database does well (unless you always want all of the sensor data every time)
  - ahachete 11y ago
    An array is basically a set of homogeneous fields indexed by number, which are probably accessed in constant-time. A relational table is a completely different beast. Even after disregarding performance (which might be an abysmal difference in many use cases) it's not obvious how to access by index without "hacks". And if you consider multidimensional data, things get tougher without arrays...
Jgrubb 11y ago
IANADBA, but "normalizing something more" without taking into account the performance impact or the overall usability for the end user sounds like a design mistake. Everything is a nail and all that...
gnud 11y ago
I've not actually used arrays in relational databases, but often wished I had them when doing grouping and aggregates. It would often be nice to do the aggregate on the server side, but also return all the IDs in each group in a clean way.
marcosdumay 11y ago
They are incredibly useful in stored procedures. Also at indexes, pg's full text search is completely reliant on array indexes, ditto for the json storage. I think I've never used them as column type, but I can imagine a few uses there too.
- snuxoll 11y ago
  > I think I've never used them as column type, but I can imagine a few uses there too. A lot of cases where you would use a one:many are useful to store in an array instead. Tags would be a good example, multi-select lists, etc.
  - marcosdumay 11y ago
    Except that if you'll want to search by those tags, using an array, instead of a one:many means you'll need a bigger and more expensive index. It's good for some kind of list that you won't search by. That's very limiting, but not an empty set.
allenwlee 11y ago
i use arrays as a substitute for app-side caching via materialized views
jeltz 11y ago
Array are very useful in ad-hoc queries since you can create arrays with the array_agg() aggregate function and then use those arrays for lot's of handy things.

by mhd 11y ago

timonv 11y ago

We use them a lot where the join table could be over a million rows. Saves a ton of performance and a join. Were talking seconds here on a 3-4s query before, ~1s after.

pjungwir 11y ago
Yes, it can make a huge difference. I did this once when I had arrays of ~1 million floats and had to compute statistics on them. I wound up implementing a bunch of stats functions as C stored procedures that operate on arrays, and brought query times down from ~12 seconds to ~20 milliseconds: https://github.com/pjungwir/aggs_for_arrays/ https://github.com/pjungwir/aggs_for_arrays/ Another time arrays are handy is when you don't know how many "columns" you need to return. SQL can't do this, but a variable-length array can. Here is a writeup for one time that came in handy: http://illuminatedcomputing.com/posts/2013/03/fun-postgres-puzzle/ http://illuminatedcomputing.com/posts/2013/03/fun-postgres-p... Another time they are helpful is to throw an `array_agg` into an aggregate query to see what values are getting rolled up. This can be really useful if you're trying to debug weird behavior. Also `(array_agg(...))[1]` is a poor-man's `first` function. :-)
- Erwin 11y ago
  Thanks -- I had a a query on 9.1 using MEDIAN (implemented in pl/psql) going from 12 seconds to 0.2 seconds by switching to array_to_median(array_agg(the_column)). I found it was faster to actually query a million values and calculate the median in Python than to use that pl/psql MEDIAN so it's nice this can be done easier with array_agg + your thing.

ahachete 11y ago

> I have to admit that I'm still not quite sure about arrays in relational databases. Don't get me wrong, I use them all the time, but it kinda feels like when you've got tables that you just know could be normalized more thoroughly. Why is that? There are many use cases for data (like vector data) which really needs an array. And it would be unwise to store it as columns. Think of, for example, matrix data or a practically unbounded number of double values coming from a sensor. Plus, PostgreSQL has a limit in the number of columns (1600) of a table, of which you could run out soon if representing this kind of data as regular columns rather than array values.

mhd 11y ago
That's a nice justification for their inclusion, but I don't really see that as the common actual use case. More often it seems that you'll get arrays where normally a join table would be used (e.g. tags for a blog post, authors for a book).
hobs 11y ago
Why wouldnt an unbounded set of values related to a sensor just fit in a one to many model of data? (Instead of a constantly updating row, simply inserting more rows) It seems like you would be trading one fat row for something the database does well (unless you always want all of the sensor data every time)
- ahachete 11y ago
  An array is basically a set of homogeneous fields indexed by number, which are probably accessed in constant-time. A relational table is a completely different beast. Even after disregarding performance (which might be an abysmal difference in many use cases) it's not obvious how to access by index without "hacks". And if you consider multidimensional data, things get tougher without arrays...

Jgrubb 11y ago

IANADBA, but "normalizing something more" without taking into account the performance impact or the overall usability for the end user sounds like a design mistake. Everything is a nail and all that...

gnud 11y ago

I've not actually used arrays in relational databases, but often wished I had them when doing grouping and aggregates. It would often be nice to do the aggregate on the server side, but also return all the IDs in each group in a clean way.

marcosdumay 11y ago

They are incredibly useful in stored procedures. Also at indexes, pg's full text search is completely reliant on array indexes, ditto for the json storage. I think I've never used them as column type, but I can imagine a few uses there too.

snuxoll 11y ago
> I think I've never used them as column type, but I can imagine a few uses there too. A lot of cases where you would use a one:many are useful to store in an array instead. Tags would be a good example, multi-select lists, etc.
- marcosdumay 11y ago
  Except that if you'll want to search by those tags, using an array, instead of a one:many means you'll need a bigger and more expensive index. It's good for some kind of list that you won't search by. That's very limiting, but not an empty set.

allenwlee 11y ago

i use arrays as a substitute for app-side caching via materialized views

jeltz 11y ago

Array are very useful in ad-hoc queries since you can create arrays with the array_agg() aggregate function and then use those arrays for lot's of handy things.