Forum: Ruby on Rails Expired items

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
7941135a1ec26984e2eb08c219b20e77?d=identicon&s=25 Youyou Semsem (semyou)
on 2009-05-24 19:17
Hi guys,

I have a model Post which has a expiry_date. I want to know what is the
best way to manage scalability in this case. 2 options:

1. Whenever I want to SELECT from the table, I need to include where
expiry_date > NOW. If the table Post grows like a monster, I will be in
trouble. Imagine after 3 years or more. Indexes will be huge too.

2. Have a trigger, cron job, or a plugin (if it exists) that would go
around the table and move expired items to a new table Post_Archive.
That way, I maintain only current Posts in my main table, which implies
that over 3 years I won't be as bad as option 1.

Any help? comments from your experience?

Thanks,
Youssef
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2009-05-24 20:20
(Received via mailing list)
On May 24, 6:17 pm, Youyou Semsem <rails-mailing-l...@andreas-s.net>
wrote:
> Hi guys,
>
> I have a model Post which has a expiry_date. I want to know what is the
> best way to manage scalability in this case. 2 options:
>
> 1. Whenever I want to SELECT from the table, I need to include where
> expiry_date > NOW. If the table Post grows like a monster, I will be in
> trouble. Imagine after 3 years or more. Indexes will be huge too.
>
You could have a table with millions of rows and such a query would be
fast (as long of course as you had an index on the expiry_date
column). Not to say that moving expired posts somewhere else wouldn't
necessarily be a bad idea but certainly not something I would do
preemptively

Fred
4375b4aca04b8d72482fd409fb6d0b47?d=identicon&s=25 Roderick van Domburg (roderickvd)
on 2009-05-24 22:38
Youyou Semsem wrote:
> 1. Whenever I want to SELECT from the table, I need to include where
> expiry_date > NOW. If the table Post grows like a monster, I will be in
> trouble. Imagine after 3 years or more. Indexes will be huge too.

Obviously using indices is the easiest. I'm not sure how many posts
you're expecting, but unless you *know* it'll run into the millions with
a high percentage of cache misses this sounds like premature
optimization. First let your RDBMS deal with the size of the indices
(it'll do fine) and then let your DBA worry about it (he'll cache it
into RAM and put it on a fast disk).

--
Roderick van Domburg
http://www.nedforce.com
7941135a1ec26984e2eb08c219b20e77?d=identicon&s=25 Youyou Semsem (semyou)
on 2009-05-25 19:54
Roderick van Domburg wrote:
> Youyou Semsem wrote:
>> 1. Whenever I want to SELECT from the table, I need to include where
>> expiry_date > NOW. If the table Post grows like a monster, I will be in
>> trouble. Imagine after 3 years or more. Indexes will be huge too.
>
> Obviously using indices is the easiest. I'm not sure how many posts
> you're expecting, but unless you *know* it'll run into the millions with
> a high percentage of cache misses this sounds like premature
> optimization. First let your RDBMS deal with the size of the indices
> (it'll do fine) and then let your DBA worry about it (he'll cache it
> into RAM and put it on a fast disk).
>
> --
> Roderick van Domburg
> http://www.nedforce.com

Thank you guys. You saved me a lot of work. I will not move them out of
the table even though I know I am supposed to expect billions of rows if
everything goes as expected!

Thanks,
Youssef
This topic is locked and can not be replied to.