Indexing options for Postgres for a table with billion records when you want to index only recent records

Question

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:

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

Specifically.

1. Is the constant create and drop indexes bad for the table?
2. 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 ?

Cheers

Answer 1

Creating and dropping indexes does not harm the table in the least. Also, with a partial index, you will pay the price for index maintenance only if the row matches the condition.

You should consider range partitioning by updated_at. Then queries with that column in the WHERE condition will only scan the necessary partitions. As a side effect, deleting old data becomes easier.

The price you would be paying is twofold:

• Most other queries would become at least slightly slower. Make sure that you don't have too many partitions.

• Updating a row so that updated_at crosses a partition boundary will incur the overhead of moving the row to another partition.