6 ms·

Sure you might get away with it, that’s not the point. The point is tags are a type of relational data and there are already better (and proper) ways to manage

by deno 13y ago

Sure you might get away with it, that’s not the point. The point is tags are a type of relational data and there are already better (and proper) ways to manage relational data in a relational database.

saurik 13y ago
PostgreSQL is an object/relational database, and it is not clear that pulling this out to a relation is "better". This isn't a case of denormalization, as the data isn't being duplicated, and the database supports indexing the content correctly, even having appropriate primitives for manipulating it. Let's put it this way: the set of tags an object has on it represent its location in an N-dimensional (where N is the number of possible tags) coordinate system, much like a longitude/latitude pair represent a location in a 2-dimensional coordinate system. Tags are just more discretized. If you are thinking about the search space of tags like this, wanting to ask questions like "find me documents close to this one in tag-space", then it is not just less efficient but semantically incorrect to pull this information out into a separate table: this isn't some NoSQL BS, this is math. To put this in perspective with the longitude/latitude scenario, this would be like storing a separate table that had "post, axis, value", with post as a foreign key to the post being located, axis being either "longitude" or "latitude", and value being the floating point coordinate. Many, including myself, would say separating longitude and latitude like this is a misunderstanding of the relational data model; its the kind of thing an ORM would do to our data, and ORMs are not known to think much about semantics in the database: they are just mapping concepts blindly. Pulling tags out into a separate table is doing the same thing, conceptually, and causes the same issues asking questions like "similar document" as separating longitude and latitude causes "similar location". You can't index that location as a single "thing" anymore, as it isn't one. However, if the questions you are asking are more "give me the list of posts tagged with this specific tag", and the tagging event is a "thing" that maybe even has who performed the tagging, when the tagging happened, then you'd want a tagging relation. It's all about semantics and use cases.
- deno 13y ago
  In this example longitude/latitude tuple is a single logical unit, and a perfect example for using arrays, though of course PostgreSQL already has this covered. However this isn’t comparable; it’s a single location, single column, not a list of locations. A list of locations gives you choice between using multi‐dimensional array ([[x,y], [x1, y1]…]) or a relational design, and we’re back at square one. I think the crucial element here is whether the set is bounded. A set of coordinates is bounded, a set of tags usually is not. I know I made this point about semantics in my last post, but I’m actually more concerned about the practical aspects of working with arrays used in this, in my opinion, relational (unbounded) case. It may be so that arrays in PostgreSQL will become so powerful as to provide complete alternative for forming relational models… however that wasn’t my experience so far. More complex joins require you to go through UNNEST() and essentially normalize the arrays into temporary tables, which makes for terrible performance. The relational design has also other advantages, a composite primary key has unique guarantees, something that’s not yet possible for arrays, and separate table always gives you the flexibility of including additional metadata as your needs evolve. For me the question really is ‘what are the practical advantages for using arrays over a relational design,’ and not the other way around. After all, PostgreSQL is still mostly a relational database. And of course, as others have pointed out, there are some advantages, but most of them would be considered (micro)optimizations, and I’d rather leave those to database developers.
  - saurik 13y ago
    Operations you tend to perform on tags: "find me posts that have all of these tags", "find me posts that have similar tags to this one", "order the results of any of these queries by properties of the post". Something you usually do not do with tags: "give me a list of every tag that the system has ever seen used on a post". Tags normally don't themselves have properties. The event of a tagging usually does not (but sometimes does) have properties either. I am not at all certain why you would find yourself needing to UNNEST: there's nothing you'd do with a tag where that would be interesting or valuable. In my previous comments, I offered multiple "practical advantages" of using arrays for this case over relations; you are choosing to view them as "microoptimizations", but I'd say the exact opposite: every operation one normally does with tags, when used with arrays, is a single operator in a where clause. Even the simplest case, "show me posts with these tags" is at least slightly harder with the relation. In general, the clearest code with the right semantics tends to also be the one that gives the database the most room to optimize: its the same here; you are just muddling up the N-dimensionality of tags by moving them into a relation. Your attempt to compare a list of locations doesn't make much sense to me, as a list of locations doesn't seem to fit well with the question "give me a list of posts that have similar sets of locations to this other sets of locations"; if you can explain the semantics of that use case more, I might be able to appreciate it. However, right now, I think you are still just caught up looking at tags at the wrong scale: a set of locations is more similar to a set of tag clouds, not a single tag cloud; a tag cloud is a single location in an N-dimensional space, and all of the operations developers tend to perform on that data type are easier to think about, easier to optimize, and even easier to type the SQL for with PostgreSQL, than thinking about them as discrete data items. What separates PostgreSQL from other database systems in this regard is that they actually have index implementations that can make this obvious translation of the semantics of tags fast: normally, you are forced to do the relation, and you end up in the hell of working with relations for things where they make no sense at all. As an example: "give me all the posts from last friday, and all of the tags on each"; this is the exact opposite of your UNNEST scenario: you end up having to rebuild the records of the posts here by doing a join and an array_agg. It would be ok if you bought something, but you didn't, and the only reason it even seems to be "more relational" is due to a backwards understanding of what a tag cloud is (like, to be clear: I really don't like the denormalization-is-good crowd, and I think NoSQL is generally the wrong solution to any problem, but I maintain that splitting a tag cloud into a relation is like splitting a basic data type like a long/lat, or attempting to do an ORM columns->rows mapping, both of which misapply relations; its like you are reading what I said as if I'm talking about long/lat being changed into an array, and totally ignoring the semantics of a long/lat as a location in a two-dimensional space... the fact that "PostgreSQL already has yo covered" for something like long/lat precisely demonstrates that people wouldn't even consider breaking those apart). Can you provide me an example of your problem using arrays here? You seem to just be asserting the same point that they are somehow harder over and over again. The only such example I'm seeing in this post is "uniqueness guarantee", which is a property of a single array and thereby trivially described as a check constraint on the column. This kind of set-oriented use case, FWIW, is actually precisely why GiST and GIN were created, and why they support the intarray contrib module; and this kind of facet-oriented search is one of the reasons why PostgreSQL got so many great contributors via PostGIS.

