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
PostGIS in Action
About the Authors
Consulting
