Table size in PostgreSQL

Disk space seems endless, until you run out and/or have to pay the bill. The question is how to find tables with a high disk storage usage and with the query below it shows the table and index size, but also the size of TOAST data for PostgreSQL.

SELECT schemaname, tablename,
    pg_size_pretty(tsize) AS size_table,
    pg_size_pretty(size) AS size_index,
    pg_size_pretty(total_size) AS size_total
FROM (SELECT *,
        pg_table_size(schemaname||'.'||tablename) AS tsize,
        pg_relation_size(schemaname||'.'||tablename) AS size,
        pg_total_relation_size(schemaname||'.'||tablename) AS total_size
    FROM pg_tables) AS TABLES
WHERE schemaname='public'
ORDER BY total_size DESC;

After running this query on the development schema and exporting the results to CSV, we can see that a ManyToMany table consumes a total of 39 MB. With over 330.000 entries this seems numbers seem to be fine as the table size is in line with the amount of data stored in it.

schemaname,tablename,size_table,size_index,size_total
public,domain_asset_domain_asset_group,12 MB,12 MB,39 MB
public,domain_account_domain_function,2960 kB,2936 kB,9720 kB
public,domain_account,1760 kB,1728 kB,4088 kB
public,domain_command,2016 kB,1992 kB,3528 kB
public,person,832 kB,792 kB,1736 kB
public,domain_command_collection,712 kB,688 kB,1248 kB
public,domain_asset_group,648 kB,624 kB,1160 kB
public,domain_asset,544 kB,520 kB,1088 kB
public,domain_function,440 kB,416 kB,784 kB
public,sessions,64 kB,32 kB,80 kB
public,asset_application,8192 bytes,8192 bytes,56 kB
public,domain_authority,8192 bytes,8192 bytes,40 kB
public,asset_function,8192 bytes,0 bytes,24 kB

Collecting this data and graphing it may help spot problems and predict storage needs. It may help DevOps teams to figure out if their databases are growing and with what speed.

Increasing Inotify Watches Limit   Upgrading from CentOS 7.3 to 7.4