I'm curious about the performance characteristic of Postgres arrays and how to decide when to use an array versus when to use a many-to-many join.
For instance, I have an app with a "project" with users that have different roles (owners and members). Right now, I'm modeling that with a join table that contains the role information. Would those be better as arrays? (I'm inclined to think not.)
Alternatively, I also have a messaging system in place, where the recipients probably would be better modeled with an array.
Anyway, this is great information, and it gives another, nice tool in the quiver. Thanks!
Arrays in postgres are incredibly useful for some things. For instance if you use gist or gin index (I forget which), you can do fast intersection queries on arrays.
This is really useful if you want to do something like get all the posts with a certain set of tags: `select posts.tags && ARRAY['postgres', 'arrays']`.
It's also useful for implementing things like materialized paths if you're modeling hierarchies, or adjacency lists if you're modeling graphs I suppose.
Of course all of this could be done with small join tables, but the array intersection / set syntax gives you some nice tools.
Really you should never have a toss up between a many-to-many join and an array in your schema. The right place to use an array would be a case where you were dealing with a one-to-many join with a trivial entity that probably shouldn't be an entity.
That said, arrays are also terribly useful in computation. There are lots of vector operations that really don't make sense on a result set, but that PostgreSQL can compute very efficiently if presented with an array.
Arrays are useful when order matters. Traditionally, in SQL, you would have to have an `order` field, which takes a bit of overhead to keep correctly ordered. For example, if you wanted to swap two items, you'd need a transaction around it to prevent race conditions. Likewise, deleting an item requires updating the `order` field of each item after the deleted one. With an array, the order is inherent in the structure.
To clarify: I'd think using arrays for mapping out roles to users is probably a bad idea. That's really a relation. On the messaging side, you could make a case for the id's of the users being rolled up in to the message, but that assumes that you don't track anything about the messages on a per-recipient basis (like, "delivered, received, received time, etc.).
I rarely use postgres arrays as an actual storage type, but have used them frequently in results (there are aggregation functions to turn multiple rows into an array). The common use is something like pulling a user's info and roles in a single query with the roles from the many to many relationship aggregated to an array, so the result is one row per user and only requires one trip to the db.. The other common use is similar to above, but for handing off to a stored procedure of some sort. I haven't found a use for it as a stored column type, however.
Comments
I'm curious about the performance characteristic of Postgres arrays and how to decide when to use an array versus when to use a many-to-many join.
For instance, I have an app with a "project" with users that have different roles (owners and members). Right now, I'm modeling that with a join table that contains the role information. Would those be better as arrays? (I'm inclined to think not.)
Alternatively, I also have a messaging system in place, where the recipients probably would be better modeled with an array.
Anyway, this is great information, and it gives another, nice tool in the quiver. Thanks!
Arrays in postgres are incredibly useful for some things. For instance if you use gist or gin index (I forget which), you can do fast intersection queries on arrays.
This is really useful if you want to do something like get all the posts with a certain set of tags: `select posts.tags && ARRAY['postgres', 'arrays']`.
It's also useful for implementing things like materialized paths if you're modeling hierarchies, or adjacency lists if you're modeling graphs I suppose.
Of course all of this could be done with small join tables, but the array intersection / set syntax gives you some nice tools.
Really you should never have a toss up between a many-to-many join and an array in your schema. The right place to use an array would be a case where you were dealing with a one-to-many join with a trivial entity that probably shouldn't be an entity.
That said, arrays are also terribly useful in computation. There are lots of vector operations that really don't make sense on a result set, but that PostgreSQL can compute very efficiently if presented with an array.
Arrays are useful when order matters. Traditionally, in SQL, you would have to have an `order` field, which takes a bit of overhead to keep correctly ordered. For example, if you wanted to swap two items, you'd need a transaction around it to prevent race conditions. Likewise, deleting an item requires updating the `order` field of each item after the deleted one. With an array, the order is inherent in the structure.
To clarify: I'd think using arrays for mapping out roles to users is probably a bad idea. That's really a relation. On the messaging side, you could make a case for the id's of the users being rolled up in to the message, but that assumes that you don't track anything about the messages on a per-recipient basis (like, "delivered, received, received time, etc.).
I rarely use postgres arrays as an actual storage type, but have used them frequently in results (there are aggregation functions to turn multiple rows into an array). The common use is something like pulling a user's info and roles in a single query with the roles from the many to many relationship aggregated to an array, so the result is one row per user and only requires one trip to the db.. The other common use is similar to above, but for handing off to a stored procedure of some sort. I haven't found a use for it as a stored column type, however.