How should we handle rows which won't be queried once they are old in PostgreSQL?
Partition and/or use partial indexes, so you only have an index for the part you care about
No, AFAIK there are no correlation hints in Pg yet. Pity.
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 &gt; current_timestamp - INTERVAL '2' week).