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

References:

http://www.postgresql.org/docs/current/static/functions-admin.html
http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples/

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s