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

just a side note: `select pg_database_size('databaseName')/1024/1024;` gives you better human readable megabytes — Markus, Jun 23 '20 at 11:46
@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) — Stephen, Feb 24 '22 at 22:33

score 581 · Answer 1 · edited Jul 11 '20 at 01:25

581

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+

edited Jul 11 '20 at 01:25

user664833

18,397
19
91
140

answered Jun 02 '14 at 08:38

Ashish

6,001
2
18
18

and if i want to know only one schema size? is there a way to do that instead by using [this approach](https://stackoverflow.com/questions/4418403/list-of-schema-with-sizes-relative-and-absolute-in-a-postgresql-database)? – luisvenezian Oct 19 '20 at 01:51

Mike Sherrill 'Cat Recall' · Answer 2 · 2023-07-24T16:13:27.597

316

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.

edited Jul 24 '23 at 16:13

answered Sep 20 '13 at 00:39

Mike Sherrill 'Cat Recall'

91,602
17
122
185

Sometimes database contains indexes also. It has some storage value. I am looking for one command that will provide size of the complete database. – Beautiful Mind Sep 20 '13 at 04:12
13

@user2151087: `pg_database_size()` *includes* the sizes for indexes – Sep 20 '13 at 06:00
4

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… – Skippy le Grand Gourou May 28 '20 at 10:07

score 225 · Answer 3 · edited Jul 24 '23 at 15:23

225

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

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

edited Jul 24 '23 at 15:23

StackzOfZtuff

2,534
1
28
25

answered Sep 20 '13 at 06:55

Srinivasreddy Jakkireddy

2,581
1
11
7

5

How is that answer different to Mike's? – Sep 20 '13 at 06:58
9

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](https://stackoverflow.com/a/18907188/4162356]) is for all databases on the server of which the latter answers better the original question. – James Brown Feb 28 '19 at 08:46
1

The question was "What is the command to find the size of *all* the databases?" – DylanYoung May 24 '22 at 18:17

score 92 · Answer 4 · edited May 23 '17 at 11:55

92

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.

edited May 23 '17 at 11:55

Community

1
1

answered Jun 05 '15 at 04:32

owyongsk

2,349
1
19
21

1

For me, only `\d+ *` worked, plain `\d+` returned `Did not find any relations.` – phil pirozhkov Mar 14 '18 at 10:14
4

@philpirozhkov Connect to a database first (`\c dbname`), then do `\d+`. – chappjc Jan 15 '19 at 15:53

score 33 · Answer 5 · edited Mar 09 '18 at 15:27

33

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;

edited Mar 09 '18 at 15:27

jwg

5,547
3
43
57

answered Mar 09 '18 at 10:38

Anoop Sharma

381
3
3

5

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. – Onni Hakala Apr 25 '18 at 05:30
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;` – Michael Apr 26 '19 at 07:12
3

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;`. – M-Dahab Mar 10 '20 at 09:50

score 31 · Answer 6 · edited Jul 01 '19 at 22:08

31

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;
$$

edited Jul 01 '19 at 22:08

MozenRath

9,652
13
61
104

answered Mar 21 '17 at 16:42

Shaun McCready

773
7
15

3

why can't it be a single query from `pg_database` rather than this hideous pl/pgsql? – MozenRath Jul 01 '19 at 22:09

GollyJer · Answer 7 · 2020-06-08T19:55:52.433

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.

score 8 · Answer 8 · answered Apr 17 '14 at 07:55

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.

answered Apr 17 '14 at 07:55

SPRBRN

2,406
4
35
48

3

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). – zloster Feb 15 '16 at 14:22
1

The same technique works when one wants to know the Table size. Also works as of today (2023) and on pgAdmin 4 – DarkCygnus Apr 20 '23 at 21:50

score 5 · Answer 9 · answered Jan 18 '17 at 21:18

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;

score 1 · Answer 10 · edited Jul 24 '23 at 15:22

1

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

edited Jul 24 '23 at 15:22

StackzOfZtuff

2,534
1
28
25

answered May 16 '18 at 17:23

Jon Carnes

27
1

3

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. – fzzfzzfzz May 16 '18 at 17:27
4

While this might be an accurate answer, it's best practice to include some explanation. – sniperd May 16 '18 at 17:27
The correct command on CentOS is this one: `du -k /var/lib/pgsql/ | sort -n | tail` – Feriman Mar 09 '20 at 14:24

score 0 · Answer 11 · answered Jul 31 '23 at 06:36

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

Postgres DB Size Command

11 Answers11

Linked