Re: postgres vacuum memory limits

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Saturday, July 31, 2021, Ayub M <hiayub(at)gmail(dot)com> wrote:
>> But when default_statistics_target is increased to 3000, the session usage
>> is 463mb

> IIUC, the analyze process doesn’t consult maintenance_work_mem. It simply
> creates an array, in memory, to hold the random sample of rows needed for
> computing the requested statistics.

Yeah. A sample of N rows of the table is going to take X amount of
memory; playing with [maintenance_]work_mem isn't going to affect that.
If you're not happy with the memory consumption, the statistics target
is exactly the knob that's provided to adjust that.

In an ideal world maybe ANALYZE could work within a memory budget that's
smaller than the sample size, but I think that'd inevitably involve a
lot more I/O and significantly worse performance than what we do now.
In any case it'd require a massive rewrite that breaks a lot of
extensions, since the per-datatype APIs for ANALYZE presume in-memory
data.

Keep in mind also that large statistics targets translate to bloat
everywhere else too, since that implies larger pg_statistic entries
for the planner to consult. So I'm not sure that focusing on ANALYZE's
usage in isolation is a helpful way to think about this. If you can't
afford the amount of memory needed to run ANALYZE, you won't like the
downstream behavior either.

regards, tom lane