Expired items

Rails
1

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

2

On May 24, 6:17 pm, Youyou S. [email protected]
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

3

Youyou S. 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

4

Roderick van Domburg wrote:

Youyou S. 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