skip to Main Content

What I am trying to do is to calculate the sum of the 2 tableSpaces I have in my PostgreSQL DB.

SELECT pg_size_pretty (pg_tablespace_size ('pg_default') );

with this query I am able to find the used space but when I modified it to find the details of both the tablespaces by using below query

SELECT pg_size_pretty ( pg_tablespace_size ('poolData')
                       ,pg_tablespace_size ('pool')    );

but it didn’t gave any output. Is there any way to find both in a single query, so that I can calculate the sum of tablespaces?

2

Answers


  1. Function pg_tablespace_size() returns type bigint, so you can add them with a regular +.

    SELECT pg_size_pretty( pg_tablespace_size('poolData')
                          +pg_tablespace_size ('pool')    );
    

    You can also sum() sizes of tablespaces you’re interested in, fetching them from pg_tablespace.

    select pg_size_pretty(sum(spcname)) from pg_tablespace
    where spcname in ('poolData','pool');
    
    Login or Signup to reply.
  2. To be able to find the sum of tablespaces in postgresql in this scenario i would recommend that you use the following query :

    SELECT
      pg_tablespace_size('pg_default') AS pg_default_size,
      pg_tablespace_size('poolData') AS poolData_size,
      pg_tablespace_size('pool') AS pool_size,
      pg_size_pretty(pg_tablespace_size('pg_default') + pg_tablespace_size('poolData') + pg_tablespace_size('pool')) AS total_size;
    

    The query above retrieves the size of each tablespace individually and then calculates the sum of the sizes.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search