6 ms·

Tagging in Rails 4 using PostgreSQL arrays

57 points by ricny046 13y ago

calebio 13y ago
I think one problem with this is that it doesn't behave like a set which I believe acts_as_taggable_on on does. It doesn't allow a user to tag an object with the same tag more than once. It seems that the Postgres array method would (without other backend code to prevent it) immediately allow a user to tag with the same tag over and over. So for users who are more comfortable using an out of the box solution (even if they are using Postgres) they may have better luck with acts_as_taggable_on. Either way, this is pretty cool!
deleted 13y ago
[deleted]
- avolcano 13y ago
  Why?
deno 13y ago
Using arrays for relations, like tags, is a bad idea. If you try to do anything more complex (joins) later with them you’ll find yourself creating temporary tables with UNNEST and your performance will plummet. This is especially true since materialized views are still WIP. Arrays are best used for logical units of data that you’d otherwise serialize or put in several columns. They are for avoiding having to create additional tables for non‐relational data not the other way around. Think of them as complementary DIY solution to PostgreSQL’s geometric types, like Point or Box[1]. From PostgreSQL manual[2]: > Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements. [1] http://www.postgresql.org/docs/9.2/static/datatype-geometric.html http://www.postgresql.org/docs/9.2/static/datatype-geometric... [2] http://www.postgresql.org/docs/9.2/static/arrays.html http://www.postgresql.org/docs/9.2/static/arrays.html
- saurik 13y ago
  And yet, PostgreSQL ships with two index solutions (the ones mentioned in this article) that allows you to index them as if they were sets, supporting highly efficient queries of the form "give me all entries that have the four tags on them". This really depends on your data model and what kinds of queries you want to perform. Even that tip talks about large numbers of elements: you don't usually have large numbers of tags.
  - 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.
slaxman 13y ago
I am a rails developer and recently shifted to using postgres. it's stuff like this that I love about the rails postgres combination. It makes writing code so easy. agreed that it may do 80% of functionality of current gems but with a little tweak it can achieve the same while keeping your code minimal and readable. another example is the hstore feature in postgres. it makes storage of json type data in a rdbms a breeze.
film42 13y ago
I just approached a problem with tagging about a week ago and wanted to use postgres arrays, but the problem was, I felt like adding proprietary components to ActiveRecord killed the flexibility of my app. Meaning, I'm 99% database agnostic and the 1% keeps me from migrating to some xyz cloud sql solution if I needed to scale up quickly and not get hosed by pricing schemes of certain vendors. Does anyone else have similar thoughts?
- deno 13y ago
  That just seems like a limitation of your mapping layer. I don’t know ActiveRecord, but in SQLAlchemy you can map, say a 2DPoint (virtual) column named ‘position,’ to either native PostgreSQL Point type, 2‐element array, or for databases that support neither to two entirely separate columns quite seamlessly. You can then use table.position as you normally would (table.position == [2,5]) and it will get mapped to the desired operation (e.g. table._x = 2 AND table._y = 5). Though don’t use arrays for tags, see my other post.
- fdr 13y ago
  I don't think worrying about database portability often makes much sense for projects with beyond the simplest access patterns and projects that one does not ship to a client, where one anticipates needing to support many possible databases that are supported on-site. Oftentimes, using features supported only by one or a few database management implementations can make things substantially easier. Ports are often difficult and risky without substantial continuous integration all along because of other quirks in the implementation regardless, both of any ORM layer and of the DBMS. One advantage of arrays can be performance: not having to locate several rows in another relation can be a time-saver; in this case it can act like a nicer denormalization. But, as array elements are not quite first class in SQL (as the manual notes) it doesn't work quite as smoothly if one plans to enrich their tags with a lot of meta-information or other relationships. In any case, I have used this strategy on an analytic-oriented system and it worked great: the queries were simple to read, performed well, and the representation of the row was convenient to use in client programs. I've also used hstore to convenient effect on more OLTP-oriented systems although it too is a Postgres-only (for now) data type extension.
instakill 13y ago
If anyone has problems with updated data in the arrays not being persisting, I actually wrote a post on that today http://paweljaniak.co.za/2013/07/28/rails-4-and-postgres-arrays/ http://paweljaniak.co.za/2013/07/28/rails-4-and-postgres-arr...
lbarrow 13y ago
What's the advantage of doing this instead of just making tags a separate table with a many-to-many relationship with documents?
- plq 13y ago
  Many-to-many relationships come with their own overhead -- you use additional resources for foreign keys, indexes, etc. If your data is seldom updated (or not that big), not used for database operations, and having it all in memory is fine, it can be better to use native arrays.
  - deno 13y ago
    It’s hard to tell how big of an advantage this is, if any, without some actual data.

