We've looked at how to get the size of a specific table, so now it's time to widen the problem to related areas. Rather than an absolute value for a specific table, let's look at the relative sizes.
The following basic query will tell us the "Top 10 Biggest Tables":
SELECT table_name ,pg_relation_size(table_name) as size FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog') ORDER BY size DESC LIMIT 10;
Tables are shown in descending order of size, with at most 10 rows displayed. In this case, we look at all tables in all schemas, apart from tables in the
information_schema or in
pg_catalog. These latter two schemas are the locations where Postgres keeps its own ...