4 ms·

I would tend to normalise arrays too, but I can see the argument that when getting an 'app' and turning it into an object in $programming_language (say), it's r

I would tend to normalise arrays too, but I can see the argument that when getting an 'app' and turning it into an object in $programming_language (say), it's rather a pain to have to go through the sql result set building up an object from the denormalised data (and arguably results in a lot of wasted data being transmitted). It gets particularly bad if you have more than one 1-many relationship on the object you're trying to build up.

fusiongyro 14y ago
If you're using an ORM, and you almost certainly are, it's equal to all the other work you do to map your objects. If you're querying directly, array_agg on the way out is the same work as unnest is on the way in. You can have your cake and eat it to. Tagging has been discussed a lot recently as a good excuse to use arrays in Postgres, but since the whole point of tagging is to enhance searching I think it really misses the point to do it this way. It appeals to our intuitions only because we think of rows as being large and string arrays as being small, but in practice this sort of intuitive "optimization" is almost always wrong.
- AlisdairO 14y ago
  > If you're using an ORM, and you almost certainly are I'm not a web developer - I spend more of my time in raw-sql-land, so this discussion is largely academic to me :-). That said, even if you're using an ORM, it's presumably not exactly free for the ORM to produce an object out of the thousands of distinct rows you can end up getting if you have multiple one to many relationships. In general, the availability of arrays for the purposes of data transport - less so actual storage - in postgres is rather helpful for these sort of problems. I find working with other DBs to do object storage quite constricting by comparison.
  - fusiongyro 13y ago
    Without some numbers I see no reason to believe that Postgres would be more efficient at transporting arrays than rows. One of those is a highly optimized use-case that literally everybody relies on every day. The other one is a corner case. I'm not saying it's impossible but I'm going to need more than a good feeling to buy it.
    - AlisdairO 13y ago
      Perhaps my wording was unclear - I'm not talking about postgres' efficiency, but efficiency of processing the data that postgres produces. Consider the example of an ORM that's storing an object with three multi-valued properties on the object it's encoding, each with 100 values. Encoding one has a row with three arrays in it, encoding two is the result set from joining to separate tables. In the end, it's quite plainly obvious that it's going to be cheaper (and, frankly, easier) to construct the object from a row with three arrays of size 100 in it than it is from a million row result set. Yes, this is absolutely a corner case (particularly with many multi-valued props of such a cardinality), but I'm not really sure why it requires numbers to prove - unless there's some kind of JDBC/ODBC technique for dealing with this situation that I'm simply unaware of?

fusiongyro 14y ago

If you're using an ORM, and you almost certainly are, it's equal to all the other work you do to map your objects. If you're querying directly, array_agg on the way out is the same work as unnest is on the way in. You can have your cake and eat it to. Tagging has been discussed a lot recently as a good excuse to use arrays in Postgres, but since the whole point of tagging is to enhance searching I think it really misses the point to do it this way. It appeals to our intuitions only because we think of rows as being large and string arrays as being small, but in practice this sort of intuitive "optimization" is almost always wrong.

AlisdairO 14y ago
> If you're using an ORM, and you almost certainly are I'm not a web developer - I spend more of my time in raw-sql-land, so this discussion is largely academic to me :-). That said, even if you're using an ORM, it's presumably not exactly free for the ORM to produce an object out of the thousands of distinct rows you can end up getting if you have multiple one to many relationships. In general, the availability of arrays for the purposes of data transport - less so actual storage - in postgres is rather helpful for these sort of problems. I find working with other DBs to do object storage quite constricting by comparison.
- fusiongyro 13y ago
  Without some numbers I see no reason to believe that Postgres would be more efficient at transporting arrays than rows. One of those is a highly optimized use-case that literally everybody relies on every day. The other one is a corner case. I'm not saying it's impossible but I'm going to need more than a good feeling to buy it.
  - AlisdairO 13y ago
    Perhaps my wording was unclear - I'm not talking about postgres' efficiency, but efficiency of processing the data that postgres produces. Consider the example of an ORM that's storing an object with three multi-valued properties on the object it's encoding, each with 100 values. Encoding one has a row with three arrays in it, encoding two is the result set from joining to separate tables. In the end, it's quite plainly obvious that it's going to be cheaper (and, frankly, easier) to construct the object from a row with three arrays of size 100 in it than it is from a million row result set. Yes, this is absolutely a corner case (particularly with many multi-valued props of such a cardinality), but I'm not really sure why it requires numbers to prove - unless there's some kind of JDBC/ODBC technique for dealing with this situation that I'm simply unaware of?