How to Find the Size of a PostgreSQL Database

As a PostgreSQL database administrator, it‘s important to keep track of your database sizes over time. A sudden increase in database size can indicate issues like runaway queries or spikes in application usage. Monitoring size also helps plan for storage capacity upgrades.

Fortunately, PostgreSQL provides several convenient ways to find the sizes of your databases. In this comprehensive guide, we‘ll explore five methods:

1. The pg_database_size() function
2. The pg_size_pretty() function
3. Querying the pg_database system catalog
4. The pgAdmin GUI
5. The psql command line

The pg_database_size() Function

The simplest way to get a database‘s size is with the pg_database_size() function:

SELECT pg_database_size(‘mydb‘);

This returns the size in bytes as a bigint (64 bit integer). For example:

pg_database_size
--------------------
 2146435072
(1 row)

That‘s not very readable, so we can use some mathematical operations to transform the size into more understandable units:

SELECT pg_size_pretty(pg_database_size(‘mydb‘));

Now we get a friendlier output:

pg_size_pretty
--------------
2 GB
(1 row)

The pg_size_pretty() function formatted the size into gigabytes.

Alternatively, you can manually convert to megabytes:

SELECT pg_database_size(‘mydb‘) / 1024 / 1024 AS size_mb;

Which would display:

size_mb
--------
    2048
(1 row) 

So this database is around 2 GB or 2048 MB in size.

The pg_database_size() function is useful when you want a quick and simple way to check a database‘s current storage usage.

The pg_size_pretty() Function

As we saw before, pg_size_pretty() takes a bigint representing bytes and formats it into an easier to read value with units like KB, MB, GB etc.

We can use it directly on pg_database_size():

SELECT pg_size_pretty(pg_database_size(‘mydb‘));

Or we can use it in custom queries for more flexibility in the output:

SELECT 
   datname AS "Database",
   pg_size_pretty(pg_database_size(datname)) AS "Size"
FROM pg_database;

This will display every database on the PostgreSQL server and format their sizes nicely:

   Database   | Size 
--------------+------
 postgres     | 7659 MB
 template1    | 7659 MB
 template0    | 7659 MB  
 mydb         | 2048 MB
(4 rows)

In addition to prettifying database sizes, pg_size_pretty() can also format table sizes, index sizes, and any other byte values.

Querying the pg_database System Catalog

The pg_database system catalog contains one row for every database in the PostgreSQL cluster. Querying it returns useful info like the database owner, default tablespace, and of course size.

Here is a query to find all database sizes:

SELECT 
   datname AS "Database",
   pg_size_pretty(pg_database_size(datname)) AS "Size",
   datallowconn AS "Allow Connections"   
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

This additionally shows if each database allows connections, sorted descending by size:

   Database   |   Size    | Allow Connections
--------------+-----------+-------------------
 template0    | 7659 MB   | f
 template1    | 7659 MB   | t 
 postgres     | 7446 MB   | t
 mydb         | 2048 MB   | t
(4 rows)

We can also search for a specific database:

SELECT * 
FROM pg_database
WHERE datname=‘mydb‘;

The output contains useful configuration info:

-[ RECORD 1 ]----------+--------------------------
datname                | mydb
datdba                 | 10
encoding               | 6
datistemplate          | f
datallowconn           | t 
datconnlimit           | -1
datlastsysoid          | 13976
datfrozenxid           | 819
datminmxid             | 1
dattablespace          | 1663
datacl                 | {=c/myuser,myuser=CTc/myuser}
datid                  | 16639
datname                | mydb
datcache               | 8000
datcollate             | en_US.UTF-8
datctype               | en_US.UTF-8
datcollation           | english_united states.1252
datconninfo            | 
datowner               | myuser
datreplident           | d
datistemplate          | f                     
datallowconnections    | t
datfrozenxid           | 819
dattablespace          | 1663
datconfig              | 
datacl                 | {=c/myuser,myuser=CTc/myuser}
datid                  | 16639
datname                | mydb
datdba                 | 10
encoding               | 6
datcollate             | en_US.UTF-8  
datctype               | en_US.UTF-8
datistemplate          | f
dattrusted             | f
datuser                | 10
datindx               | 24897
datvacuumxid           | 0
datminmxid             | 1
dattablespace          | 1663
datconnlimit           | -1
datacl                 | {=c/myuser,myuser=CTc/myuser}
datlastsysoid          | 13976
datfrozenxid           | 819
datminmxid             | 1
dattablespace          | 1663
datconnlimit           | -1

In addition to checking sizes, the pg_database catalog provides other useful configuration details for troublehooting and inspecting your PostgreSQL databases.

The pgAdmin GUI

If you use pgAdmin for managing PostgreSQL, you can easily discover database sizes in the graphical interface.

• Navigate to the database you want to check
• Right click on the database name
• Select Properties
• Choose the Statistics tab

In the statistics, you‘ll see total database size displayed:

You can also get sizes on some individual database objects like tables and indexes through pgAdmin‘s UI.

The benefit here is using pgAdmin‘s visualization tools rather than writing SQL queries. The downside is that it can only display information about one database at a time, while SQL allows more flexibility for aggregations across multiple databases.

The psql Command Line

psql is the built-in command line tool for working with PostgreSQL databases. For admins who prefer the terminal, psql provides an easy way to find database sizes.

To start, connect to the postgres database, which provides access to system catalogs:

psql -d postgres

Then run the pg_database_size() query:

postgres=# SELECT pg_size_pretty(pg_database_size(‘mydb‘));

This prints out the nicely formatted size:

 pg_size_pretty 
----------------
 2048 MB
(1 row)

You can run any of the other size queries shown in this article through the psql terminal.

In addition to ad-hoc queries, psql provides the \l+ meta-command to show sizes for all databases:

postgres=# \l+
                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 mydb      | myuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 2048 MB | pg_default | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7511 MB | pg_default | Default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7657 MB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            | 
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7657 MB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |  
(4 rows)

This provides a quick overview of all your database sizes in one place!

Conclusion

There are many handy methods to find the size of PostgreSQL databases, including:

• The pg_database_size() function for a simple size check
• pg_size_pretty() for readable formats
• pg_database system catalog queries for configs
• pgAdmin‘s graphical interface
• psql‘s built-in commands

Choosing among these options depends on your specific needs and PostgreSQL access methods. You might use pgAdmin for a one-off check, while monitoring scripts would query pg_database directly.

Hopefully this guide has shone some light on where PostgreSQL database size info lives, and how to access it through various interfaces. Finding current and historical database sizes is invaluable for planning and optimizing any healthy PostgreSQL deployment.