Find PostgreSQL database size using SQL ‘Select’

This article will show you a very simple way to find the size of a PostgreSQL database using a SQL SELECT statement. This will work on PostgreSQL version 8.3.3, but should work on older versions as well. Looking for optimize your database performance, check out How to find when PostgreSQL tables were auto-vacuumed and auto-analyzed and other PostgreSQL tips.

Finding the DB Size in PostgreSQL

Finding the database size in Postgres is useful to understand how much data is stored in a database and for capacity planning purposes.

First you will need to pull up a SQL command-line prompt using psql

psql -h hostname -d database -U username

After you have established a successful connection to the database you want to find the size of. Execute the following SQL query:

SELECT pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database where datname='database_name';

You should see a response similar to the following:

 datname     | size
 -----------------+-------
 database_name | 15 MB
 (1 row)

Let’s break down the components of this command to understand it better.

SELECT is the PostgreSQL command used to fetch data from a database.

pg_database.datname is selecting the name of the database from the system catalog table pg_database. This table contains information about the available databases.

pg_size_pretty(pg_database_size(pg_database.datname)) AS size is a bit more complex. pg_database_size(pg_database.datname) is a function that returns the size of the database in bytes. The outer function pg_size_pretty converts this byte size into a human-readable format, such as in KB, MB, GB, etc. The AS size clause renames this column in the output to “size”.

FROM pg_database specifies that the data should be fetched from the pg_database system catalog table.

WHERE datname='database_name' is a condition to filter the databases. Replace ‘database_name’ with the name of the database you’re interested in. This condition ensures that the command will only return the size of the specified database.

This command fetches the name and the size of a specific database within your PostgreSQL instance. The size is displayed in a human-readable format. The output will be a table with two columns: datname for the database name, and size for the size of the database.