Find the sum of tablespaces in PostgreSQL

Question

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?

A typo. SELECT pg_size_pretty(pg_tablespace_size('poolData')), pg_size_pretty(pg_tablespace_size ('pool')); — Laurenz Albe
– Laurenz Albe, Commented Nov 23, 2023 at 11:56

Zegarek · Accepted Answer · 2023-11-23 11:55:54Z

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

answered Nov 23, 2023 at 11:55

Zegarek

30.1k5 gold badges27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1