Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I need to store message data in SQL. Cannot decide which way to go here.

There is a main class Message, say (simplified):

class Message(models.Model):
    user_id = models.ForeignKey(User)
    text = models.TextField()

Plus, there are other Message classes that inherit this one.

class MmsMessage(Message):
    imagedata = models.ForeignKey(ImageData)

And so on. These other message classes of course can have more than 1 additional field.

Now, I am evaluating the best (fastest) design pattern to make this work. In around 25% of cases I will not be needing additional fields, simply raw Message objects (Message.objects.all). In other cases, I need all data. Additional fields may not necessarily be searchable. Nonetheless, it would be nice thing to have.

I was thinking about:

  • A: Inheritance (concrete, abstract)

    • Abstract inheritance is out. I loose the ability to do: Message.objects.all() which is unacceptable.

    • Concrete inheritance seems to me like a way to go. Tried two approaches. django-model-utils one (select_subclasses) which doesn't need additional queries, but due to lots of inner joins and redundant data in results it is very slow compared to other solutions.

    • django_polymorphic (still concrete inheritance) approach (using contenttypes to know what we are dealing with and then select related fields) is ~4 times faster than select_subclasses (at least on postgresql) - which was a small surprise for me (it requires +n queries where n is a number of child types but still faster due to simpler joins and no unnecessary data results). Tested on 10 000 objects across 20 different Message child types.

  • B: EAV model (many to many for additional attributes)

    • Haven't tested EAV model but I doubt it will be faster than inheritance solution. When I know what column names and types I want, it seems that EAV model loses all its charm.

  • [UPDATED - horse_with_no_name] B1: hstore - similiar to EAV with many to many but possibly much faster (no joins, backend support)

    • Great to add dictionary like custom fields.
    • Downsides: I lose compatibility with other django database backends (I would prefer not to), also it is type-agnostic, key and value is TEXT. I am also worried about making Message table raw queries slower in general due to many TEXT fields in hstore dict.

  • C: XML field in Message table for additional data

    • XML field in Message table is something that feels a little fishy to me. What if I dont need these additional fields (from message child types) to be searchable or indexable - is XML field a good solution?

What is the best option in your opinion?

share|improve this question
Have a look at the hstore extension: postgresql.org/docs/current/static/hstore.html – a_horse_with_no_name Dec 1 '11 at 11:43
Thanks for the link! Looks promising, there is even django-hstore project that provides decent support for this. Downside - Amazon RDS which is a possible target DB in the future doesn't support PostgreSQL as of now. Edited question. – vith Dec 1 '11 at 12:42
This sounds like a problem for a document db, like couch. I cannot see the benefit of the relational overhead if this is the core of the application. It would solve your additional fields problem easily. – Burhan Khalid Dec 19 '11 at 4:35
Unfortunately, this is already pretty huge app which benefits from RDBMS on many levels. So RDBMS as core is a must. FYI we decided to go with hstore. – vith Jan 23 '12 at 0:22
@vith: the queries on the message table will not be slower as long as you only select the hstore attribute when you need it. – a_horse_with_no_name Feb 22 '12 at 17:13

1 Answer

up vote 1 down vote

The simple answer here is to just use a single table. However the real question is why you're putting stuff in a database in the first place. If your intent is to scale up to large sizes, then you probably want to look at a hybrid storage model (indexing messages in the DB and storing the raw message in something like hbase).

share|improve this answer
Went with hstore and single table solution. Why we didnt went with solutions like you suggested is that our indexes cover most of the data part we need. Hybrid storage model is useful if raw message would be much greater than its indexes which is not the case. – vith Feb 22 '12 at 19:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.