Page Comparison

...

• Once you know which Schema is the issue you can get a list of tables with their corresponding sizes by running this SQL against the schema. Change the value of where table_schema = to the name of your schema. The example here is for the audit schema

  Code Block
  select table_name, pg_relation_size(quote_ident(table_name)) from information_schema.tables where table_schema = 'audit' order by pg_relation_size DESC

The tables for this schema will be listed order of size, from largest to smallest. This should allow you to focus your efforts in the right place.

...

There have been cases where this hasn’t worked and this is an alternative that will list tables and which schema they belong to in size order.

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
  table_size

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

...