The v6 version of quick vacuum, which utilizes the amtargetdelete()
interface for retail indextuple deletion.
Now it is more simple and laconic.
It must be applied after Retail-IndexTuple-Deletion-Access-Method.patch.
BENCHMARKS:
-----------
Initial script:
pgbench -i -s $scale # initial DB generation
"CREATE INDEX pgbench_accounts_ext ON public.pgbench_accounts USING
btree (abalance);" # additional index
Comparison with lazy vacuum:
script:
"DELETE FROM pgbench_accounts WHERE (random() < $factor);" # delete a
part of tuples for cleaning strategies comparison
"VACUUM pgbench_accounts;" # check time of vacuum process by bash 'date
+%s%N | cut -b1-13' command
Results:
| $scale=10 | $scale=100 |
$factor| QVAC | LVAC | QVAC | LVAC |
1E-6 | - | - | 284 | 979 |
1E-5 | 78 | 144 | 288 | 1423 |
1E-4 | 72 | 280 | 388 | 3304 |
1E-3 | 189 | 609 | 2294 | 6029 |
1E-2 | 443 | 783 | 54232| 67884|
1E-1 | 1593 | 1237 | 83092| 86104|
where QVAC - forced use of quick vacuum; LVAC - use lazy vacuum for
index cleanup. $factor corresponds a number of vacuumed tuples. For
example, $scale=10, $factor=1E-1 -> 100000 tuples vacuumed. Time
measured in ms.
So, quick strategy can be used in a vacuum process effectively up to
1-2% of DEAD tuples in a relation.
--
Andrey Lepikhov
Postgres Professional
https://postgrespro.com
The Russian Postgres Company