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:
- Should we have an index on the date and time column?
- Is there any hint we can give that the date and time is (almost) monotonically increasing with ID.
- Should we look at trying to remove rows older than two weeks from the table, is that likely to improve performance?