Hi all,
I'm thinking about "partial (or range) vacuum" feature.
As you know, vacuum process scans and re-organizes a whole table,
so huge cpu load will be generated when vacuuming a large table,
and it will take long time (in some cases, it may take 10 minutes
or more).
Huge vacuum process hits a system performance.
Otherwise, dead tuples also hit the performance.
So, I imagine if the vacuum process can be done partially,
the huge vacuum load can be parted, and the performance penalty
of the vacuum can be reduced(parted).
"partial (or range) vacuum" means vacuuming a part of the table.
For example, if you have 10 Gbytes table,
you can execute vacuum partially, 10 times, 1 Gbytes each.
Attached patch extends vacuum syntax and lazy_scan_heap() function.
Backend can process the partial vacuum command as below:
psql$ vacuum table1 (0, 100);
In the above command, "0" means start block number,
and "100" means end block number of the vacuum scan.
Attached image contains three graphs generated with pgstatpage()
function (also attached).
1.) distribution of freespace of the "tellers" table after pgbench.
2.) after partial vacuum, between 200 block and 400 block.
3.) after pgbench running again.
(X-axis: block number, Y-axis: freespace size of a page)
I think the partial vacuum and intelligent pg_autovacuum
makes postgres backend near to vacuum-less.
Is this interesting? Any comments?
--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp
