Find which schemas/tables are taking up excessive disk space

Owned by Gavin Kelly, created
Last updated: 16 Dec, 2022
3 min read

 

Overview

If space is low or has run out on the drive with the Postgres database on it and you need to locate which schemas/tables need reducing in size, you can use these queries to help.

Step-by-step guide

  • Find which database is the issue. Run TreeSizeFree-Portable.exe as an administrator. It should be under D:\Astun\Tools\TreeSizeFree-Portable if not there ask Astun Technology and we can supply it.

     

  • Scan the drive with the PGDATA folder on it and locate the PGDATA\base folder. Note any exceptionally large folders.



  • If for example folder 17331 is unusually large, find the corresponding database using this SQL in PGAdmin

    select oid as database_id, datname as database_name, datallowconn as allow_connect, datconnlimit as connection_limit from pg_database order by oid;

  • Select the database and use the following SQL to list the schemas along with their sizes.

    SELECT schema_name, pg_size_pretty(sum(table_size)::bigint), (sum(table_size) / pg_database_size(current_database())) * 100 FROM ( SELECT pg_catalog.pg_namespace.nspname as schema_name, 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 GROUP BY schema_name ORDER BY schema_name

  • 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

    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.