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!
