Getting Database and Table Sizes in Postgres

Total Database Size

This SQL simply gets the total size of the database in a human readable format.

SELECT pg_size_pretty(pg_database_size('postgres')) as db_size

List all Tables

This lists all the tables in the database public schema.

SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public'

Search Schema for Column Name

I often need to search all the tables or views to find which contain a particular column. Replace ‘COLUMN_NAME’ with your column below.

SELECT t.table_schema,t.table_name
FROM information_schema.tables t
INNER JOIN information_schema.columns c 
      ON c.table_name = t.table_name 
      AND c.table_schema = t.table_schema 
WHERE c.column_name = 'COLUMN_NAME'
      AND t.table_schema not in ('information_schema', 'pg_catalog')
      AND t.table_type = 'BASE TABLE'
ORDER BY t.table_schema;

In this case I searched for all columns containing the word ‘order’.

Table Sizes

Retrieve the size per table in the public schema from largest to smallest.

SELECT nspname || '.' || relname AS "table_name",
        pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
     WHERE nspname = 'public'
     AND C.relkind <> 'i'
     AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC

Full Schema

SELECT * FROM information_schema.columns WHERE table_schema = 'public'

Leave a comment

Your email address will not be published. Required fields are marked *