如何估算Postgres表中一列的大小？

Question

在Postgres 9.1的表中有一个text类型的列，我想知道仅该列对所需磁盘空间的影响。它不需要很精确，但我想大致了解该列是否占数据库消耗的磁盘空间的20％/30％/ ...。

我知道有pg_relation_size，但它仅在表级别操作。

我有许多具有相同模式的数据库。我转储了一个较小的数据库，并使用grep和cut剪切了该列，然后比较了纯文本转储的大小。但这并不一定是现场数据库空间要求的良好指标，而且对于大型数据库来说也更难做到。

- Thomas Kappler

Answer 1

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;

- Clodoaldo Neto

---

pg_relation_size返回基础表的大小，但不包括toast表。当某人关心每个列的磁盘使用情况时，很可能涉及到toast表，因此最好使用pg_table_size。 - jjanes

如果您想要计算toast表以及索引的大小，您可以使用pg_total_relation_size。 - Jason Champion

当表非常大时，请注意此操作似乎会对整个表执行一个完整的“Seq Scan”扫描。 - Linh Dam

Answer 2

对已接受答案的稍微改进：美化输出大小，并使用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;

- Denis Vermylen

Answer 3

如果您想要按大小排序的方式获取数据库中所有列的报告，则可以按照以下方法进行操作。

BEGIN;
CREATE FUNCTION tc_column_size(table_name text, column_name text)
    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, column_name) size
    FROM
        information_schema.columns
    WHERE
        table_schema = 'public') AS z
WHERE
    size IS NOT NULL
    -- and z.table_name = 'my_table' -- <--- uncomment to filter a table
ORDER BY
    z.size DESC;

ROLLBACK; -- <--- You may not want to keep that function

- Stan Sokolov

---

在第7行中，还应引用table_name：EXECUTE 'select sum(pg_column_size(t."' || column_name || '")) from "' || table_name || '" t ' into response; - Molomby

在内部查询中过滤表格（即将注释移到模式的过滤器）会更快。 - undefined