Postgres DB Size Command

Question

What is the command to find the size of all the databases?

I am able to find the size of a specific database by using following command:

select pg_database_size('databaseName');

Solution 1

You can enter the following psql meta-command to get some details about a specified database, including its size:

\l+ <database_name>

And to get sizes of all databases (that you can connect to):

\l+

Solution 2

You can get the names of all the databases that you can connect to from the "pg_datbase" system table. Just apply the function to the names, as below.

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;

If you intend the output to be consumed by a machine instead of a human, you can cut the pg_size_pretty() function.

Solution 3

-- Database Size
SELECT pg_size_pretty(pg_database_size('Database Name'));
-- Table Size
SELECT pg_size_pretty(pg_relation_size('table_name'));

Solution 4

Based on the answer here by @Hendy Irawan

Show database sizes:

\l+

e.g.

=> \l+
 berbatik_prd_commerce    | berbatik_prd     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 19 MB   | pg_default | 
 berbatik_stg_commerce    | berbatik_stg     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8633 kB | pg_default | 
 bursasajadah_prd         | bursasajadah_prd | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 1122 MB | pg_default | 

Show table sizes:

\d+

e.g.

=> \d+
 public | tuneeca_prd | table | tomcat | 8192 bytes | 
 public | tuneeca_stg | table | tomcat | 1464 kB    | 

Only works in psql.

Solution 5

Yes, there is a command to find the size of a database in Postgres. It's the following:

SELECT pg_database.datname as "database_name", pg_size_pretty(pg_database_size(pg_database.datname)) AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;

View more solutions

Comments

[Abel Callejo]

What is its unit though? Is it in bytes?

[john16384]

It is bytes. :-)

[Markus]

just a side note: select pg_database_size('databaseName')/1024/1024; gives you better human readable megabytes

[Stephen]

@Markus just a side note: select pg_size_pretty(pg_database_size('databaseName')); gives you better human readable sizes, no matter the magnitude. (It's a bit hard to read MB when you're in the TB)

[Beautiful Mind]

Sometimes database contains indexes also. It has some storage value. I am looking for one command that will provide size of the complete database.

[a_horse_with_no_name]

@user2151087: pg_database_size() includes the sizes for indexes

[a_horse_with_no_name]

How is that answer different to Mike's?

[zloster]

And if you click the Databases tree node (attached to a DB connection) and select the Statistics tab you will be presented with a nice summary of all the Databases and their sizes (third column).

[phil pirozhkov]

For me, only \d+ * worked, plain \d+ returned Did not find any relations.

[onnimonni]

The ordering is wrong in this function. It can't tell the difference between human readable formats. For example database of size 7151 KB comes before database of size 7 GB.

[fzzfzzfzz]

You might want to add more context about the assumptions this makes re where the database is storing its data, what the output of this will look like, etc.

[sniperd]

While this might be an accurate answer, it's best practice to include some explanation.

[chappjc]

@philpirozhkov Connect to a database first (\c dbname), then do \d+.

[James Brown]

For future me and others landing here, I'll save you the trouble: This one is shorter and for named database/table where Mike's is for all databases on the server of which the latter answers better the original question.

[Michael]

Fixed: SELECT database_name, pg_size_pretty(size) from (SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname) AS size FROM pg_database ORDER by size DESC) as ordered;

[MozenRath]

why can't it be a single query from pg_database rather than this hideous pl/pgsql?

[Feriman]

The correct command on CentOS is this one: du -k /var/lib/pgsql/ | sort -n | tail

[M-Dahab]

I think you need the "raw" size for sorting only. I used this instead of a sub-query SELECT pg_database.datname AS "DB Name", pg_size_pretty(pg_database_size(pg_database.datname)) AS "Size" FROM pg_database ORDER BY (pg_database_size(pg_database.datname)) DESC;.

[Skippy le Grand Gourou]

In case anyone was wondering, this query provides exactly the same values as \l+. The output format is easier to read, though (less columns). Tradeoff between writability and readability…

[luisvenezian]

and if i want to know only one schema size? is there a way to do that instead by using this approach?

[DylanYoung]

The question was "What is the command to find the size of all the databases?"