#16 How do I find out the size of my database in postgresql?

Hello and good day and welcome to another article on some handy tools of the trade, namely a functions that can be used to get the size of postgresql database objects and when I say database objects it mean any object whether it be indexes, tables, entire database itself and many more.

For every article in the learning series I will mention a thing or two as it is rhetorical to the topic we are covering, which will help quickly find out information or ease the way of getting information that we need. I will mark it as a TIP or Trick of the trade. **

For this particular one, which is getting to know or calculating the size of the DB there are multiple ways.

TIP of the trade: you can always use \l+ whilst you are connected to any database in postgres to get the size of all the DB's and it will take care of the things for you. but there are other ways to get this information.

Now what are the ways in which you can get the size of the DB?

1. First one was mentioned above as doing just a \l+

postgres=# \l+


List of databases

-[ RECORD 1 ]-----+-------------------------------------------
Name              | flask
Owner             | raj
Encoding          | UTF8
Collate           | C
Ctype             | UTF-8
Access privileges |
Size              | 7705 kB
Tablespace        | pg_default
Description       |

-[ RECORD 2 ]-----+-------------------------------------------
Name              | rajtest
Owner             | raj
Encoding          | UTF8
Collate           | C
Ctype             | UTF-8
Access privileges |
Size              | 435 MB
Tablespace        | pg_default
Description       |

Now the query that runs behind the scenes which does all the magic for you is the one mentioned below:

SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
       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",
       t.spcname as "Tablespace",
       pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;

If you look at it carefully its using the system function "pg_size_pretty" and then "pg_database_size". Now this gives you another way of doing things and why is this useful? because your \l+ is not going to work everywhere as it only works in the terminal not in any of the clients that you or your app team would be using.

More important is when you are writing an application that cannot just run \l+ or \d+ and fetch the results for you. So what is the solution to this? well the second way is the solution

2. Here you can use the functions mentioned above to get the results but just for fun lets see what happens when we call the function "pg_database_size".

postgres=# select pg_database_size('postgres');

-[ RECORD 1 ]----+--------

pg_database_size | 8227839

Now the above shows you the size of the but you will have no idea as to what it is in terms of kb, mb, bytes gigs or what its measure is. So that is when the pretty function comes into picture and makes things pretty for you. I will cover pg_size_pretty in another article but for now understand it makes things look pretty. So when you run the third one which is:

3. "pg_size_pretty(pg_catalog.pg_database_size(dbanme))" this is what you get:

select pg_size_pretty(pg_catalog.pg_database_size('postgres'));
-[ RECORD 1 ]--+--------

pg_size_pretty | 8035 kB

So there you go, and as I mentioned before there could be more of them but I have covered some very basic and handy ones which can be used all the time and will give you quick results.

Thanks for coming and reading the article. Feel free to post your questions if any and I will be more than happy to answer those for you. Happy learning!!