PostgreSQL Database Size Stats
A while ago, I saw this post on Paul’s blog, about a query to get database size. I looked into it, but I was pretty unhappy with the query, as pg_database_size appears to include the size of pg_catalog.
The actual query in question is this:
SELECT
pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname))
AS size FROM pg_database;
The query I came up with, has a much more accurate usage of what a user can actually control, the downside being you have to connect to each database to get usage:
SELECT
CURRENT_DATABASE(),
pg_size_pretty(sum(pg_total_relation_size(tablename))::int8)
AS size
FROM pg_tables
WHERE schemaname != 'pg_catalog'
AND schemaname != 'information_schema';
For a better breakdown of which tables are hogging disk space, I came up with this:
SELECT
tablename,
pg_size_pretty(pg_total_relation_size(tablename)) AS total_usage,
pg_size_pretty((pg_total_relation_size(tablename)
- pg_relation_size(tablename))) AS external_table_usage
FROM pg_tables
WHERE schemaname != 'pg_catalog'
AND schemaname != 'information_schema'
ORDER BY pg_total_relation_size(tablename);
The external_table_usage column shows how much space is being used for things such as indexes, very useful if you’re looking to free up some space by dropping unneeded indexes.