Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a table in a PostgreSQL database which is growing on the order of millions of rows per day.

Each row consists of:

ID
Foreign user ID
Date and time
Other data

The date and time aren't strictly monotonic with ID, however they are close.

When querying this table we are only interested in getting rows for a given foreign user ID, with a date and time in the last two weeks. Rows more than two weeks old will never be queried, however we'd like to keep them for archival purposes.

Given this special use case:

  1. Should we have an index on the date and time column?
  2. Is there any hint we can give that the date and time is (almost) monotonically increasing with ID.
  3. Should we look at trying to remove rows older than two weeks from the table, is that likely to improve performance?
share|improve this question
add comment

1 Answer

up vote 6 down vote accepted
  1. Partition and/or use partial indexes, so you only have an index for the part you care about
  2. No, AFAIK there are no correlation hints in Pg yet. Pity.
  3. Depends a lot on the type of queries, but it can be worth doing, especially if you can do it by partitioning.

Partition

This is a classic use for table partitioning. Partition the table into two (or more) parts, one with the hot data and one with the old archival data. Index anything you need to on the hot table, and use fewer indexes to save space and save insert time on the cold table.

With constraint exclusion enabled, Pg will automatically scan only new_table when your queries match the partitioning constraint.

If your queries don't all use the same constraint you can either add a where test that does use the constraint, or just query the new_table partition directly.

Partial indexes

Alternately, you can create partial indexes that only cover the ranges you're interested in. Again, these will only work if your queries use constraints Pg can recognise as matching the partial index. In your case you'd have to create and drop them over time to keep your time range relevant, since you can't index on a dynamic expression like WHERE (some_field > current_timestamp - INTERVAL '2' week).

share|improve this answer
3  
An active flag could be used to create a partial index, but that would require setting the "old" rows to active = false in e.g. a cron job –  a_horse_with_no_name Sep 29 '12 at 7:48
1  
@a_horse_with_no_name Yep; personally I'd just partition on active and use constraint exclusion in that case, since you're rewriting the data anyway; same idea though, and good point. –  Craig Ringer Oct 1 '12 at 7:31
    
Thank you for the comprehensive response and ideas. More karma up for grabs on a follow-up question. –  dukedave Oct 13 '12 at 5:25
add comment

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.