Postgres equivalent for Oracle's DBA_FREE_SPACE and DBA_DATA_FILES

Question

This is the sql query. I need to convert into postgres query

SELECT a.tablespace_name,
       a.maxbytes,
       a.mbytes,
       (a.maxbytes - a.mbytes),
       ROUND(((a.maxbytes - a.mbytes) / a.maxbytes)*100,2) AS fieldvalues
FROM (
  SELECT tablespace_name,
         SUM(maxbytes / 1024 / 1024) maxbytes,
         SUM(bytes / 1024 / 1024) mbytes
  FROM dba_data_files
  WHERE tablespace_name IN (SELECT tablespace_name
                            FROM dba_segments
                            WHERE OWNER = 'TEST')
  GROUP BY tablespace_name
) a,
  (SELECT tablespace_name,
         SUM(bytes / 1024 / 1024) mbytes
  FROM dba_free_space
  WHERE tablespace_name IN (SELECT tablespace_name
                            FROM dba_segments
                            WHERE OWNER = 'TEST')
  GROUP BY tablespace_name
) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY a.tablespace_name

Answer 1

Short answer: there is no equivalent query in Postgres.

---

The tablespace concept in Postgres is completely different to Oracle's tablespace concept. Postgres doesn't have container files. As documented in the manual each table is stored in one (or more files) specific to that table. A tablespace is nothing more then a directory on the harddisk.

Because of that, no such thing as DBA_FREE_SPACE exists (or is necessary) in Postgres.

To calculate the size of a single database, you can use e.g.

select pg_database_size('my_database_name');

To calculate the size of all tables in a schema, use

select table_schema,
       table_name,
       pg_size_pretty(pg_total_relation_size (format('%I.%I', table_schema, table_name)))
from information_schema.tables
where table_schema not in ('information_schema', 'pg_catalog')
order by pg_total_relation_size(format('%I.%I', table_schema, table_name)) desc;

Answer 2

In addition to what is said above, the concept of postgres tablespace is similar to Oracle tablespace that

• it is a logical construct
• you can use each tablespace to store files on separate mount point and thus can have separate disks etc.

But the concept of PCT used and PCT used does not exist in Postgres - where PCT used is 100 and PCT free is zero. So if you start with a blank table, then rows are always added till the block is full.

Then, if you delete space, space is marked for deletion but deleted space is not recovered until a special process called vacuum runs. Furthermore if you update data new row is inserted and old one is marked as stale and later cleaned up as part of vacuum process.

To see how Postgres manages free space, read this blog