Message-ID: <31beb34c3108173ea386d567de92da720da4bb07.camel@cybertec.at>
Subject: Re: Dealing with SeqScans when Time-based Partitions Cut Over
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Matthew Planchard <msplanchard@gmail.com>, pgsql-general@postgresql.org
Date: Fri, 19 Dec 2025 08:47:48 +0100
In-Reply-To: <87bjjv1v96.fsf@gmail.com>
References: <87bjjv1v96.fsf@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/31beb34c3108173ea386d567de92da720da4bb07.camel%40cybertec.at>
Precedence: bulk

On Thu, 2025-12-18 at 13:48 -0500, Matthew Planchard wrote:
> In a table with high insert frequency (~1.5k rows/s) and high query
> frequency (~1k queries/s), partitioned by record creation time, we have
> observed the following behavior:
>=20
> * When the current time crosses a partition boundary, all new records
> =C2=A0 are written to the new partition, which was previously empty, as
> =C2=A0 expected
>=20
> * Because the planner's latest knowledge of the partition was based on
> =C2=A0 its state prior to the cutover, it assumes the partition is empty =
and
> =C2=A0 creates plans that use sequential scans
>=20
> * The table accumulates tens to hundreds of thousands of rows, and the
> =C2=A0 sequentail scans start to use nearly 100% of available database CP=
U
>=20
> * Eventually the planner updates thee stats and all is well, but the
> =C2=A0 cycle repeats the next time the partitions cut over.
>=20
> We have tried setting up a cron job that runs ANALYZE on the most recent
> partition of the table every 15 seconds at the start of the hour, and
> while this does help in reducing the magnitude and duration of the
> problem, it is insufficient to fully resolve it (our engineers are still
> getting daily pages for high DB CPU utilization).
>=20
> We have considered maintaining a separate connection pool with
> connections that have `enable_seqscan` set to `off`, and updating the
> application to use that pool for these queries, but I was hoping the
> community might have some better suggestions.

I would try to tune autovacuum to check more often:

  autovacuum_naptime =3D 5s  # perhaps even less

Then hopefully the new partitions get analyzed early enough.

Yours,
Laurenz Albe