PostgreSQL table disk space
Here are a couple of handy queries to figure out the amount of space a table is using and how much of it is wasted space.
select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
See how much space your tables are taking.
select
relname,
pg_size_pretty(pg_total_relation_size(oid))
from
pg_class
where
relkind = 'r'
order by pg_total_relation_size(oid) desc;
relname | pg_size_pretty
-----------------------------------------+----------------
file_instance | 1729 GB
raw_message_cortex | 399 GB
as_timeline_1 | 80 GB
See how much is wasted space.
select
relname,
reltuples,
relpages,
reltuples / relpages as "T / P"
from
pg_class
where
relkind = 'r'
and relpages > 0
order by reltuples / relpages;
relname | reltuples | relpages | T / P
---------------------------------------+-------------+----------+--------------------
session_collection_source | 12 | 207 | 0.0579710144927536
sessions | 2 | 2 | 1
pg_ts_parser | 1 | 1 | 1
pg_db_role_setting | 2 | 1 | 2
pg_tablespace | 2 | 1 | 2
pg_extension | 2 | 1 | 2
pg_auth_members | 2 | 1 | 2
family_exclusion | 2 | 1 | 2
q_file_view | 148681 | 69478 | 2.13997236535306
file_instance | 9.4325e+06 | 4339863 | 2.17345570585984
