The tablespace in Postgres is the location on the disk where all the data of the database is stored. This data includes tables, triggers indexes, etc. The tablespace is actually needed by Postgres for mapping the logical name onto the physical address/location on the disk. The 2 default tablespaces that are being provided by Postgres are pg_default and pg_global. The first one stores default data while the other one stores global data.

This write-up will teach us the method to get the size of the tablespace in PostgreSQL.

How to Find the Size of a Postgres Tablespace?

To find the size of the Postgres Tablespace, we use a built-in function named “pg_tablespace_size” that takes the table name as an argument and returns the size of the tablespace. The basic syntax for this function is as follows:

SELECT pg_tablespace_size('tabsp_name');

In the above syntax:

● The pg_tablespace_size() function is used to find the size of a tablespace.

● The tablespace’s name is provided as a parameter to the function.

The function takes the name of tablespace as an argument to give its size in bytes.

To make the size of a tablespace human-readable the “pg_size_pretty()” function is used. In this case, the “pg_size_pretty()” function will take the result of the “pg_tablespace_size()” as its parameter and return the size of the tablespace in bytes that is a human-readable format.

The syntax of using pg_tablespace_size() along with pg_size_pretty() is:

SELECT pg_size_pretty(
pg_tablespace_size('tabsp_name'));

Let's see the example in order to get the size of a Postgres tablespace.

Example: Finding the Size of a Postgres Tablespace

To get the size of the tablespace named “example_tablespace” (that is already created) in bytes, we write the query as:

SELECT pg_size_pretty(
pg_tablespace_size('example_tablespace'));

On execution of this query, the output will look like this:

We can see that executing the query gives the size of the tablespace in KBs.

So that was all about the tablespace size.

Conclusion

We can find the size allocated to a tablespace in Postgres by utilizing the pg_tablespace_size() function. This function takes in the name of the tablespace and returns the size of the tablespace. Another function that is used to make the returned size human-readable is “pg_size_pretty()”. In this post, we have discussed both the functions to get the size of a tablespace in Postgres.