I have a huge table with rows > 1000000. However, people will only care about rows that were ordered by updated_at and created recently. There are updated_at and created_at columns in the table. By default, there is a filter for the last 30 days of created_at and there is an order by updated_at desc and a Limit of 30 in the end. The values of created_at goes over 2 years but the users mostly care about the last month.
I have created an index on updated_at and created_at using BTree each.
I have explored partition and it is really what I am looking for where you can divide the tables by created_at. However, there is no way to create a unique index on multiple child tables.
Is there any other way to increase performance? Very few users care about very old data and it should be accessible even though it will slow down.