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');

Thoroughgoing

What is its unit though? Is it in bytes? – Irita

It is bytes. :-) – Piled

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

@Headband 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) – Seldom

Answer 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+

Marni

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

Answer 2

You can get the names of all the databases that you can connect to from the "pg_database" 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.

Pasture

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

@user2151087: pg_database_size() includes the sizes for indexes – Ate

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… – Bullish

Answer 3

-- Database Size
SELECT pg_size_pretty(pg_database_size('Database Name'));

-- Table Size
SELECT pg_size_pretty(pg_relation_size('table_name'));

Riviera

How is that answer different to Mike's? – Ate

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. – Gizzard

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

Answer 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.

Diversified

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

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

Answer 5

SELECT pg_size_pretty(pg_database_size('name of database'));

Will give you the total size of a particular database however I don't think you can do all databases within a server.

However you could do this...

DO
$$
DECLARE
r   RECORD;
db_size TEXT;
BEGIN
FOR r in
SELECT datname FROM pg_database
WHERE datistemplate = false
LOOP
db_size:= (SELECT pg_size_pretty(pg_database_size(r.datname)));

RAISE NOTICE 'Database:% , Size:%', r.datname , db_size;

END LOOP;
END;
$$

Rangefinder

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

Answer 6

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;

Eastwards

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. – Arguello

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; – Intrigue

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;. – Scorify

Answer 7

From the PostgreSQL wiki.

---

_{NOTE: Databases to which the user cannot connect are sorted as if they were infinite size.}

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS Size
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20

---

The page also has snippets for finding the size of your biggest relations and largest tables.

Nubbin

Answer 8

Start pgAdmin, connect to the server, click on the database name, and select the statistics tab. You will see the size of the database at the bottom of the list.

Then if you click on another database, it stays on the statistics tab so you can easily see many database sizes without much effort. If you open the table list, it shows all tables and their sizes.

Willywilly

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). – Richie

The same technique works when one wants to know the Table size. Also works as of today (2023) and on pgAdmin 4 – Enlistment

Answer 9

You can use below query to find the size of all databases of PostgreSQL.

Reference is taken from this blog.

SELECT 
    datname AS DatabaseName
    ,pg_catalog.pg_get_userbyid(datdba) AS OwnerName
    ,CASE 
        WHEN pg_catalog.has_database_privilege(datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(datname))
        ELSE 'No Access For You'
    END AS DatabaseSize
FROM pg_catalog.pg_database
ORDER BY 
    CASE 
        WHEN pg_catalog.has_database_privilege(datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(datname)
        ELSE NULL
    END DESC;

Charismatic

Answer 10

du -k /var/lib/postgresql/ | sort -n | tail

Aholla

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. – Aright

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

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

Answer 11

You could use the following query in order to get the sizes of all the database in descending order based on their sizes.

SELECT pg_database.datname as "databasename", 
pg_database_size(pg_database.datname)/1024/1024/1024 AS sizegb 
FROM pg_database ORDER by pg_database_size(pg_database.datname) DESC;

For better understanding, explore the following article: How to Find The Size of all Databases in PostgreSQL

Gazebo

Answer 12

Using pgAdmin (version 8.2), there is a hidden 'Statistics' menu on the right

Regenerator