Re: Use case stuck due to Partitioning

On Mon, Jun 28, 2021 at 2:51 PM Michael Lewis <mlewis(at)entrata(dot)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.

David J.