How to find out database size in PostgreSQL: Guide

When working with the PostgreSQL DBMS, you often need to know the size of a database, table, or specific index. Information about the size of a database can be useful for optimizing system operation, monitoring performance, or assessing disk space usage. PostgreSQL provides convenient built-in functions for obtaining information about the sizes of database objects.

In this article, we will look at a simple way to determine the size of the entire database cluster or a specific database in PostgreSQL.

The article on the page is based on my many years of experience with this technology and is presented to you as a short instruction, but if you want to get acquainted with it in more detail, then I recommend that you go to the official documentation on PostgreSQL.

The first query shows the size of each database in the cluster.

psql> SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;

The pg_database system table contains various information about all databases in the current cluster. The datname field in the table stores the database name.

The pg_database_size function returns the database size in bytes, but we are not interested in looking at the information in bytes, so we pass the function result further to the pg_size_pretty function. The pg_size_pretty function converts bytes into a more readable format, such as kilobytes, megabytes, and gigabytes, so that the result is readable.

As a result, the query returns a list of all databases in the PostgreSQL cluster along with their sizes in a convenient format, which allows you to quickly estimate the amount of space occupied.

With the second query we look at the size of a specific database by passing the database name to the pg_database_size function.

psql> SELECT pg_size_pretty(pg_database_size('demo'));

Ultimately, understanding how to find out the size of a database in PostgreSQL is an important skill for administrators and developers. It allows you to effectively manage resources, identify bottlenecks, and plan for future system development. Built-in PostgreSQL functions, such as pg_database_size, provide a flexible and convenient way to analyze data. Regular use of these tools will help maintain database performance at a high level and prevent problems associated with insufficient disk space.