Which are my biggest tables?
We've looked at getting the size of a specific table, so now it's time to widen the problem to related areas. Rather than having an absolute value for a specific table, let's look at the relative sizes.
How to do it...
The following basic query will tell us the 10 biggest tables:
SELECT quote_ident(table_schema)||'.'||quote_ident(table_name) as name ,pg_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name)) as size FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog') ORDER BY size DESC LIMIT 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
, as we did in the How many tables are in the database? recipe.
How it works…
PostgreSQL provides a dedicated function, pg_relation_size
, to compute the actual disk space used by a specific table or index. We just need to provide the table name. In addition to the main data files, there are other files (called forks) that can be measured by specifying an optional second argument. These include the Visibility Map (VM), the Free Space Map (FSM), and the initialization fork for unlogged objects.