table size, database size

Andreas 'ads' Scherbaum

I have seen this question more than once in the past: "How much storage space do i need for my table?"
PostgreSQL can give you this information.

The storage space (in bytes) for one specific table:

ads=# select pg_relation_size('size_test');
pg_relation_size
------------------
5668864
(1 row)

Same query, but with the result in a human-readable format:

ads=# select pg_size_pretty(pg_relation_size('size_test'));
pg_size_pretty
----------------
5536 kB(1 row)

Remember that this is only the size of the table, not included an index or additional stuff.
The size including any index can be found out with:

ads=# select pg_size_pretty(pg_total_relation_size('size_test'));
pg_size_pretty
----------------
7656 kB
(1 row)


The size of a complete database:

ads=# select pg_size_pretty(pg_database_size('ads'));
pg_size_pretty
----------------
11 MB
(1 row)


You can even find out, how much space a specific value needs:

ads=# select pg_column_size(5::smallint);
pg_column_size
----------------
2
(1 row)

ads=# select pg_column_size(5::int);
pg_column_size
----------------
4
(1 row)

ads=# select pg_column_size(5::bigint);
pg_column_size
----------------
8
(1 row)

ads=# select pg_column_size('This is a string'::varchar);
pg_column_size
----------------
20
(1 row)

ads=# select length('This is a string'::varchar);
length
--------
16
(1 row)

The 4 byte difference between the column size and the string length is the storage overhead needed for varchar columns.

The size for a tablespace can be found out with pg_tablespace_size(), but i don't have a machine with configured tablespace at hand to show an example.