PostgreSQL: Determining the database size

by on Jul.01, 2023, under News

PostgreSQL, often referred to as “Postgres,” is an open-source, object-relational database management system (DBMS). It is designed to efficiently store, retrieve, and manage large amounts of structured and semi-structured data. PostgreSQL provides robust features, reliability, and performance, making it a popular choice for a wide range of applications, from small-scale projects to enterprise-level systems.

PostgreSQL offers a rich set of data types, including numeric, string, text, date/time, boolean, and more. It supports advanced data manipulation features, such as transactions, concurrency control, and referential integrity, ensuring data consistency and reliability.

One of the notable strengths of PostgreSQL is its extensibility. It allows users to define their own data types, operators, and functions, enabling the creation of custom data structures and behavior tailored to specific application requirements. This extensibility makes PostgreSQL highly flexible and adaptable.

PostgreSQL also includes support for advanced SQL features, including complex queries, subqueries, and joins. Additionally, it provides additional capabilities beyond standard SQL, such as support for full-text search, geospatial data, and JSON document storage and querying.

We can determine the size of the databases relatively easily by running the following query in postgres:

select t1.datname AS db_name,
       pg_size_pretty(pg_database_size(t1.datname)) as db_size
from pg_database t1
order by pg_database_size(t1.datname) desc;

To let us evaluate the total size (in bytes), we can also use the following query:

select sum(pg_database_size(t1.datname)) AS db_name
from pg_database t1
order by db_name desc;

Of course, we can again use the built-in pg_size_pretty function to convert the size to a human readable form.

select pg_size_pretty(sum(pg_database_size(t1.datname))) AS db_name
from pg_database t1
order by db_name desc;

Leave a Reply


Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!