57 points by ricny046 13y ago

I think one problem with this is that it doesn't behave like a set which I believe acts_as_taggable_on on does. It doesn't allow a user to tag an object with the same tag more than once. It seems that the Postgres array method would (without other backend code to prevent it) immediately allow a user to tag with the same tag over and over. So for users who are more comfortable using an out of the box solution (even if they are using Postgres) they may have better luck with acts_as_taggable_on. Either way, this is pretty cool!

deleted 13y ago

[deleted]

avolcano 13y ago
Why?

deno 13y ago

Using arrays for relations, like tags, is a bad idea. If you try to do anything more complex (joins) later with them you’ll find yourself creating temporary tables with UNNEST and your performance will plummet. This is especially true since materialized views are still WIP. Arrays are best used for logical units of data that you’d otherwise serialize or put in several columns. They are for avoiding having to create additional tables for non‐relational data not the other way around. Think of them as complementary DIY solution to PostgreSQL’s geometric types, like Point or Box[1]. From PostgreSQL manual[2]: > Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements. [1] http://www.postgresql.org/docs/9.2/static/datatype-geometric.html http://www.postgresql.org/docs/9.2/static/datatype-geometric... [2] http://www.postgresql.org/docs/9.2/static/arrays.html http://www.postgresql.org/docs/9.2/static/arrays.html

saurik 13y ago
And yet, PostgreSQL ships with two index solutions (the ones mentioned in this article) that allows you to index them as if they were sets, supporting highly efficient queries of the form "give me all entries that have the four tags on them". This really depends on your data model and what kinds of queries you want to perform. Even that tip talks about large numbers of elements: you don't usually have large numbers of tags.
- 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.

slaxman 13y ago

I am a rails developer and recently shifted to using postgres. it's stuff like this that I love about the rails postgres combination. It makes writing code so easy. agreed that it may do 80% of functionality of current gems but with a little tweak it can achieve the same while keeping your code minimal and readable. another example is the hstore feature in postgres. it makes storage of json type data in a rdbms a breeze.

film42 13y ago

I just approached a problem with tagging about a week ago and wanted to use postgres arrays, but the problem was, I felt like adding proprietary components to ActiveRecord killed the flexibility of my app. Meaning, I'm 99% database agnostic and the 1% keeps me from migrating to some xyz cloud sql solution if I needed to scale up quickly and not get hosed by pricing schemes of certain vendors. Does anyone else have similar thoughts?

deno 13y ago
That just seems like a limitation of your mapping layer. I don’t know ActiveRecord, but in SQLAlchemy you can map, say a 2DPoint (virtual) column named ‘position,’ to either native PostgreSQL Point type, 2‐element array, or for databases that support neither to two entirely separate columns quite seamlessly. You can then use table.position as you normally would (table.position == [2,5]) and it will get mapped to the desired operation (e.g. table._x = 2 AND table._y = 5). Though don’t use arrays for tags, see my other post.
fdr 13y ago
I don't think worrying about database portability often makes much sense for projects with beyond the simplest access patterns and projects that one does not ship to a client, where one anticipates needing to support many possible databases that are supported on-site. Oftentimes, using features supported only by one or a few database management implementations can make things substantially easier. Ports are often difficult and risky without substantial continuous integration all along because of other quirks in the implementation regardless, both of any ORM layer and of the DBMS. One advantage of arrays can be performance: not having to locate several rows in another relation can be a time-saver; in this case it can act like a nicer denormalization. But, as array elements are not quite first class in SQL (as the manual notes) it doesn't work quite as smoothly if one plans to enrich their tags with a lot of meta-information or other relationships. In any case, I have used this strategy on an analytic-oriented system and it worked great: the queries were simple to read, performed well, and the representation of the row was convenient to use in client programs. I've also used hstore to convenient effect on more OLTP-oriented systems although it too is a Postgres-only (for now) data type extension.

instakill 13y ago

If anyone has problems with updated data in the arrays not being persisting, I actually wrote a post on that today http://paweljaniak.co.za/2013/07/28/rails-4-and-postgres-arrays/ http://paweljaniak.co.za/2013/07/28/rails-4-and-postgres-arr...

lbarrow 13y ago

What's the advantage of doing this instead of just making tags a separate table with a many-to-many relationship with documents?

plq 13y ago
Many-to-many relationships come with their own overhead -- you use additional resources for foreign keys, indexes, etc. If your data is seldom updated (or not that big), not used for database operations, and having it all in memory is fine, it can be better to use native arrays.
- deno 13y ago
  It’s hard to tell how big of an advantage this is, if any, without some actual data.