3 ms·

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 A

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

by film42 13y ago

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.