On Mon, Jun 28, 2021 at 2:51 PM Michael Lewis <mlewis@entrata.com> wrote:
I am unclear exactly what you want to do with modified_date. Can you write pseudo code perhaps?
I second this. While I'm not all that familiar with partitioning I am readily getting the feeling that whether or not partitioning is used here is immaterial to the question of how best to retrieve most recent data. My take is that when choosing to partition on time its usually best done because the older data becomes irrelevant over time and thus can be readily ignored. If that data doesn't become stale then the benefit of partitioning is lost since you now have meaningful data on all partitions.
Partitioning, like indexes, are not some super feature whose presence solves all performance problems. They need to be designed and used in a thoughtful manner. Saying "Basically, I need to partition my huge table based on some key and also on date to split the data into smaller dataset for faster query." doesn't indicate that much designing has taken place - only that there is a belief that "smaller datasets make for faster queries". Partitioning doesn't necessarily make the dataset smaller - it just moves parts around. The queries need to only look at a subset of that main dataset and the query described here doesn't do that - and there isn't a feature of the current partitioning implementation, that I know of (limited experience admittedly), that will overcome this reality of the data.