I have a huge tables where a lot of DELETEs are being made. From time to time Im vacuuming tables to restore some space back to the system, by executing below
VACUUM FULL VERBOSE ANALYSE table_name
I know autovacuum should do the job, but I cant see any space usage reduction over the time. Full Vacuum it is apparently quite long process and sometimes I realize it wasnt worth effort, because I recoverd only fre Gigs. Other times I can get dozen of space
So I wonder if there is any way to check in Postgres how much space can be released back to OS before any operation are taken / which tables need vacuuming
Im using version 12.
**** Edit ****
In short I need rough estimate of wasted space I can get back after vacuuming.
I'm not sure about "released back to OS".
But for rough estimate of wasted space I would do:
SELECT
schemaname , relname ,
pg_total_relation_size(relid) AS total_size,
n_live_tup , n_dead_tup ,
n_dead_tup * 100.0 /
CASE n_live_tup WHEN 0 THEN 1 ELSE n_live_tup END AS dead_ratio,
pg_total_relation_size(relid)::numeric * n_dead_tup / CASE n_live_tup WHEN 0 THEN 1 ELSE n_live_tup END AS wasted_space
FROM
pg_catalog.pg_stat_all_tables
WHERE
schemaname = 'public'
ORDER BY
wasted_space DESC;
schemaname | relname | total_size | n_live_tup | n_dead_tup | dead_ratio | wasted_space |
---|---|---|---|---|---|---|
public | page_extract | 409575424 | 92428 | 5831 | 6.3086943350499849 | 25838861 |
public | annotation_type | 761856 | 1 | 6 | 600.0000000000000000 | 4571136 |
public | doc_dflt | 38010880 | 15789 | 1120 | 7.0935461397175249 | 2696319 |
public | page | 215359488 | 215567 | 1650 | 0.76542327907332755014 | 1648411 |
public | file | 17784832 | 12995 | 1027 | 7.9030396306271643 | 1405542 |
Logic:
Of course it's a very rough estimation, real results will vary.