How to get the disk size of a Postgresql database

If you’re a PostgreSQL user or developer, you may often need to monitor the size of your database to manage storage resources efficiently. One crucial aspect of this monitoring is checking the disk size of your database.

In this post, we will explore how to get the disk size of a database in PostgreSQL. We will cover various methods, including built-in PostgreSQL functions and SQL queries, that will help you accurately determine the disk size of your database.

Get the disk size of a database in Postgresql

In PostgreSQL, you can get the disk size of a database by querying the pg_database_size function. Here’s an example SQL query:

SELECT pg_size_pretty(pg_database_size('your_database_name'));

Replace ‘your_database_name’ with the name of the database for which you want to get the size. The pg_database_size function returns the size in bytes, and pg_size_pretty is used to convert it into a human-readable format.

You can also get the size of the current database like this:

SELECT pg_database_size(current_database());

However, this is limited to just the current database. If you want to know the size of all the databases together, then you’ll need a query such as the following:

SELECT sum(pg_database_size(datname)) from pg_database;

If you want to get the size of all databases in your PostgreSQL server, you can use the following query:

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

This will give you the size of all databases in descending order, with the largest database listed first. The pg_database table contains information about all databases in the PostgreSQL server, and pg_database_size function is used to get the size of each database in bytes. The pg_size_pretty function is used to convert the size into a human-readable format.

dbname | size | size_pretty
------------+----------+-------------
pggbt3 | 77828655 | 74 MB
postgres | 8532527 | 8333 kB
template1 | 8532527 | 8333 kB
template0 | 7938563 | 7753 kB
(4 rows)

Understanding pg_database table in Postgresql

In PostgreSQL, the pg_database table is a system catalog table that stores information about all databases within a PostgreSQL database cluster. It contains metadata about each database, such as the database name, database owner, creation time, and various other attributes.

Here are some important columns in the pg_database table:

• datname: This column stores the name of the database.
• datowner: This column stores the name of the owner of the database.
• datcreate: This column stores the timestamp when the database was created.
• datistemplate: This boolean column indicates whether the database is a template database or not. If it is set to true, it means the database is a template database that can be used as a blueprint for creating new databases.

 

The pg_database table is a useful resource for querying metadata about databases in a PostgreSQL cluster, and it can be used to retrieve information such as database sizes, owners, and access control settings.