How should we handle rows which won't be queried once they are old in PostgreSQL?

Question

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?

Answer 1

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).