How To Find The Size Of A Schema Or Table (Including Toast Tables)

Today I’ve been struggling with Postgres, trying to find out which table(s) is taking up all the hard drive space. My approach was to see which is the largest schema(s), then see which is the largest table (including toast table(s)) and work from there.

How To Find The Size Of A Schema In Postgres

The following postgres script shows the size of each schema in a Postgres database:

SELECT schema_name,
pg_size_pretty(sum(table_size)::bigint),
(sum(table_size) / pg_database_size(current_database())) * 100
FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name,
pg_relation_size(pg_catalog.pg_class.oid) as table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name

This gives you a good overview of what’s going on.

How To Find The Size Of All Tables

The next script shows the size of all tables, but excludes system and Toast tables:

SELECT nspname || ‘.’ || relname AS “relation”,
pg_size_pretty(pg_total_relation_size(C.oid)) AS “total_size”
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN (‘pg_catalog’, ‘information_schema’)
AND C.relkind <> ‘i’
AND nspname !~ ‘^pg_toast’
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 150;

If your size problem resides in one of these tables, you can fix that. However, if it doesn’t, it may be in a Toast table.

How To Find The Size Of A Toast Table

The Toast table is like an overflow table for data that doesn’t fit into other tables allocated space. There is actually a Toast table for each table that requires a Toast table. Here’s how to find the size of all Toast tables in Postgres:

SELECT nspname || ‘.’ || relname AS “relation”,
pg_size_pretty(pg_total_relation_size(C.oid)) AS “total_size”,
C.oid
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN (‘pg_catalog’, ‘information_schema’)
AND C.relkind <> ‘i’
AND nspname ~ ‘^pg_toast’
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 150;

How To Find Out What Table A Toast Table Corresponds To

Once you’ve found the size of the Toast table, you may want to see what table that Toast table corresponds to. To do this, use the value in the Oid column from the script above, to find the name of the corresponding table via this script:

select oid::regclass from pg_class where reltoastrelid=’1273510250′::regclass;

You’ll then need to do something with the data to fix the size problem (delete some of it, truncate it, delete the table, etc.), then run a FULL Vacuum to release the space from the disk. Don’t forget that you’ll need at least the same amount of disk space as the table that you’re vacuuming so the new table can be written to disk before the old one is cleaned up (though, if you truncated the data, that’ll be ~0kb).