Hi
Enclosed is the patch to implement the requirement that issue log message to suggest VACUUM FULL if a table is nearly empty.
The requirement comes from the Postgresql TODO list.
[Benefit]
To find which table is nearly empty and suggest using 'VACUUM FULL' to release the unused disk space this table occupied.
[Analysis]
A table is nearly empty include two scenario:
1. The table occupy small disk size and contains few unused rows.
2. The table occupy large disk size and contains large numbers of unused rows.
Obviously the requirement is used to release the disk in the scenario2.
[Solution details]
A check function is added in the function 'lazy_vacuum_rel' to check if the table is large enough and contains large numbers of unused rows. If it is then issue a log message that suggesting using 'VACUUM FULL' on the table.
The judgement policy is as following:
If the relpage of the table > RELPAGES_VALUES_THRESHOLD(default 1000) then the table is considered to be large enough.
If the free_space/total_space > FREESPACE_PERCENTAGE_THRESHOLD(default 0.5) then the table is considered to have large numbers of unused rows.
The free_space is calculated by reading the details from the FSM pages. This may increase the IO, but expecting very less FSM pages thus it shouldn’t cause
Any problems. Please let me know your suggestions.
[When the log message prints]
When executing SQL command 'VACUUM' or 'VACUUM on a table', this function will be invoked and may issue the log message if the table reach the condition.
When auto vacuum work and execute 'VACUUM on a table’, this function will be invoked and may issue the log message if the table reach the condition.
[Example]
SELECT count(*) from t5;
count
-------
3000
(1 row)
DELETE FROM t5 where f1<2900;
DELETE 2899
SELECT count(*) from t5;
count
-------
101
(1 row)
LOG: automatic vacuum of table "wjdb.public.t5": index scans: 0
pages: 0 removed, 20 remain
tuples: 2899 removed, 101 remain, 0 are dead but not yet removable
buffer usage: 64 hits, 1 misses, 25 dirtied
avg read rate: 0.130 MB/s, avg write rate: 3.261 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.05 sec
LOG: Table "t5" contains large numbers of unused row, suggest using VACUUM FULL on it!
VACUUM t5;
LOG: Table "t5" contains large numbers of unused row, suggest using VACUUM FULL on it!
Kind regards
Jing Wang
Fujitsu Australia