PostgreSQL – Size of tablespace
In this article, we will look into the function that is used to get the size of the PostgreSQL database tablespace.
The pg_tablespace_size() function is used to get the size of a tablespace of a table. This function accepts a tablespace name and returns the size in bytes.
Syntax: select pg_tablespace_size('tablespace_name');
Here we will query for the size of the pg_default tablespace using the below command:
SELECT pg_size_pretty ( pg_tablespace_size ('pg_default') );
Notice we used to make the pg_size_pretty() function to make the result humanly readable. The pg_size_pretty() function takes the result of another function and formats it using bytes, KB, MB, GB, or TB as required.
Note: There is generally not much point in creating more than one tablespace per logical file system, since one cannot control the location of every individual file within a logical file system. However, PostgreSQL does not enforce limitations on creation of new tablespace, and indeed it is not directly aware of the file system boundaries on one’s system. It just stores files in the directories where one wants them to be stored.
But for the sake of example, we will create a new tablespace and find its size. To create a new tablespace use the below command:
CREATE TABLESPACE new_tablespace LOCATION 'C:\sample_tablespace\';
Now that we have created a new tablespace named new_tablespace, let’s find its size.
SELECT pg_size_pretty ( pg_tablespace_size ('new_tablespace') );