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.