• Find PostgreSQL database size

    PostgreSQL is an object-relational database management system (ORDBMS). It’s a free and open-source tool available under BSD licence.

    Thanks to some built-in functions, you can get several information regarding database and/or table size. You will find below the main and most used examples.

    Database size (in octal)

    dbpg-# SELECT pg_database_size(‘db_1’);
    (1 line)

    Database size (in human understandable format)

    dbpg-# SELECT pg_size_pretty(pg_database_size(‘db_1’));
    327 MB
    (1 line)

    All databases size

    dbpg-# SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
    datname  | size
    postgres | 3537 kB
    db_1     | 327 MB
    db_2     | 4732 kB
    db_3     | 29 MB
    db_4     | 168 MB
    temp0    | 3928 kB
    (6 lines)

    Table size (excluding indexes and some auxiliary data)

    dbpg-# SELECT pg_size_pretty(pg_relation_size(‘users’));
    64 kB
    (1 line)

    Table size (including all data)

    dbpg-# SELECT pg_size_pretty(pg_total_relation_size(‘users’));
    152 kB
    (1 line)

    Find the largest table in database

    dbpg-# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
    relname | relpages
    db_1    | 8732
    (1 line)

     For the full documentation, please visit http://www.postgresql.org/