How to estimate the size of one column in a Postgres table?

Question

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.

Romain Vincent · Accepted Answer · 2024-07-28 16:45:23Z

102

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)

edited Jul 28, 2024 at 16:45

Romain Vincent

3,8703 gold badges26 silver badges33 bronze badges

answered Aug 19, 2013 at 15:35

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jjanes Over a year ago

pg_relation_size returns the size of the base table without including the toast table. When someone cares about disk usage of each column, it is likely that a toast table is involved, so you probably want to use pg_table_size instead.

Jason Champion Over a year ago

You can also use pg_total_relation_size if you want to count the toast table plus the size of the indexes.

Linh Dam Over a year ago

This seems to do a full Seq Scan on the table so be careful when the table is very large.

Denis Vermylen · Accepted Answer · 2019-02-14 09:36:49Z

56

Slight improvement on the accepted answer: pretty print the size and use pg_total_relation_size to be more accurate.

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;

answered Feb 14, 2019 at 9:36

Denis Vermylen

6615 silver badges3 bronze badges

Comments

Ulysse BN · Accepted Answer · 2021-12-16 13:57:06Z

21

If you want a report for all the columns in a database sorted by size then here is the way

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

edited Dec 16, 2021 at 13:57

Ulysse BN

11.7k7 gold badges64 silver badges95 bronze badges

answered Mar 20, 2020 at 20:15

Stan Sokolov

2,3101 gold badge25 silver badges24 bronze badges

2 Comments

Molomby Over a year ago

Should also quote the table_name on line 7: EXECUTE 'select sum(pg_column_size(t."' || column_name || '")) from "' || table_name || '" t ' into response;

yeger Over a year ago

It is much faster to filter the table in the inner query (i.e. move the comment to the schema's filter)

Collectives™ on Stack Overflow

How to estimate the size of one column in a Postgres table?

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related