sqlpostgresqlpostgresql-9.3

postgresql list and order tables by size


How can I list all the tables of a PostgreSQL database and order them by size?


Solution

  • This shows you the size of all tables in the schema public:

    select
      table_name,
      pg_size_pretty(pg_total_relation_size(quote_ident(table_name))),
      pg_total_relation_size(quote_ident(table_name))
    from information_schema.tables
    where table_schema = 'public'
    order by 3 desc;
    

    If you have multiple schemas, you might want to use:

    select 
      table_schema, 
      table_name,
      pg_relation_size('"'||table_schema||'"."'||table_name||'"')
    from information_schema.tables
    order by 3
    

    SQLFiddle example: http://sqlfiddle.com/#!15/13157/3

    List of all object size functions in the manual.