public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Dealing with SeqScans when Time-based Partitions Cut Over
Date: Thu, 18 Dec 2025 14:32:07 -0500
Message-ID: <CANzqJaAXhYwR6vHzm_DqnxmWk8QCYxEmhs_JeZ+w3F+rx09xSw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
On Thu, Dec 18, 2025 at 1:48 PM Matthew Planchard <[email protected]>
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:
>
> * When the current time crosses a partition boundary, all new records
> are written to the new partition, which was previously empty, as
> expected
>
> * Because the planner's latest knowledge of the partition was based on
> its state prior to the cutover, it assumes the partition is empty and
> creates plans that use sequential scans
>
> * The table accumulates tens to hundreds of thousands of rows, and the
> sequentail scans start to use nearly 100% of available database CPU
>
> * Eventually the planner updates thee stats and all is well, but the
> cycle repeats the next time the partitions cut over.
>
> 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).
>
What's autovacuum_analyze_scale_factor set to? The default 20% is pretty
high.
autovacuum_naptime might need to be dropped, too.
And maybe have the shell script that the cron job runs sleep only 5 seconds
in the ANALY loop.
> 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.
>
How about just force seqscan off when the table is created?
ALTER TABLE <table_partition> SET (enable_seqscan = off);
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Dealing with SeqScans when Time-based Partitions Cut Over
In-Reply-To: <CANzqJaAXhYwR6vHzm_DqnxmWk8QCYxEmhs_JeZ+w3F+rx09xSw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox