Postgres: check disk space taken by materialized view?

Question

I know how to check the size of indexes and tables in Postgres (I'm using version 9.4):

SELECT
   relname AS objectname,
   relkind AS objecttype,
   reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
   FROM pg_class
   WHERE relpages >= 8
   ORDER BY relpages DESC;

But this doesn't show materialized views. How can I check how much disk space they are taking up?

Erwin Brandstetter · Answer 1 · 2022-04-06T20:16:38.303

This is assuming that materialized views have relpages >= 8 in pg_class, which doesn't have to be the case. It can actually be empty - not populated yet, indicated by pg_class.relispopulated = FALSE. The corresponding disk file has a zero size in this case.

Try instead:

SELECT oid::regclass::text  AS objectname
     , relkind   AS objecttype
     , reltuples AS entries
     , pg_size_pretty(pg_table_size(oid)) AS size  -- depending - see below
FROM   pg_class
WHERE  relkind IN ('r', 'i', 'm')
ORDER  BY pg_table_size(oid) DESC;

The cast to regclass makes sure that the name of the relation is quoted and schema-qualified where needed (where the current search_path would not resolve to the same object). See:

Missing "public" schema when converting a "regclass" value to text

Available values for relkind are:

r = ordinary table,
i = index,
S = sequence,
v = view,
m = materialized view,
c = composite type,
t = TOAST table,
f = foreign table

Use one of the database object size functions rather than building your own. Be aware that the "size of a table" can be defined in different ways. Details:

Measure the size of a PostgreSQL table row

Postgres: check disk space taken by materialized view?

1 Answers1