Thomas Damgaard's Blog

See the size of PostgreSQL databases

10 June 2022 - Thomas Damgaard

PostgreSQL has a nice built-in function pg_database_size() to get the size of a database.

This is how to use it:

SELECT pg_database_size('datname');

Where datname is the name of the database you want to know the size of.

Examples of use

In order to know the database name, use \l to list database. Result:

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 analytics | webinbox | UTF8     | en_DK.UTF-8 | en_DK.UTF-8 |
 appdev    | postgres | UTF8     | en_DK.UTF-8 | en_DK.UTF-8 |
 postgres  | postgres | UTF8     | en_DK.UTF-8 | en_DK.UTF-8 |
 sales     | postgres | UTF8     | en_DK.UTF-8 | en_DK.UTF-8 |
 template0 | postgres | UTF8     | en_DK.UTF-8 | en_DK.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_DK.UTF-8 | en_DK.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(6 rows)

postgres=#

Let’s look at sales:

postgres=# SELECT pg_database_size('sales');
 pg_database_size 
------------------
        749683203
(1 row)

postgres=# 

This is the size in kb. Typically, I want to have human readable sizes. Luckily, PostgreSQL has a nice built-in function for that as well called pg_size_pretty().

Here is how to use it:

postgres=# SELECT pg_size_pretty(pg_database_size('pastebin'));
 pg_size_pretty
----------------
 715 MB
(1 row)

postgres=#

If you want to see the sizes of all databases, this can be done with:

postgres=# SELECT datname, pg_database_size(datname) FROM pg_database;
    datname    | pg_database_size
---------------+------------------
 sales         |          8172191
 developers    |          8630943
 payroll       |         11145887
 geolocation   |        262247071
 template0     |          7918239
 weatherlogger |          8082079
 template1     |          8032927
 benchmark     |        171807391
 support       |          8778399
 webinbox      |          8647327
 inventory     |       1293734559
 postgres      |          8032927
(12 rows)

postgres=#

Or with human readable sizes:

postgres=#     select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
    datname    | pg_size_pretty
---------------+----------------
 sales         | 7981 kB
 developers    | 8429 kB
 payroll       | 11 MB
 geolocation   | 250 MB
 template0     | 7733 kB
 weatherlogger | 7893 kB
 template1     | 7845 kB
 benchmark     | 164 MB
 support       | 8573 kB
 webinbox      | 8445 kB
 inventory     | 1234 MB
 postgres      | 7845 kB
(12 rows)
Filed under: postgresql, tips