What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)

by

For a random table:

# select 
  pg_relation_size(20306, 'main') as main,
  pg_relation_size(20306, 'fsm') as fsm,
  pg_relation_size(20306, 'vm') as vm,
  pg_relation_size(20306, 'init') as init,
  pg_table_size(20306), 
  pg_indexes_size(20306) as indexes,
  pg_total_relation_size(20306) as total;
  main  |  fsm  |  vm  | init | pg_table_size | indexes |  total 
--------+-------+------+------+---------------+---------+--------
 253952 | 24576 | 8192 |    0 |        286720 |  196608 | 483328
(1 row)

From that, you can tell pg_table_size is the sum of all the return values of pg_relation_size. And pg_total_relation_size is the sum of pg_table_size and pg_indexes_size.

If you want to know how much space your tables are using, use pg_table_size and pg_total_relation_size to think about them — one number is table-only, and one number is table + indexes.

Check the storage file layout for some info about what fsm, vm, and init mean, and how they’re stored on disk.

Leave a Comment

tech