There is a column of type text
in a table in Postgres 9.1. I'd like to know the impact of just that column on the disk space needed. It doesn't need to be precise, but I'd like to get an idea if that column is responsible for 20%/30%/... of the disk space consumed by the database.
I know pg_relation_size
, but it only operates at table level.
I have many databases with this same schema. I dumped a smaller one and cut out the column with grep and cut and compared the size of the plain text dumps. But this is not necessarily a good indicator of space requirements in the live db, and it's also more difficult to do that for large databases.
select
sum(pg_column_size(the_text_column)) as total_size,
avg(pg_column_size(the_text_column)) as average_size,
sum(pg_column_size(the_text_column)) * 100.0 / pg_total_relation_size('t') as percentage
from t;
See official documentation of pg_total_relation_size
at:
https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT
Interesting related question:
What's the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)