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...