[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Use case stuck due to Partitioning
From:       "David G. Johnston" <david.g.johnston () gmail ! com>
Date:       2021-06-28 23:10:51
Message-ID: CAKFQuwYwPE79x4bJOr3G9EATQTQzai_LHGgL=XU-iNxL5GsVzw () mail ! gmail ! com
[Download RAW message or body]

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.

David J.

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr"><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><span \
style="font-family:Arial,Helvetica,sans-serif">On Mon, Jun 28, 2021 at 2:51 PM \
Michael Lewis &lt;<a href="mailto:mlewis@entrata.com">mlewis@entrata.com</a>&gt; \
wrote:</span><br></div></div><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="auto">I am unclear \
exactly what you want to do with  modified_date. Can you write pseudo code \
perhaps?<div dir="auto"><br></div></div></div></blockquote><div><br></div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif">I second this.   \
While I&#39;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&#39;t become stale \
then the benefit of partitioning is lost since you now have meaningful data on all \
partitions.</div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif">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 &quot;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.&quot; doesn&#39;t indicate that much designing has \
taken place - only that there is a belief that &quot;smaller datasets make for faster \
queries&quot;.   Partitioning doesn&#39;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&#39;t do that - and there isn&#39;t a \
feature of the current partitioning implementation, that I know of (limited \
experience admittedly), that will overcome this reality of the data.</div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif">David \
J.</div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><br></div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]