SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
This will return the 20 largets tables in your database. This will take a long time to run please be patient
SELECT relname AS "relation", pg_size_pretty (pg_total_relation_size (C .oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace) WHERE nspname NOT IN ('pg_catalog','information_schema')
3. If there is no enough disk space on the db server to run a full VACUUM (need space for temp tables), there may be two options to work around this:
1. Backup and Restore the database. Restoring the databases, tables, indexes will free up space and defragment
2. Point the temp tables to a different disk, for eg:
CREATE TABLESPACE tempspace LOCATION '/path/to/new/folder';
Reference: https://dba.stackexchange.com/questions/17057/i-need-to-run-vacuum-full-with-no-available-disk-space