MIME-Version: 1.0
From: Giriraj Sharma <giriraj.sharma27@gmail.com>
Date: Tue, 28 Oct 2025 12:06:02 +0530
Message-ID: <CAB5vuUwa3QBFvJKCWY9PYWM8CM+Qk0XSq1R84zMjuBibVW8ccw@mail.gmail.com>
Subject: Query planner instability on large append-only table with heavy
 same-day inserts
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000888b650642323baf"
Archived-At: <https://www.postgresql.org/message-id/CAB5vuUwa3QBFvJKCWY9PYWM8CM%2BQk0XSq1R84zMjuBibVW8ccw%40mail.gmail.com>
Precedence: bulk

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

Environment

PostgreSQL 14 / 15 tested.
RDS / Aurora Postgres in production.
Primary key index (effective_date, idempotency_id).
Autovacuum enabled.
SQLC (Go) for query generation.
________________________________
Context

We are running into query planner instability on an append-only table that
records daily commit information.
The workload is heavily skewed toward the current day=E2=80=99s data, and q=
uery
performance deteriorates periodically until we run ANALYZE.

________________________________
Table Definition

CREATE TABLE sample_table (
    idempotency_id transactions.string NOT NULL,
    effective_date date NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL,
    CONSTRAINT commits_v3_pk PRIMARY KEY (effective_date, idempotency_id)
);

________________________________
Query Pattern

All our lookups are parameterized (SQLC, Go):

SELECT idempotency_id
FROM sample_table
WHERE effective_date =3D @effective_date
  AND idempotency_id IN (
      SELECT unnest(@idempotency_ids::transactions.string[])
  );

Typical characteristics:

@effective_date almost always =3D current date.
@idempotency_ids varies between 2=E2=80=9350 items off-peak and 800=E2=80=
=931000 items at
peak hours.
Query is latency-sensitive (expected 5=E2=80=9310 ms).

________________________________
Workload Characteristics

~10=E2=80=9312 million inserts per weekday, almost entirely for the current=
 date.

Practically append-only =E2=80=94 no updates or deletes.
No weekend inserts.
Occasional rare inserts for past or future dates (late or early trades).
Retention: ~3 years of data (~1000 days =C3=97 10 M =3D 10+ billion rows).
PostgreSQL 14+.

________________________________
Observed Behavior

Immediately after ANALYZE, this query uses an Index Only Scan on the
primary key (effective_date, idempotency_id) with stable latency around
5=E2=80=9310 ms.

After several days (5=E2=80=937 typically), the query planner flips to a Se=
quential
Scan, and latency jumps to 2=E2=80=9330 seconds.

Running ANALYZE transactions.commits_v3; restores performance instantly.

We currently run manual ANALYZE twice a day via pg_cron, which helps but
doesn=E2=80=99t fully guarantee stability (especially in test environments =
where
insert patterns are more random).

________________________________
What We=E2=80=99ve Tried

Manual and scheduled ANALYZE runs (twice a day, each run takes ~30s) =E2=86=
=92
improves performance but not sustainable long-term.
Verified query execution plans before and after ANALYZE =E2=80=94 planner s=
witches
from Index Only Scan to Seq Scan as statistics become stale.
Confirmed table=E2=80=99s autovacuum is running (last one occurred 15 days =
ago),
but its frequency isn=E2=80=99t sufficient to keep stats current during hig=
h insert
periods. Could this be an issue at all given that we do run ANALYZE
at-least twice ?

_______________________________
Problem Summary

The planner=E2=80=99s row-count estimates for effective_date and idempotenc=
y_id
become inaccurate as we continuously append to =E2=80=9Ctoday=E2=80=99s=E2=
=80=9D date.
The result is plan instability (index scan =E2=86=94 sequential scan) until
statistics are refreshed.
We=E2=80=99re looking for a solution that keeps plans stable without manual=
 ANALYZE
as data volume scales.

________________________________

Questions for the Community

1. Partitioning

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

Given that inserts and queries almost always target today=E2=80=99s partiti=
on, 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)?

2. Autovacuum / Analyze Tuning

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?

Would it make sense to use:

