Is there any query to fetch disk space occupied by a tablespace in Azure Database for PostgreSQL flexible server

Question

Is there any query to fetch disk space occupied by a tablespace in Azure Database for PostgreSQL flexible server

Answer 1

if you are using psql you can use \db+ metacommand.

If you use other tools please use this query:

SELECT spcname AS "Name",
  pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
  pg_catalog.pg_tablespace_location(oid) AS "Location",
  pg_catalog.array_to_string(spcacl, E'\n') AS "Access privileges",
  spcoptions AS "Options",
  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS "Size",
  pg_catalog.shobj_description(oid, 'pg_tablespace') AS "Description"
FROM pg_catalog.pg_tablespace
ORDER BY 1;

Answer 2

Thanks for posting your question in the Microsoft Q&A forum.
There is a query that can be used to fetch the disk space occupied:

SELECT
  tablespace_name,
  pg_size_pretty(pg_total_relation_size('your_table_name')) AS total_size,
  pg_size_pretty(pg_total_relation_size('your_table_name') - pg_relation_size('your_table_name')) AS data_size,
  pg_size_pretty(pg_relation_size('your_table_name')) AS index_size
FROM
  pg_tables
WHERE
  schemaname = 'public' -- or the schema where your table resides
  AND tablename = 'your_table_name';

In addition, you can use the Azure portal to set up alerts on metrics for Azure Database for PostgreSQL - Flexible Server.

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **