public inbox for [email protected]  

help / color / mirror / Atom feed

From: Matthew Planchard <[email protected]>
To: [email protected]
Subject: Dealing with SeqScans when Time-based Partitions Cut Over
Date: Thu, 18 Dec 2025 13:52:22 -0500
Message-ID: <[email protected]> (raw)


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).

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.

- Matthew Planchard

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], [email protected]
  Subject: Re: Dealing with SeqScans when Time-based Partitions Cut Over
  In-Reply-To: <[email protected]>

* 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