PostgreSQL – Best Way to Analyze Free Space and Usage Growth

monitoringpostgresqltablespaces

In Oracle you can in datadictinary analyze free space by using SQL like:

select
   b.tablespace_name, b.file_id, b.file_name, b.bytes/1024 total_KBytes,
   sum(a.bytes)/(1024) sum_free_Kbytes, max(a.bytes/(1024)) max_free_Kbytes
from sys.dba_free_space a, sys.dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name, b.file_id, b.file_name, b.bytes
order by b.tablespace_name;

How can I do this in postgres? What are the best tools to view and monitor database space in postgres?

Best Answer

Postgres' tablespace concept is completely different to Oracle's. Postgres doesn't use container files the way Oracle does. Each table is stored in one or more individual files. A tablespace is essentially just a directory where files are stored. It is not container file like in Oracle.

If a table gets dropped or truncated the space is immediately released on the file system level.

For a single table you could measure the size of the file(s) on disk and the actual size of the data which can deviate substantially depending on the write pattern of the application. This is caused by Postgres' implementation of MVCC.

The Postgres Wiki has several queries to investigate that:
https://wiki.postgresql.org/wiki/Show_database_bloat

Related Question