Getting sizes of tables in a Postgres database

When disk space is getting tight, it’s nice to be able to figure out what tables are the culprits. Below is how to get a size-ordered list from PostgreSQL:

webapp=# SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS size FROM pg_class WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public') ORDER BY pg_total_relation_size(oid);

This will return something like this:

index_alert_history_archive_on_launch_id_and_entity_id | 1101 MB
index_alert_history_on_entity_id_and_code | 1328 MB
index_alert_history_archive_on_entity_id_and_code | 1374 MB
alert_history | 7959 MB
alert_history_archive | 10168 MB



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s