
Using Postgres Arrays in Django - g3orge
http://craigkerstiens.com/2012/11/06/django-and-arrays/
======
SoftwareMaven
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!

~~~
netghost
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.

------
thanatos519
A very sensible implementation, but the syntax tastes clunky and exposes the
SQL data type.

Instead of letting the SQL leak into my code, as in texts =
ArrayField(dbtype="text", dimension=2) I want to write text =
models.TextField() ''.Array(dimension=2) so can't djorm_pgarray.fields (or any
other array-supporting ORM magic) add Array() to the Field base class?

------
waterside81
This looks like one of those "sometimes it's better, sometimes it's not"
features. If you're using the Django admin, then the many-to-many relationship
would be preferable because it's a better way to manage lists. If you're not
and you're just storing lists of simple data, then this might be better. YMMV.

------
cbsmith
Didn't we just have a post of this? [http://www.niwi.be/2012/10/07/postgresql-
array-fields-with-d...](http://www.niwi.be/2012/10/07/postgresql-array-fields-
with-django/)

