Best way to store Article body?

rubyonrails-talk
1

Dear Rails developers,

What is the best way to store an Article.body ?

Usually I put it just inside the articles table. But this proves to be hogging the DB when I try to do a find(:all), or doing the articles in a Category.find(… :include => :articles).

  1. Using find :select seems to be one way.

  2. Moving the Article.body to separate tables is another way. (like BLOBs) But unlike for BLOBs/images, I don’t really like this approach to be used on Article’s body.

  3. Moving the Article.body to filesystem?

Some way or another, the body will be used also for searching purposes. Simple SQL [I]LIKE might good for now… though I’ll be moving to Ferret very soon (having used it and satisfied).

Thank you.

2

Usually I put it just inside the articles table. But this proves to be hogging the DB when I try to do a find(:all), or doing the articles in a Category.find(...... :include => :articles).

How big are the articles?

1. Using find :select seems to be one way.

This could work but has some downsides. Any fields not included in the select will act like they do not exist. @article.body will throw a NoMethodError if it was not included in the select. Also, you can't use :include and :select together. The include overrides the select. So if you need joins you have to do those manually too. I would consider this option if you only want to use it on one or two list type pages.

2. Moving the Article.body to separate tables is another way. (like BLOBs) But unlike for BLOBs/images, I don't really like this approach to be used on Article's body.

Sounds like a good option to me. What don't you like about it. ActiveRecord associations make access easy and the body does not load until you reference it.

3. Moving the Article.body to filesystem?

That sounds like a lot of work for no real benefit over the separate table approach.

Aaron

3

Usually I put it just inside the articles table. But this proves to be hogging the DB when I try to do a find(:all), or doing the articles in a Category.find(… :include => :articles).

How big are the articles?

Not so big perhaps. But 100 articles 100 KB each will cost 10 MB, not very pretty if most of them are wasted (i.e. the body isn’t displayed, maybe just an excerpt).

The problem is mostly because not everything that Model.find requests (in SELECT) is going to be used by controller nor displayed by the view. This is more of a commonality rather than exception. (the exception is, when we have to display the whole thing, and this only happens on ‘show’ action on most cases)

  1. Using find :select seems to be one way.

This could work but has some downsides. Any fields not included in

the select will act like they do not exist. @article.body will throw a NoMethodError if it was not included in the select. Also, you can’t use :include and :select together. The include overrides the select.

So if you need joins you have to do those manually too. I would consider this option if you only want to use it on one or two list type pages.

Yep, you’re right. I’ve heard :select was just invented in Rails 1.2? (is that true? wow)

If I had been able to visualize the whole app into my brain then it’d be easier to decide between :select or blob table… (so I know what all controllers do and what fields they may way) but the app is evolving. I guess so does the DB.

I think it might be a good bargain to start with :select first (since this saves the number of migrations, tables, associations) … then when things get bad, just make a migration for that, to switch to separate tables? (I’m thinking this way for now)

  1. Moving the Article.body to separate tables is another way. (like BLOBs)

But unlike for BLOBs/images, I don’t really like this approach to be used on Article’s body.

Sounds like a good option to me. What don’t you like about it. ActiveRecord associations make access easy and the body does not load

until you reference it.

Yes I agree with you it’s the easiest way to do it without any hacks. (and opens up possibilities of acts_as_* family of plugins)

  1. Moving the Article.body to filesystem?

That sounds like a lot of work for no real benefit over the separate table approach.

Aaron

Thank you Aaron.

I “hate” separate tables maybe from the conceptual side… I think it clutters the DB unnecessarily. There is really some tradeoff we have to do between clean design, practicality/productiveness, and performance. :frowning: [damn, too many tradeoffs]

I love the :select way because on the DB design side, it’s “better”. I’ve had used the separate tables approach back in the days of MySQL MyISAM (fixed for users table, containing no VARCHAR; and dynamic for users_ex table, containing BLOBs and stuff)… and I didn’t like it. Some simple details is that I can have NOT NULL… It’s pretty weird to specify that an association should have at least one child row?