pg_database_size()
A function returning the size of a database
pg_database_size() is a system function returning the total size of a specified database.
pg_database_size() was added in PostgreSQL 8.1.
Usage
pg_database_size (name) →bigint
pg_database_size (oid) →bigint
pg_database_size() can be executed by any user with the CONNECT privilege for the database, or (from PostgreSQL 10) any user who is member of the pg_read_all_stats role.
Note that pg_database_size() calculates the actual on-disk usage of the database's data directory and any associated tablespaces. This will probably result in a higher size reading than calculating the sum of all objects in that database.
Change history
- PostgreSQL 8.1
- added (commit 358a897f)
Examples
Basic execution example for pg_database_size():
postgres=# SELECT pg_database_size('proddb');
pg_database_size
------------------
6309618543
(1 row)
Attempting to determine the size of a non-existent database:
postgres=# SELECT pg_database_size('foo');
ERROR: database "foo" does not exist
Attempting to determine the size of a non-existent database by OID:
postgres=# SELECT pg_database_size(99::oid);
pg_database_size
------------------
(1 row)
(NULL was returned).
References
- PostgreSQL documentation: Database Object Management Functions
Useful links
- How big is my database and my other stuff - November 2011 blog article by Postgres OnLine Journal