On Tue, Dec 19, 2017 at 08:47:52AM -0800, Habib Nahas wrote: > We operate an RDS postgres 9.5 instance and have periodic CPU spikes to > 100%. These spikes appear to be due to autoanalyze kicking on our larger > tables. Not sure if it'll help you, but for our large, insert-only tables partitioned by time, I made several changes from default: - near the end of month, report queries for previous day's data had poor statistics, because autoanalyze scale factor defaults to 0.1, so a table analyzed on the 24th of the month won't be analyzed again until the 26th, so the histogram shows that there's zero rows for previous day, causing nested loop over thousands of rows. - for separate reasons, I increased statistics target on our key columns (up to 3000 for one column). - large stats target on large tables caused (auto)analyze to use large amount of RAM. Therefor I changed our largest tables from monthly partition granuliarity (YYYYMM) to daily (YYYYMMDD). That creates what's traditionally considered to be an excessive number of partitions (and very large pg_attribute/attrdef and pg_statistic tables), but avoids the huge RAM issue, and works for our purposes (and I hope the traditional advice for number of child tables is relaxed in upcoming versions, too). One possibility is a cronjob to set deafult "scale factor" to a modest/default values (0.1) during business hours and an aggressive value (0.005) off-hours. You could do similar with autovacuum_max_workers ... but beware if they're causing high RAM use. I believe autovacuum workers try to "play nice" and the cost are shared between all workers. But I suspect that's not true for CPU cost or RAM use, so there's nothing stopping you from having 9 workers each looping around 2+GB RAM and 100% CPU doing MCV/histogram computation. Maybe that's of some use. Justin
