Seems that th standard vacuum preformed by the autovacuum daemon dosn't reclaim space. Although I'm not sure how this is presented in postgresql

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained.

Further it may be worth preforming a full vacuum due to the heavy delete update behaviour on this table

Tip: Plain VACUUM may not be satisfactory when a table contains large numbers of dead row versions as a result of massive update or delete activity. If you have such a table and you need to reclaim the excess disk space it occupies, you will need to use VACUUM FULL, or alternatively CLUSTER or one of the table-rewriting variants of ALTER TABLE. These commands rewrite an entire new copy of the table and build new indexes for it. All these options require exclusive lock. Note that they also temporarily use extra disk space approximately equal to the size of the table, since the old copies of the table and indexes can't be released until the new ones are complete.

https://www.postgresql.org/docs/9.2/routine-vacuuming.html

This would require downtime and likely more disk space

i have done some digging on the on disk size vs the database size which i think shows how much data we could potentially reclaim by doing a full vacume. however please be warned at this point most of what i know of postgresql i learnt over the last two days :). See below for full results but i think the following are worth pointing out

• idx_factsets_jsonb_merged: from the name this sounds like its and index and i suspect it is not very good for performance if the index cant fit in ram (psql only has 8G effective_cache_size)
• catalog_resources and edges. both of theses have a large discrepancy between the on-disk size and the db size. if i read this right it means we could reclaim ~9G by doing a full vacum on these two tables
  • resource_params, fact_paths and resource_events also seem to have similar discrepancies

SELECT nspname || '.' || relname AS "relation",
      pg_size_pretty(pg_relation_size(C.oid)) AS "disk size", 
      pg_size_pretty( pg_total_relation_size(nspname || '.' || relname)) AS "size" 
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
   WHERE nspname IN ('public')
    ORDER BY pg_relation_size(C.oid) DESC;

I'm optimistically pulling o11y since AFAICS there's no actionabled