PostgreSQL Optimization – Managing Schema Size with PG TOAST

database-sizeoptimizationpostgresqlsize;

I've got the following query to measure the schema's size on my DB:

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;

However, when running it I get this information:

enter image description here

My DB is 2 TB and that schema is 1.7 TB.

Question:

Is that related to the toast tables? How can I decrease the size of it?

Best Answer

The function pg_total_relation_size() returns

Total disk space used by the specified table, including all indexes and TOAST data

Given the disproportionate size between the tables and total relation size (100 to 1), it is probably mainly due to TOAST data. TOAST data is already compressed (LZ), so it will be unlikely to be able to compress it any further.

If you have a lot of duplication, or want to store blobs outside the database, you could resort to this: Storing blobs outside the database.