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