Question 261046 in PostgreSQL Question posted in
The official documentation can be found here.

How to get PostgreSQL tables total disk size under a schema

Our PostgreSQL database was getting bigger and we wanted to identify what tables are guilty.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Solution:

    SELECT table_name, pg_size_pretty(pg_total_relation_size(table_name)),pg_total_relation_size(table_name), table_schema
FROM information_schema.tables t
where table_schema='main'
  and table_type='BASE TABLE';

    Output sample:

    table1,25 GB

    table2,14 GB

    table3,11 GB


  2. 0

    You can use :

    SELECT 
    schemaname || '.' || tablename AS full_table_name,
    pg_size_pretty(total_bytes) AS total_size
FROM (
    SELECT 
        schemaname,
        tablename,
        pg_total_relation_size(schemaname || '.' || tablename) AS total_bytes
    FROM pg_tables
    WHERE schemaname = 'Name of Schema you want'
) AS table_sizes
ORDER BY total_bytes DESC;

    You can modify the where clause to get the disk size of the tables under the schema.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search