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, as in the following example:
name | size
-------------------------+----------
public.pgbench_accounts | 13434880
public.pgbench_branches | 8192
public.pgbench_tellers | 8192
public...