Matthew J. Clemente

How Large Is My PostgreSQL Database?

May 21, 2022
1 minute

On more than one occasion I've wanted to quickly check the size of a database. Fortunately, PostgreSQL has a function for that, so we can run a query to find the answer.

PostgreSQL has functions beyond counting, including dozens for system administration. Functions related to database object size can be found in the documntation here in Table 9.92.

At first glance, it looks like the way to answer our question, "How big is my database?", is to use pg_database_size; the docs say that it:

Computes the total disk space used by the database with the specified name or OID.

Here's what it looks like in use:

SELECT pg_database_size('database_name');
// 2515784239

As you may have guessed, the number pg_database_size returned (2,515,784,239) is in bytes. That is a lot of digits and frankly a bit difficult to parse.

For a more readable, and hopefully useful answer, we can use one more function, pg_size_pretty. Here's its definition:

Converts a size in bytes into a more easily human-readable format with size units (bytes, kB, MB, GB or TB as appropriate).

Our updated query and response would look like this:

SELECT pg_size_pretty(pg_database_size('database_name'));
// 2399 MB

That's the answer I was looking for!