postgresqlpostgresql-12

Postgres - how to check how much OS space can be recovered


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.


Solution

  • 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.