How can I check the size of the database and tables in PostgreSQL?

We often focus on writing code, optimizing algorithms and building new features. However, sometimes we need to put on our administrator hat and take a look under the hood to ensure that our systems are running smoothly. One of the key maintenance tasks is monitoring the size of the database. A too large database can lead to performance issues, higher infrastructure costs and slower backups.
In this article, I will show you how to quickly check the size of the entire database and individual tables using a few simple PostgreSQL SQL queries. This is knowledge that will be useful in any project.

Checking the size of all databases

Let's start with an overview. If you have multiple databases on a single PostgreSQL server, it is useful to know which database is using the most space. This can be determined using a simple query of the pg_database system catalogue.

SELECT
  datname AS database_name,
  pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

After running this query, you will receive a list similar to this one:

     database_name             |  size
-------------------------------+---------
app_name_production            | 107 GB
app_name_development_1         | 44 MB
app_name_development_2         | 28 MB
app_name_test                  | 27 MB

This allows you to immediately identify the database on which to focus your attention.

Analysis of table size in a specific database

Once we know which database is the largest, it's time for a more in-depth analysis. We want to find out which tables inside this database occupy the most space. This is key to identifying potential problems, such as tables with logs that have grown to enormous sizes, or tables in which it would be beneficial to archive old data.
To do this, connect to the selected database and run the following query:

SELECT
  relname AS table_name,
  pg_size_pretty(pg_relation_size(oid)) AS data_size,
  pg_size_pretty(pg_total_relation_size(oid) - pg_relation_size(oid)) AS index_size,
  pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relkind = 'r' -- 'r' means normal table
AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
ORDER BY pg_total_relation_size(oid) DESC;

Here is example output:

                     table_name                     | data_size  | index_size | total_size
----------------------------------------------------+------------+------------+------------
maps_details                                        | 58 GB      | 19 GB      | 77 GB
taggings                                            | 1230 MB    | 4440 MB    | 5670 MB
search_results                                      | 3433 MB    | 732 MB     | 4165 MB
activities                                          | 2959 MB    | 1080 MB    | 4038 MB
cars                                                | 2290 MB    | 458 MB     | 2748 MB

We can see not only which tables are large, but also the ratio of data size to index size. Sometimes, as with the taggings table above, the indexes can take up much more space than the data itself! This may indicate that it is worth reviewing the indexing strategy.

Summary

I hope these two queries will help you manage your PostgreSQL database more effectively. Regularly checking its size is a simple but very effective practice that can save you many problems in the future.

Happy query-ing!