postgresql 如何估计 Postgres 表中一列的大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18316893/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to estimate the size of one column in a Postgres table?
提问by Thomas Kappler
There is a column of type textin 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.
textPostgres 9.1 的表中有一个类型的列。我想知道仅该列对所需磁盘空间的影响。它不需要精确，但我想知道该列是否负责数据库消耗的磁盘空间的 20%/30%/...。
I know pg_relation_size, but it only operates at table level.
我知道pg_relation_size，但它仅在表级别运行。
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.
我有许多具有相同架构的数据库。我转储了一个较小的并用 grep 和 cut 剪掉了列，并比较了纯文本转储的大小。但这不一定是实时数据库中空间需求的一个很好的指标，而且对于大型数据库来说，这样做也更加困难。
回答by Clodoaldo Neto
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_relation_size('t') as percentage
from t;
回答by Denis Vermylen
Slight improvement on the accepted answer: pretty print the size and use pg_total_relation_size to be more accurate.
对接受的答案略有改进：漂亮地打印大小并使用 pg_total_relation_size 更准确。
select
pg_size_pretty(sum(pg_column_size(column_name))) as total_size,
pg_size_pretty(avg(pg_column_size(column_name))) as average_size,
sum(pg_column_size(column_name)) * 100.0 / pg_total_relation_size('table_name') as percentage
from table_name;
回答by Stan Sokolov
If you want a report for all the columns in a database sorted by size then here is the way
如果您想要按大小排序的数据库中所有列的报告，那么这里是方法
DROP FUNCTION IF EXISTS tc_column_size;
CREATE FUNCTION tc_column_size(table_name varchar(255), column_name varchar(255))
RETURNS BIGINT AS
$$
declare response BIGINT;
BEGIN
EXECUTE 'select sum(pg_column_size(t."' || column_name || '")) from ' || table_name || ' t ' into response;
return response;
END;
$$
LANGUAGE plpgsql;
select z.table_name, z.column_name, pg_size_pretty(z.size) from(
select table_name, column_name, tc_column_size(table_name::varchar,
column_name::varchar) size
from information_schema.columns where table_schema='public') as z
where size is not null
order by z.size desc;
