Hi All,
I was running some pgbench tests and observed this phenomenon. This might be a known issue, but I thought its nevertheless worth mentioning.
Auto-analyze process grabs a MVCC snapshot. If it runs on a very large table, it may take considerable time and would stop the OldestXmin from advancing. During that time, if there are highly updated small tables, those would bloat a lot. For example, in the attached log snippet (and the HEAD is patched a bit to produce more information than what you would otherwise see), for a scale factor of 50 and 50 clients:
branches and tellers tables, which had a stable size of around 65 and 90 pages respectively, bloat to 402 and 499 pages respectively when accounts table is being analyzed. The accounts table analyze takes around 5 mins on my decent server and the branches and tellers tables keep bloating during that time. If these small tables are very actively accessed, vacuum may not be able to even truncate them later, once OldestXmin advances at the end of auto analyze.
I understand analyze needs snapshot to run index predicate functions, but is there something we can do ? There is a PROC_IN_ANALYZE flag, but we don't seem to be using it anywhere. Since acquire_sample_rows() returns palloced tuples, can't we let OldestXmin advance after scanning a page by ignoring procs with the flag set, just like we do for PROC_IN_VACUUM ?
Thanks,
Pavan