Solution for postgres vacuum memory limits not enforced
is Given Below:
In Postgres v12, when maintenance_work_mem and autovacuum_work_mem are set, my understanding is that the vacuum and autovacuum sessions should be limited to use the memory limits set by these parameters. But I am seeing more memory being used than these limits by autovacuum sessions, any reason why this would happen? autovacuum_work_mem is set to 100mb but I am seeing the autovacuum process taking couple of GBs.
— Updated after jjanes response. Yes looks like RES includes shared memory. But when default_statistics_target is increased to 3000, the session usage is 463mb, which is way more than 20mb maintenance_work_mem and 128mb shared_buffer. Shouldn’t the process memory be capped to 20+128mb?
postgres=# show maintenance_work_mem ; maintenance_work_mem ---------------------- 20MB (1 row) postgres=# vacuum analyze mdm_context; VACUUM postgres=# show shared_buffers; shared_buffers ---------------- 128MB (1 row) PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 62246 postgres 20 0 422892 165996 139068 R 57.1 15.7 25:06.34 postgres: postgres postgres [local] VACUUM postgres=# show default_statistics_target; default_statistics_target --------------------------- 100 (1 row) postgres=# set default_statistics_target=3000; SET postgres=# vacuum analyze mdm_context; VACUUM PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 62246 postgres 20 0 876132 474384 2976 R 62.9 47.6 25:11.41 postgres: postgres postgres [local] VACUUM
ANALYZE is not constrained by the *_mem settings. It does not have a mechanism for spilling to disk, or for batching, and just uses as much memory as needed to do the work implied by default_statistics_target (or the per-column settings, if those are used)