MIME-Version: 1.0
References: <CAB5vuUwa3QBFvJKCWY9PYWM8CM+Qk0XSq1R84zMjuBibVW8ccw@mail.gmail.com>
In-Reply-To: <CAB5vuUwa3QBFvJKCWY9PYWM8CM+Qk0XSq1R84zMjuBibVW8ccw@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Tue, 28 Oct 2025 11:30:50 -0400
Message-ID: <CAKAnmmLQGwqtD+3kn+sgAspV4dhtYYoE6DrE7uFEhn04ab6S3w@mail.gmail.com>
Subject: Re: Query planner instability on large append-only table with heavy
 same-day inserts
To: Giriraj Sharma <giriraj.sharma27@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008764e5064239b5dd"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmLQGwqtD%2B3kn%2BsgAspV4dhtYYoE6DrE7uFEhn04ab6S3w%40mail.gmail.com>
Precedence: bulk

--0000000000008764e5064239b5dd
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Oct 28, 2025 at 2:36=E2=80=AFAM Giriraj Sharma <giriraj.sharma27@gm=
ail.com>
wrote:

> Would daily range partitioning by effective_date (=E2=89=88 1000 partitio=
ns for 3
> years) be the right long-term approach here?
>

If you are querying based on dates, yes. Does not need to be daily, could
do monthly for example. A retention policy of 3 years is a great candidate
for partitioning.


> Given that inserts and queries almost always target today=E2=80=99s parti=
tion,
> will partition pruning and per-partition statistics fully eliminate the
> stale-statistics problem? Are there known performance implications of
> maintaining ~1000 daily partitions at this scale (10 M/day)? We
> occasionally receive backdated or future-dated inserts =E2=80=94 can such
> out-of-range values be handled efficiently (e.g., by creating partitions =
on
> the fly)?
>

It will help, don't know about eliminate. I would not do daily unless it is
really needed, that's a lot of partitions. Monthly to start with. You
cannot create partitions on the fly, but you can have them go to a default
partition and sort them out later. Or pre-create a bunch of partitions.

If we stay with a single table, what are practical per-table autovacuum
> settings to ensure frequent ANALYZE even as total row count grows into
> billions?
>

Turn off autovacuum for that table. Use cron to run vacuum and analyze
hourly (or some frequency that ensures good plans). If analyze is taking
too long, it can be done per-column as well, although that won't help much
if your sample table is representative. But if you have a lot of other
columns with stable values, you could analyze those less often. Measure and
see.

Should we also experiment with raising the statistics target for
> effective_date from 100 to 1000 using:
>

I don't think that will matter if your analyze is already giving you
index-only scans.

Are there best practices or proven patterns for append-only,
> time-series=E2=80=93like workloads that insert heavily into one day and r=
ead from
> the same day?
>

Partitioning. Ensure fillfactor is 100%. Minimal indexes.

Is there a known best way to make Postgres=E2=80=99s planner more resilient=
 to
> temporary statistic drift for parameterized queries like ours?


Not really, other than giving Postgres updated stats via frequent analyze.

PostgreSQL 14 / 15 tested.


Moving to a newer version is always helpful too.


Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

--0000000000008764e5064239b5dd
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Oct 28, 2025 at 2:36=E2=80=AFAM G=
iriraj Sharma &lt;<a href=3D"mailto:giriraj.sharma27@gmail.com">giriraj.sha=
rma27@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_c=
ontainer"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8=
ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr=
">Would daily range partitioning by effective_date (=E2=89=88 1000 partitio=
ns for 3 years) be the right long-term approach here?<br></div></blockquote=
><div><br></div><div>If you are querying based on dates, yes. Does not need=
 to be daily, could do monthly for example. A retention policy of 3 years i=
s a great candidate for partitioning.</div><div>=C2=A0</div><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Given that inserts and =
queries almost always target today=E2=80=99s partition, will partition prun=
ing and per-partition statistics fully eliminate the stale-statistics probl=
em? Are there known performance implications of maintaining ~1000 daily par=
titions at this scale (10 M/day)? We occasionally receive backdated or futu=
re-dated inserts =E2=80=94 can such out-of-range values be handled efficien=
tly (e.g., by creating partitions on the fly)?<br></div></blockquote><div><=
br></div><div>It will help,=C2=A0don&#39;t know about eliminate. I would no=
t do daily=C2=A0unless it is really needed, that&#39;s a lot of partitions.=
 Monthly to start with. You cannot create partitions on the fly, but you ca=
n have them go to a default partition and sort them out later. Or pre-creat=
e a bunch of partitions.</div><div><br></div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex"><div dir=3D"ltr">If we stay with a single table, what a=
re practical per-table autovacuum settings to ensure frequent ANALYZE even =
as total row count grows into billions?<br></div></blockquote><div><br></di=
v><div>Turn off autovacuum for that table. Use cron to run vacuum and analy=
ze hourly (or some frequency that ensures=C2=A0good plans). If analyze is t=
aking too long, it can be done per-column as well, although that won&#39;t =
help much if your sample table is representative. But if you have a lot of =
other columns with stable values, you could analyze those less often. Measu=
re and see.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"=
margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-lef=
t:1ex"><div dir=3D"ltr">Should we also experiment with raising the statisti=
cs target for effective_date from 100 to 1000 using:<br></div></blockquote>=
<div><br></div><div>I don&#39;t think that will matter if your analyze is a=
lready giving you index-only scans.</div><div><br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr">Are there best practices =
or proven patterns for append-only, time-series=E2=80=93like workloads that=
 insert heavily into one day and read from the same day?<br></div></blockqu=
ote><div><br></div><div>Partitioning. Ensure fillfactor is 100%. Minimal in=
dexes.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
">Is there a known best way to make Postgres=E2=80=99s planner more resilie=
nt to temporary statistic drift for parameterized queries like ours?</block=
quote><div><br></div><div>Not really, other than giving Postgres updated st=
ats via frequent analyze.</div><div><br></div><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex">PostgreSQL 14 / 15 tested.</blockquote><div><br></div>=
<div>Moving to a newer version is always helpful too.=C2=A0</div><div><br><=
/div><div><br></div></div><div dir=3D"ltr" class=3D"gmail_signature"><div d=
ir=3D"ltr"><div>Cheers,</div><div>Greg</div><div><br></div><div>--</div><di=
v>Crunchy Data - <a href=3D"https://www.crunchydata.com" target=3D"_blank">=
https://www.crunchydata.com</a></div><div>Enterprise Postgres Software Prod=
ucts &amp; Tech Support</div><div><br></div></div></div></div>

--0000000000008764e5064239b5dd--