ALTER TABLE transactions.commits_v3
  SET (
    autovacuum_analyze_scale_factor =3D 0,
    autovacuum_analyze_threshold =3D 50000,
    autovacuum_vacuum_scale_factor =3D 0.05,
    autovacuum_vacuum_threshold =3D 10000
  );

to decouple analyze frequency from table size?

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

ALTER TABLE transactions.commits_v3
  ALTER COLUMN effective_date SET STATISTICS 1000;

to improve the planner=E2=80=99s histogram accuracy for the date distributi=
on?

3. Best Practices

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

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

________________________________

Goal

We=E2=80=99d like a =E2=80=9Cset it and forget it=E2=80=9D architecture =E2=
=80=94 either through
partitioning or robust autovacuum tuning =E2=80=94 where:
The planner always chooses the index scan for same-day queries.
We no longer need manual ANALYZE (that runs via pg_cron).
Query latency remains in the 5=E2=80=9310 ms range, even as total data volu=
me grows
into billions of rows.

________________________________

Any recommendations, benchmark references, or production-proven strategies
for this workload would be highly appreciated.


--=20

Giriraj Sharma
about.me/girirajsharma

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

<div dir=3D"ltr">Environment<br><br>PostgreSQL 14 / 15 tested.<br>RDS / Aur=
ora Postgres in production.<br>Primary key index (effective_date, idempoten=
cy_id).<br>Autovacuum enabled.<br>SQLC (Go) for query generation.<br>______=
__________________________<br>Context<br><br>We are running into query plan=
ner instability on an append-only table that records daily commit informati=
on.<br>The workload is heavily skewed toward the current day=E2=80=99s data=
, and query performance deteriorates periodically until we run ANALYZE.<br>=
<br>________________________________<br>Table Definition<br><br><font face=
=3D"monospace">CREATE TABLE sample_table (<br>=C2=A0 =C2=A0 idempotency_id =
transactions.string NOT NULL,<br>=C2=A0 =C2=A0 effective_date date NOT NULL=
,<br>=C2=A0 =C2=A0 created_at timestamptz DEFAULT now() NOT NULL,<br>=C2=A0=
 =C2=A0 CONSTRAINT commits_v3_pk PRIMARY KEY (effective_date, idempotency_i=
d)<br>);<br></font><br>________________________________<br>Query Pattern<br=
><br>All our lookups are parameterized (SQLC, Go):<br><br><font face=3D"mon=
ospace">SELECT idempotency_id<br>FROM sample_table<br>WHERE effective_date =
=3D @effective_date<br>=C2=A0 AND idempotency_id IN (<br>=C2=A0 =C2=A0 =C2=
=A0 SELECT unnest(@idempotency_ids::transactions.string[])<br>=C2=A0 );</fo=
nt><br><br>Typical characteristics:<br><br>@effective_date almost always =
=3D current date.<br>@idempotency_ids varies between 2=E2=80=9350 items off=
-peak and 800=E2=80=931000 items at peak hours.<br>Query is latency-sensiti=
ve (expected 5=E2=80=9310 ms).<br><br>________________________________<br>W=
orkload Characteristics<br><br>~10=E2=80=9312 million inserts per weekday, =
almost entirely for the current date.<br><br>Practically append-only =E2=80=
=94 no updates or deletes.<br>No weekend inserts.<br>Occasional rare insert=
s for past or future dates (late or early trades).<br>Retention: ~3 years o=
f data (~1000 days =C3=97 10 M =3D 10+ billion rows).<br>PostgreSQL 14+.<br=
><br>________________________________<br>Observed Behavior<br><br>Immediate=
ly after ANALYZE, this query uses an Index Only Scan on the primary key (ef=
fective_date, idempotency_id) with stable latency around 5=E2=80=9310 ms.<b=
r><br>After several days (5=E2=80=937 typically), the query planner flips t=
o a Sequential Scan, and latency jumps to 2=E2=80=9330 seconds.<br><br>Runn=
ing ANALYZE transactions.commits_v3; restores performance instantly.<br><br=
>We currently run manual ANALYZE twice a day via pg_cron, which helps but d=
oesn=E2=80=99t fully guarantee stability (especially in test environments w=
here insert patterns are more random).<br><br>_____________________________=
___<br>What We=E2=80=99ve Tried<br><br>Manual and scheduled ANALYZE runs (t=
wice a day, each run takes ~30s) =E2=86=92 improves performance but not sus=
tainable long-term.<br>Verified query execution plans before and after ANAL=
YZE =E2=80=94 planner switches from Index Only Scan to Seq Scan as statisti=
cs become stale.<br>Confirmed table=E2=80=99s autovacuum is running (last o=
ne occurred 15 days ago), but its frequency isn=E2=80=99t sufficient to kee=
p stats current during high insert periods. Could this be an issue at all g=
iven that we do run ANALYZE at-least twice ?<br><br>_______________________=
________<br>Problem Summary<br><br>The planner=E2=80=99s row-count estimate=
s for effective_date and idempotency_id become inaccurate as we continuousl=
y append to =E2=80=9Ctoday=E2=80=99s=E2=80=9D date.<br>The result is plan i=
nstability (index scan =E2=86=94 sequential scan) until statistics are refr=
eshed.<br>We=E2=80=99re looking for a solution that keeps plans stable with=
out manual ANALYZE as data volume scales.<br><br>__________________________=
______<br><br>Questions for the Community<br><br>1. Partitioning<br><br>Wou=
ld daily range partitioning by effective_date (=E2=89=88 1000 partitions fo=
r 3 years) be the right long-term approach here?<br><br>Given that inserts =
and queries almost always target today=E2=80=99s partition, will partition =
pruning and per-partition statistics fully eliminate the stale-statistics p=
roblem? 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 effi=
ciently (e.g., by creating partitions on the fly)?<br><br>2. Autovacuum / A=
nalyze Tuning<br><br>If we stay with a single table, what are practical per=
-table autovacuum settings to ensure frequent ANALYZE even as total row cou=
nt grows into billions?<br><br>Would it make sense to use:<font face=3D"mon=
ospace"><br><br>ALTER TABLE transactions.commits_v3<br>=C2=A0 SET (<br>=C2=
=A0 =C2=A0 autovacuum_analyze_scale_factor =3D 0,<br>=C2=A0 =C2=A0 autovacu=
um_analyze_threshold =3D 50000,<br>=C2=A0 =C2=A0 autovacuum_vacuum_scale_fa=
ctor =3D 0.05,<br>=C2=A0 =C2=A0 autovacuum_vacuum_threshold =3D 10000<br>=
=C2=A0 );</font><br><br>to decouple analyze frequency from table size?<br><=
br>Should we also experiment with raising the statistics target for effecti=
ve_date from 100 to 1000 using:<br><br><font face=3D"monospace">ALTER TABLE=
 transactions.commits_v3<br>=C2=A0 ALTER COLUMN effective_date SET STATISTI=
