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

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).

Categories

System function

See also

pg_tablespace_size(), pg_relation_size(), pg_column_size(), pg_table_size(), pg_indexes_size()