Hi all,
I'm currently using PostgreSQL 9.2 and my DB size is 2.2 TB..
Running the query below;
WITH schema_size AS (
SELECT
tab.table_catalog AS database_name,
tab.table_schema AS schema_name,
tab.table_name,
pg_total_relation_size(table_schema || '.' || tab.table_name) AS table_size_total,
pg_relation_size(table_schema || '.' || tab.table_name) AS table_size
FROM information_schema.tables tab
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
), pretty_size AS (
SELECT
database_name,
schema_name,
pg_database_size(database_name) AS database_size_bigint,
pg_size_pretty(pg_database_size(database_name)) AS database_size,
sum(table_size_total) AS schema_size_bigint_total,
pg_size_pretty(sum(table_size_total)) AS schema_size_total,
sum(table_size) AS schema_size_bigint,
pg_size_pretty(sum(table_size)) AS schema_size
FROM schema_size
GROUP BY database_name, schema_name
)
SELECT
database_name,
schema_name,
database_size,
schema_size_total,
schema_size,
((schema_size_bigint_total * 100) / database_size_bigint) AS perc_total,
((schema_size_bigint * 100) / database_size_bigint) AS perc
FROM pretty_size
I get the following data:
schema | schema_size_total | schema_size | perc_total | perc
gorfs | 1824 GB | 20 GB | 85.4308477608319514 | 0.94562882033477939710
As you can see, the GORFS schema is 1.8 TB.
That is all pg_Toast tables....
I read the documentation [1] but actually I couldn't understand:
Is there any way to get the size decreased ?
The DB is in production, so I can't run the VACCUM FULL.
Is there anything I can do?
Is the pg_toast normal?
Thanks
Lucas