How to find the PostgreSQL database size

PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.
If you want to find the size of a database, in our example the sqview database, please tape:
sqview-# SELECT pg_database_size(‘sqview’);
(1 ligne)

The result in octal, for a pretty print we will use the pg_size_pretty function which converts the size in bytes to human understandable format.
sqview-# SELECT pg_size_pretty(pg_database_size(‘sqview’));
106 MB
(1 ligne)

Well if we need to get the size of all databases
sqview-# SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
datname | size
postgres | 3537 kB
template1 | 3537 kB
template0 | 3480 kB
freedom | 25 MB
anakeen | 6081 kB
sqview | 106 MB
rt | 8201 kB
(7 lignes)

PostgreSQL can also give the size of a table ‘users’ like this

sqview-# SELECT pg_size_pretty(pg_relation_size(‘users’));
64 kB
(1 ligne)

This value exclude indexes and some auxiliary data.
If you want to include them use pg_total_relation_size instead of pg_relation_size as shown below.
sqview-# SELECT pg_size_pretty(pg_total_relation_size(‘users’));
152 kB
(1 ligne)

to find the largest table in the postgreSQL database.
sqview-# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
relname | relpages
sqview | 9666
(1 ligne)


Author: Ali MEZGANI

My name is MEZGANI Ali. I was born back in 1978 in Rabat Morocco. My interests are Debian Linux , programming , science and music.

6 thoughts on “How to find the PostgreSQL database size”

  1. This gives you the PG database names in Megabytes.

    SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    pg_catalog.pg_database_size(d.datname)/1024/1024 as size_in_MB,
    CONCAT(pg_catalog.pg_database_size(d.datname)/1024/1024, ‘ MB’) as size_in_MB
    FROM pg_catalog.pg_database d
    WHERE d.datname ‘rdsadmin’ and d.datname ‘template0’ and d.datname ‘template1’ and d.datname ‘postgres’
    ORDER BY pg_catalog.pg_database_size(d.datname) DESC;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s