We have a table which has close to 1 billion records taking up to ~315 Gigs. This is just the size of the raw table without the size taken by any indexes.
This table has a
updated_at column which gets updated whenever an entry in the table is added/updated. The use case we have now is to query recently added/modified entries. We don't want to add an index on the
updated_at column for the whole table.
We are considering the following options:
- At the beginning of a month, create a partial index on the table for the duration of the month (Example: If we run this on May 1st, we will create an index for range May 1 - May31).
- At the end of the month, drop the partial index for the previous month (Example: If we run this on May 31, we will drop the index created on April 1 for the range April 1 - April 30)
This way we will have not more than two indexes on the column at a given time.
Since the query loads might not look past more than two months right now, this will work well but not sure if there are any gotchas around it.
- Is the constant create and drop indexes bad for the table?
- How does the performance of the inserts/updates go if there are multiple indexes to update. Though all the updates should only affect one index at a time. The index for the previous month will not receive any updates once we have next month.
Are there better ways to handle this situation than outlined above ?