背景
夜谈PostgreSQL 垃圾回收参数优化之 - maintenance_work_mem , autovacuum_work_mem。
http://www.postgres.cn/v2/news/viewone/1/398
https://rhaas.blogspot.com/2019/01/how-much-maintenanceworkmem-do-i-need.html
9.4以前的版本，垃圾回收相关的内存参数maintenance_work_mem，9.4以及以后的版本为autovacuum_work_mem，如果没有设置autovacuum_work_mem，则使用maintenance_work_mem的设置。
这个参数设置的是内存大小有什么用呢？
这部分内存被用于记录垃圾tupleid，vacuum进程在进行表扫描时，当扫描到的垃圾记录ID占满了整个内存（autovacuum_work_mem或maintenance_work_mem），那么会停止扫描表，开始INDEX的扫描。
扫描INDEX时，清理索引中的哪些tuple，实际上是从刚才内存中记录的这些tupleid来进行匹配。
当所有索引都扫描并清理了一遍后，继续从刚才的位点开始扫描表。
过程如下：
1、palloc autovacuum_work_mem memory
2、scan table,
3、dead tuple's tupleid write to autovacuum_work_mem
4、when autovacuum_work_mem full (with dead tuples can vacuum)
5、record table scan offset.
6、scan indexs
7、vacuum index's dead tuple (these: index item's ctid in autovacuum_work_mem)
8、scan indexs end
9、continue scan table with prev's offset
... 显然，如果垃圾回收时autovacuum_work_mem太小，INDEX会被多次扫描，浪费资源，时间。
palloc autovacuum_work_mem memory 这部分内存是使用时分配，并不是直接全部使用掉maintenance_work_mem或autovacuum_work_mem设置的内存，PG代码中做了优化限制:
对于小表，可能申请少量内存，算法请参考如下代码（对于小表，申请的内存数会是保障可记录下整表的tupleid的内存数（当maintenance_work_mem或autovacuum_work_mem设置的内存大于这个值时））。
我已经在如下代码中进行了标注：
/*
* MaxHeapTuplesPerPage is an upper bound on the number of tuples that can
* fit on one heap page. (Note that indexes could have more, because they
* use a smaller tuple header.) We arrive at the divisor because each tuple
* must be maxaligned, and it must have an associated item pointer.
*
* Note: with HOT, there could theoretically be more line pointers (not actual
* tuples) than this on a heap page. However we constrain the number of line
* pointers to this anyway, to avoid excessive line-pointer bloat and not
* require increases in the size of work arrays.
*/
#define MaxHeapTuplesPerPage \
((int) ((BLCKSZ - SizeOfPageHeaderData) / \
(MAXALIGN(SizeofHeapTupleHeader) + sizeof(ItemIdData))))
/*
* Guesstimation of number of dead tuples per page. This is used to
* provide an upper limit to memory allocated when vacuuming small
* tables.
*/
#define LAZY_ALLOC_TUPLES MaxHeapTuplesPerPage
/*
* lazy_space_alloc - space allocation decisions for lazy vacuum
*
* See the comments at the head of this file for rationale.
*/
static void
lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks)
{
long maxtuples;
int vac_work_mem = IsAutoVacuumWorkerProcess() &&
autovacuum_work_mem != -1 ?
autovacuum_work_mem : maintenance_work_mem;
if (vacrelstats->hasindex)
{
maxtuples = (vac_work_mem * 1024L) / sizeof(ItemPointerData);
maxtuples = Min(maxtuples, INT_MAX);
maxtuples = Min(maxtuples, MaxAllocSize / sizeof(ItemPointerData));
/* curious coding here to ensure the multiplication can't overflow */
这里保证了maintenance_work_mem或autovacuum_work_mem不会直接被使用光，
如果是小表，会palloc少量memory。
if ((BlockNumber) (maxtuples / LAZY_ALLOC_TUPLES) > relblocks)
maxtuples = relblocks * LAZY_ALLOC_TUPLES;
/* stay sane if small maintenance_work_mem */
maxtuples = Max(maxtuples, MaxHeapTuplesPerPage);
}
else
{
maxtuples = MaxHeapTuplesPerPage;
}
vacrelstats->num_dead_tuples = 0;
vacrelstats->max_dead_tuples = (int) maxtuples;
vacrelstats->dead_tuples = (ItemPointer)
palloc(maxtuples * sizeof(ItemPointerData));
} maintenance_work_mem这个内存还有一个用途，创建索引时，maintenance_work_mem控制系统在构建索引时将使用的最大内存量。为了构建一个B树索引，必须对输入的数据进行排序，如果要排序的数据在maintenance_work_mem设定的内存中放置不下，它将会溢出到磁盘中。
例子
如何计算适合的内存大小
postgres=# show autovacuum_work_mem ;
autovacuum_work_mem
---------------------
1GB
(1 row)
postgres=# show maintenance_work_mem ;
maintenance_work_mem
----------------------
1GB
(1 row) 也就是说，最多有1GB的内存，用于记录一次vacuum时，一次性可存储的垃圾tuple的tupleid。
tupleid为6字节长度。
/*
* ItemPointer:
*
* This is a pointer to an item within a disk page of a known file
* (for example, a cross-link from an index to its parent table).
* blkid tells us which block, posid tells us which entry in the linp
* (ItemIdData) array we want.
*
* Note: because there is an item pointer in each tuple header and index
* tuple header on disk, it's very important not to waste space with
* structure padding bytes. The struct is designed to be six bytes long
* (it contains three int16 fields) but a few compilers will pad it to
* eight bytes unless coerced. We apply appropriate persuasion where
* possible. If your compiler can't be made to play along, you'll waste
* lots of space.
*/
typedef struct ItemPointerData
{
BlockIdData ip_blkid;
OffsetNumber ip_posid;
} 1G可存储1.7亿条dead tuple的tupleid。
postgres=# select 1024*1024*1024/6;
?column?
-----------
178956970
(1 row) 而自动垃圾回收是在什么条件下触发的呢？
src/backend/postmaster/autovacuum.c
* A table needs to be vacuumed if the number of dead tuples exceeds a
* threshold. This threshold is calculated as
*
* threshold = vac_base_thresh + vac_scale_factor * reltuples
vac_base_thresh: autovacuum_vacuum_threshold
vac_scale_factor: autovacuum_vacuum_scale_factor
postgres=# show autovacuum_vacuum_threshold ;
autovacuum_vacuum_threshold
-----------------------------
50
(1 row)
postgres=# show autovacuum_vacuum_scale_factor ;
autovacuum_vacuum_scale_factor
--------------------------------
0.2
(1 row) 以上设置，表示当垃圾记录数达到50+表大小乘以0.2时，会触发垃圾回收。
可以看成，垃圾记录约等于表大小的20%，触发垃圾回收。
那么1G能存下多大表的垃圾呢？约8.9亿条记录的表。
postgres=# select 1024*1024*1024/6/0.2;
?column?
--------------------
894784850
(1 row) 压力测试例子
postgres=# show log_autovacuum_min_duration ;
log_autovacuum_min_duration
-----------------------------
0
(1 row) create table test(id int primary key, c1 int, c2 int, c3 int);
create index idx_test_1 on test (c1);
create index idx_test_2 on test (c2);
create index idx_test_3 on test (c3); vi test.sql
\set id random(1,10000000)
insert into test values (:id,random()*100, random()*100,random()*100) on conflict (id) do update set c1=excluded.c1, c2=excluded.c2,c3=excluded.c3; pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 1200 垃圾回收记录
2019-02-26 22:51:50.323 CST,,,35632,,5c755284.8b30,1,,2019-02-26 22:51:48 CST,36/22,0,LOG,00000,"automatic vacuum of table ""postgres.public.test"": index scans: 1
pages: 0 removed, 6312 remain, 2 skipped due to pins, 0 skipped frozen
tuples: 4631 removed, 1158251 remain, 1523 are dead but not yet removable, oldest xmin: 1262982800
buffer usage: 39523 hits, 1 misses, 1 dirtied
avg read rate: 0.004 MB/s, avg write rate: 0.004 MB/s
system usage: CPU: user: 1.66 s, system: 0.10 s, elapsed: 1.86 s",,,,,,,,"lazy_vacuum_rel, vacuumlazy.c:407",""
2019-02-26 22:51:50.566 CST,,,35632,,5c755284.8b30,2,,2019-02-26 22:51:48 CST,36/23,1263417553,LOG,00000,"automatic analyze of table ""postgres.public.test"" system usage: CPU: user: 0.16 s, system: 0.04 s, elapsed: 0.24 s",,,,,,,,"do_analyze_rel, analyze.c:722","" index scans:1 表示垃圾回收的表有索引，并且索引只扫描了一次。
说明autovacuum_work_mem足够大，没有出现vacuum时装不下垃圾dead tuple tupleid的情况。
小结
建议：
1、log_autovacuum_min_duration=0，表示记录所有autovacuum的统计信息。
2、autovacuum_vacuum_scale_factor=0.01，表示1%的垃圾时，触发自动垃圾回收。
3、autovacuum_work_mem，视情况定，确保不出现垃圾回收时多次INDEX SCAN.
4、如果发现垃圾回收统计信息中出现了index scans: 超过1的情况，说明：
4.1、需要增加autovacuum_work_mem，增加多少呢？增加到当前autovacuum_work_mem乘以index scans即可。
4.2、或者调低autovacuum_vacuum_scale_factor到当前值除以index scans即可，让autovacuum尽可能早的进行垃圾回收。
参考
http://www.postgres.cn/v2/news/viewone/1/398
https://rhaas.blogspot.com/2019/01/how-much-maintenanceworkmem-do-i-need.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.