CS 1000;<br></font><br>to improve the planner=E2=80=99s histogram accuracy =
for the date distribution?<br><br>3. Best Practices<br><br>Are there best p=
ractices or proven patterns for append-only, time-series=E2=80=93like workl=
oads that insert heavily into one day and read from the same day?<br><br>Is=
 there a known best way to make Postgres=E2=80=99s planner more resilient t=
o temporary statistic drift for parameterized queries like ours?<br><br>___=
_____________________________<br><br>Goal<br><br>We=E2=80=99d like a =E2=80=
=9Cset it and forget it=E2=80=9D architecture =E2=80=94 either through part=
itioning or robust autovacuum tuning =E2=80=94 where:<br>The planner always=
 chooses the index scan for same-day queries.<br>We no longer need manual A=
NALYZE (that runs via pg_cron).<br>Query latency remains in the 5=E2=80=931=
0 ms range, even as total data volume grows into billions of rows.<br><br>_=
_______________________________<br><br>Any recommendations, benchmark refer=
ences, or production-proven strategies for this workload would be highly ap=
preciated.<br><br><br>-- <br>=C2=A0<br>Giriraj Sharma<br><a href=3D"http://=
about.me/girirajsharma">about.me/girirajsharma</a><br>=C2=A0<br>=C2=A0</div=
>

--000000000000888b650642323baf--