by deno 13y ago

saurik 13y ago

PostgreSQL is an object/relational database, and it is not clear that pulling this out to a relation is "better". This isn't a case of denormalization, as the data isn't being duplicated, and the database supports indexing the content correctly, even having appropriate primitives for manipulating it. Let's put it this way: the set of tags an object has on it represent its location in an N-dimensional (where N is the number of possible tags) coordinate system, much like a longitude/latitude pair represent a location in a 2-dimensional coordinate system. Tags are just more discretized. If you are thinking about the search space of tags like this, wanting to ask questions like "find me documents close to this one in tag-space", then it is not just less efficient but semantically incorrect to pull this information out into a separate table: this isn't some NoSQL BS, this is math. To put this in perspective with the longitude/latitude scenario, this would be like storing a separate table that had "post, axis, value", with post as a foreign key to the post being located, axis being either "longitude" or "latitude", and value being the floating point coordinate. Many, including myself, would say separating longitude and latitude like this is a misunderstanding of the relational data model; its the kind of thing an ORM would do to our data, and ORMs are not known to think much about semantics in the database: they are just mapping concepts blindly. Pulling tags out into a separate table is doing the same thing, conceptually, and causes the same issues asking questions like "similar document" as separating longitude and latitude causes "similar location". You can't index that location as a single "thing" anymore, as it isn't one. However, if the questions you are asking are more "give me the list of posts tagged with this specific tag", and the tagging event is a "thing" that maybe even has who performed the tagging, when the tagging happened, then you'd want a tagging relation. It's all about semantics and use cases.

