Hi Savu,
Try with this query:
SELECT *,
Pg_size_pretty(total_bytes) AS total,
Pg_size_pretty(index_bytes) AS INDEX,
Pg_size_pretty(toast_bytes) AS toast,
Pg_size_pretty(table_bytes) AS TABLE
FROM (SELECT *,
total_bytes - index_bytes - Coalesce(toast_bytes, 0) AS
table_bytes
FROM (SELECT c.oid,
nspname AS table_schema,
relname AS TABLE_NAME,
c.reltuples AS row_estimate,
Pg_total_relation_size(c.oid) AS total_bytes,
Pg_indexes_size(c.oid) AS index_bytes,
Pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE relkind = 'r') a
WHERE table_schema = 'public' ORDER BY total_bytes DESC) a;
It will show your total bytes for each table, including TOAST data.
TOAST means The Oversized-Attribute Storage Technique. This is your “extra” size in disk. You will see which tables are bigger and how much space you can free with the VACUUM operation.
Not always you can recover all the space. The only way to full recover the disk space is to “dump” your database and “restore” it so that all the rows are inserted and all the free space is used. If the disk space is the same after restoring from a dump/backup then you can’t do anything. The space in database is real.
Best regards.