According to
SELECT pg_database_size('mydb') my database is about 15TBytes. According to SELECT spcname FROM pg_tablespace I have a total of 5 tablespaces (pg_global, pg_default and 3 others I created).
If I use SELECT pg_tablespace_size ('????') on each tablespace I find I have about 25Mbytes in the two system tablespaces, and less than 10TBytes in the other 3 tablespaces.
What accounts for the 5TByte discrepancy?
Some similar thread suggests that this may be 'toast' space. But this query...
SELECT oid::regclass,
reltoastrelid::regclass,
pg_relation_size(reltoastrelid) AS toast_size
FROM pg_class
WHERE relkind = 'r'
AND reltoastrelid 0
ORDER BY 3 DESC;
Indicates no more than a few MBytes of toast
The database is mostly used as a data-warehouse, so there's very little update/deletion of data once it's been inserted, but I've tried doing a VACUUM FULL on some of the larger tables, but using this query...
SELECT (pg_total_relation_size(c.oid) + pg_indexes_size(c.oid) ) as tablePlusIdx
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
AND relname = 'mytable'
before and after the VACUUM FULL indicates a space saving of only a few percent.
What else might account for the difference between pg_database_size() and the sum size of all the individual partitions? Is there any data stored outside of the tablespaces?
I would prefer NOT to have to try a VACUUM FULL on all the tables, firstly because it doesn't seem to make any difference (not on the tables I've tried anyhow) and because the DB is so large even a single VACUUM FULL can take several hours.
Asked by ConanTheGerbil
(1303 rep)
Dec 3, 2024, 11:30 AM