In this day and age of clouds and what not, it is still important to keep tabs on your database growth to ensure it doesn't outgrow your disks or so that you can be prepared when it does. A common question that is asked is how big your database is or all your databases combined. Luckily PostgreSQL has had for a long time functions to help you answer this easily. In this little article, I'll demonstrate how to answer these questions and more with some PostgreSQL built-in functions

How big is my database?

The pg_database_size() is a function that takes the name of a database in the database cluster and returns the size in bytes. Trying to decipher bytes when most databases are in the size of megabytes, gigabytes, or even terabytes can get pretty ugly. Luckily there is a pretty function called pg_size_pretty() which makes these numbers more human readable. In additon there is a current_database() function which tells us which database we are currently connected to. Combine them all and you have your answer

SELECT pg_size_pretty( pg_database_size( current_database() ) ) As human_size
    , pg_database_size( current_database() ) As raw_size;

human_size |   raw_size
------------+--------------
 181 GB     | 193841573064

How big are my databases?

In this example we sum up the sizes of all databases in the current PostgreSQL instance. Note: we have to cast the size to bigint since the SUM function returns a numeric when summing bigints and pg_pretty_size expects a bigint.

SELECT pg_size_pretty( SUM(pg_database_size(datname))::bigint ) As human_size
    ,  SUM( pg_database_size(datname) )::bigint   As raw_size
    , pg_size_pretty( (SUM(pg_database_size(datname) ) - pg_database_size(current_database() ) )::bigint ) aS h_without_current
    FROM pg_database;

In this case the raw_size is a bit useful since it lets us know we've got more than this database in our cluster. Since my current database dwarfs the size of the others, I also would like to know how big things are without it.

human_size |   raw_size   | without_current
-----------+--------------+-----------------
181 GB     | 194080676140 | 228 MB

Size family of functions

Querying the PostgreSQL catalog, here are all the size function gems I've found. I'm especially fond of pg_column_size which tells you how much space an object would take up if it were stored in a table field.
For example: SELECT pg_column_size('LINESTRING(1 2,3 4, 5 6)'::geometry);

       funcname        |                                       description
------------------------+------------------------------------------------------------------------------------------
 pg_column_size         | bytes required to store the value, perhaps with compression
 pg_database_size       | total disk space usage for the specified database
 pg_indexes_size        | disk space usage for all indexes attached to the specified table
 pg_relation_size       | disk space usage for the main fork of the specified table or index
 pg_table_size          | disk space usage for the specified table, 
                          including TOAST, free space and visibility map
 pg_tablespace_size     | total disk space usage for the specified tablespace
 pg_total_relation_size | total disk space usage for the specified table and associated indexes