May 2018
Intermediate to advanced
576 pages
30h 25m
English
The following basic query will tell us the 10 biggest tables:
SELECT table_name,pg_relation_size(table_schema || '.' || table_name) as sizeFROM information_schema.tablesWHERE table_schema NOT IN ('information_schema', 'pg_catalog')ORDER BY size DESCLIMIT 10;
The tables are shown in descending order of size, with at the most 10 rows displayed. In this case, we look at all the tables in all the schemas, apart from the tables in information_schema or pg_catalog, like we did in the How many tables are in the database? recipe.