The difference between pg_total_relation_size vs pg_relation_size.

Products

• VMware Tanzu Greenplum

Environment

Product Version: 6.0

Resolution

Checklist:

The purpose of this article is to help explain the difference between "pg_relation_size" and  "pg_total_relation_size".
 

pg_total_relation_size returns the total on-disk space used for that table, including all associated indexes. This function is equivalent to pg_table_size + pg_indexes_size.

pg_relation_size returns the on-disk size in bytes of one fork of that relation. (Note that for most purposes it is more convenient to use the higher-level functions pg_total_relation_size or pg_table_size, which sum the sizes of all forks.) With one argument, it returns the size of the "main" data fork of the relation. The second argument can be provided to specify which fork to examine.

See Database Object Management Functions in the Postgres documentation for information on forks of the relation.

The below example is a test table with a small number of update and delete operations. 

gpadmin=# Select pg_total_relation_size('public.Adamtest')/1024/1024 "SizeMB";
 SizeMB 
--------
      6
(1 row)
 
gpadmin=# select pg_relation_size('public.Adamtest')/1024/1024 "SizeMB";
 SizeMB 
--------
      3
(1 row)

gpadmin=# vacuum full public.Adamtest;
VACUUM
gpadmin=# Select pg_total_relation_size('public.Adamtest')/1024/1024 "SizeMB";
 SizeMB 
--------
      5 
A view to highlight the differences between the two.
gpadmin=# select                                                         
      pg_size_pretty(pg_total_relation_size(relid)) as total_size,
      pg_size_pretty(pg_relation_size(relid, 'main')) as relation_size_main,
      pg_size_pretty(pg_relation_size(relid, 'fsm')) as relation_size_fsm,
      pg_size_pretty(pg_relation_size(relid, 'vm')) as relation_size_vm,
      pg_size_pretty(pg_relation_size(relid, 'init')) as relation_size_init,
      pg_size_pretty(pg_table_size(relid)) as table_size,
      pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as external_size
 from 
      pg_catalog.pg_statio_user_tables
where 
      schemaname = 'public'
  and relname like 'adamtest';
 total_size | relation_size_main | relation_size_fsm | relation_size_vm | relation_size_init | table_size | external_size 
------------+--------------------+-------------------+------------------+--------------------+------------+---------------
 5664 kB    | 3520 kB            | 0 bytes           | 0 bytes          | 0 bytes            | 3648 kB    | 2144 kB
(1 row)
 
gpadmin=#