deno 13y ago
In this example longitude/latitude tuple is a single logical unit, and a perfect example for using arrays, though of course PostgreSQL already has this covered. However this isn’t comparable; it’s a single location, single column, not a list of locations. A list of locations gives you choice between using multi‐dimensional array ([[x,y], [x1, y1]…]) or a relational design, and we’re back at square one. I think the crucial element here is whether the set is bounded. A set of coordinates is bounded, a set of tags usually is not. I know I made this point about semantics in my last post, but I’m actually more concerned about the practical aspects of working with arrays used in this, in my opinion, relational (unbounded) case. It may be so that arrays in PostgreSQL will become so powerful as to provide complete alternative for forming relational models… however that wasn’t my experience so far. More complex joins require you to go through UNNEST() and essentially normalize the arrays into temporary tables, which makes for terrible performance. The relational design has also other advantages, a composite primary key has unique guarantees, something that’s not yet possible for arrays, and separate table always gives you the flexibility of including additional metadata as your needs evolve. For me the question really is ‘what are the practical advantages for using arrays over a relational design,’ and not the other way around. After all, PostgreSQL is still mostly a relational database. And of course, as others have pointed out, there are some advantages, but most of them would be considered (micro)optimizations, and I’d rather leave those to database developers.
- saurik 13y ago
  Operations you tend to perform on tags: "find me posts that have all of these tags", "find me posts that have similar tags to this one", "order the results of any of these queries by properties of the post". Something you usually do not do with tags: "give me a list of every tag that the system has ever seen used on a post". Tags normally don't themselves have properties. The event of a tagging usually does not (but sometimes does) have properties either. I am not at all certain why you would find yourself needing to UNNEST: there's nothing you'd do with a tag where that would be interesting or valuable. In my previous comments, I offered multiple "practical advantages" of using arrays for this case over relations; you are choosing to view them as "microoptimizations", but I'd say the exact opposite: every operation one normally does with tags, when used with arrays, is a single operator in a where clause. Even the simplest case, "show me posts with these tags" is at least slightly harder with the relation. In general, the clearest code with the right semantics tends to also be the one that gives the database the most room to optimize: its the same here; you are just muddling up the N-dimensionality of tags by moving them into a relation. Your attempt to compare a list of locations doesn't make much sense to me, as a list of locations doesn't seem to fit well with the question "give me a list of posts that have similar sets of locations to this other sets of locations"; if you can explain the semantics of that use case more, I might be able to appreciate it. However, right now, I think you are still just caught up looking at tags at the wrong scale: a set of locations is more similar to a set of tag clouds, not a single tag cloud; a tag cloud is a single location in an N-dimensional space, and all of the operations developers tend to perform on that data type are easier to think about, easier to optimize, and even easier to type the SQL for with PostgreSQL, than thinking about them as discrete data items. What separates PostgreSQL from other database systems in this regard is that they actually have index implementations that can make this obvious translation of the semantics of tags fast: normally, you are forced to do the relation, and you end up in the hell of working with relations for things where they make no sense at all. As an example: "give me all the posts from last friday, and all of the tags on each"; this is the exact opposite of your UNNEST scenario: you end up having to rebuild the records of the posts here by doing a join and an array_agg. It would be ok if you bought something, but you didn't, and the only reason it even seems to be "more relational" is due to a backwards understanding of what a tag cloud is (like, to be clear: I really don't like the denormalization-is-good crowd, and I think NoSQL is generally the wrong solution to any problem, but I maintain that splitting a tag cloud into a relation is like splitting a basic data type like a long/lat, or attempting to do an ORM columns->rows mapping, both of which misapply relations; its like you are reading what I said as if I'm talking about long/lat being changed into an array, and totally ignoring the semantics of a long/lat as a location in a two-dimensional space... the fact that "PostgreSQL already has yo covered" for something like long/lat precisely demonstrates that people wouldn't even consider breaking those apart). Can you provide me an example of your problem using arrays here? You seem to just be asserting the same point that they are somehow harder over and over again. The only such example I'm seeing in this post is "uniqueness guarantee", which is a property of a single array and thereby trivially described as a check constraint on the column. This kind of set-oriented use case, FWIW, is actually precisely why GiST and GIN were created, and why they support the intarray contrib module; and this kind of facet-oriented search is one of the reasons why PostgreSQL got so many great contributors via PostGIS.