Query planner instability on large append-only table with heavy same-day inserts

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’s data, and query
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 = @effective_date
AND idempotency_id IN (
SELECT unnest(@idempotency_ids::transactions.string[])
);

Typical characteristics:

@effective_date almost always = current date.
@idempotency_ids varies between 2–50 items off-peak and 800–1000 items at
peak hours.
Query is latency-sensitive (expected 5–10 ms).

________________________________
Workload Characteristics

~10–12 million inserts per weekday, almost entirely for the current date.

Practically append-only — 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 × 10 M = 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–10 ms.

After several days (5–7 typically), the query planner flips to a Sequential
Scan, and latency jumps to 2–30 seconds.

Running ANALYZE transactions.commits_v3; restores performance instantly.

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

________________________________
What We’ve Tried

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

_______________________________
Problem Summary

The planner’s row-count estimates for effective_date and idempotency_id
become inaccurate as we continuously append to “today’s” date.
The result is plan instability (index scan ↔ sequential scan) until
statistics are refreshed.
We’re 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 (≈ 1000 partitions for 3
years) be the right long-term approach here?

Given that inserts and queries almost always target today’s partition, 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 — 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 = 0,
autovacuum_analyze_threshold = 50000,
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_threshold = 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’s histogram accuracy for the date distribution?

3. Best Practices

Are there best practices or proven patterns for append-only,
time-series–like workloads that insert heavily into one day and read from
the same day?

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

________________________________

Goal

We’d like a “set it and forget it” architecture — either through
partitioning or robust autovacuum tuning — 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–10 ms range, even as total data volume grows
into billions of rows.

________________________________

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

--

Giriraj Sharma
about.me/girirajsharma