Is there a way to find the physical disk size of a table in Postgres?

Question

Please note that I'm talking about the physical size, on disk, that the table consumes, not the size of the data and metadata in the table.

As noted here: https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained.

Let's say that I have a very hot table where nearly all rows are updated very often. This would mean that is is rather unlikely that pages become entirely free at a given point in time where VACUUM runs, and an exclusive table lock is pretty much out ouf the question.

I imagine such a table would grow fairly quickly in physical disk size which cannot be reclaimed by the OS without a VACUUM FULL.

So, is there a way to find out just how much physical disk space it is taking at a point in time from within Postgres itself? If not, can I at least find out which storage blocks are referenced by the table and look it up in a shell?

Answer 1

It seems that I've misread the definition of pg_total_relation_size - this seems to be the exact value that I'm looking for and includes the physical size on disk. I led myself to believe that this value only includes data but not the table's reserved disk space.

See also this answer for a more thorough explanation.

For completeness' sake:

SELECT relid FROM pg_stat_all_tables WHERE schemaname = 'schema' AND relname = 'tablename';
-- relid from above
SELECT pg_size_pretty(pg_total_relation_size) FROM pg_total_relation_size(relid);