53

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

I understand the basic differences explained in the documentation, but what does it imply in terms of how much space my table is actually using?

Marco Roy
  • 4,004
  • 7
  • 34
  • 50
  • 1
    http://stackoverflow.com/questions/2596624/how-do-you-find-the-disk-size-of-a-postgres-postgresql-table-and-its-indexes/2596678#2596678 – Roman Tkachuk Feb 01 '17 at 23:06
  • 1
    @a_horse_with_no_name I'm mostly confused about "returns the on-disk size in bytes of one fork of that relation." What's a relation & a fork in this context? (I'm new to PostgreSQL) It also lists some fork examples: "main data fork", "Free Space Map", "Visibility Map" & "initialization fork". What are those? Which one is relevant to the actual disk space my table is using? – Marco Roy Feb 01 '17 at 23:10

3 Answers3

54

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.

Glen Thompson
  • 9,071
  • 4
  • 54
  • 50
jmelesky
  • 3,710
  • 1
  • 24
  • 24
39

I am pretty sure after looking into the below image you will get a good understanding of various size relationships. I have made this diagram after reading all the answers mentioned in this answer section & analyzing more in DB for the query.

For storage file layout fsm, vm, and init mean, you can get from this link like @jmelesky mentioned.

types of memory in postgresql

Satish Patro
  • 3,645
  • 2
  • 27
  • 53
  • Thank you, that's very helpful. Then it seems that TOAST size can be computed as `pg_total_relation_size(relid) - pg_relation_size(relid) - pg_indexes_size(relid)` – EM0 Dec 22 '21 at 16:26
  • 1
    @EM0 that's right – Satish Patro Jan 12 '22 at 07:06
  • 1
    Ran across this as I'm putting together metrics for a Postgresql db. The docs state that given 1 argument (ie the relation), pg_relation_size will return the main fork only. So @EM0's calculation will still include the fms, vm, and init as well as the TOAST size. As of v14 there appears to be no way to get the combined main, fsm, vm, and init forks (relation_size in the diagram) as one method call. https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT – JM_24 Jan 26 '22 at 23:04
  • Got your point. Then toast_size = pg_total_relation_size(relid) - pg_indexes_size(relid) - (pg_relation_size(relid, 'main') + pg_relation_size(relid, 'fsm') + pg_relation_size(relid, 'vm') + pg_relation_size(relid, 'init')) toast_size = pg_table_size(relid) - ((pg_relation_size(relid, 'main') + pg_relation_size(relid, 'fsm') + pg_relation_size(relid, 'vm') + pg_relation_size(relid, 'init)')) – Satish Patro Jan 28 '22 at 06:51
  • 2
    Small errata in the image: the bottom box says "total_relational_size", should be "total_relation_size" – alvherre Jun 02 '22 at 12:58
13

pg_table_size: Disk space used by the specified table, excluding indexes (but including TOAST, free space map, and visibility map)

pg_relation_size: The size of the main data fork of the relation

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 = 'XXXX'
  and relname like 'XXXXXX';

total_size         | 6946 MB
relation_size_main | 953 MB
relation_size_fsm  | 256 kB
relation_size_vm   | 32 kB
relation_size_init | 0 bytes
table_size         | 6701 MB
external_size      | 5994 MB

so pg_table_size is not only the sum of all the return values of pg_relation_size but you have to add toast size

toast_bytes         | 5748 MB
xzilla
  • 1,142
  • 9
  • 19
Rachad Abi Chahine
  • 131
  • 1
  • 2
  • what do you mean by external size ? – Dineshkumar Apr 26 '21 at 11:24
  • total_size - total table size data_size - size of table's rows external_size - size of external elements, such as indexes etc. – Rachad Abi Chahine May 03 '21 at 10:30
  • "A table that has columns with potentially large entries will have an associated TOAST table, which is used for out-of-line storage of field values that are too large to keep in the table rows proper." - from the postgres docs. – Hula_Zell Nov 12 '21 at 18:55
  • @Dineshkumar you can look into this answer https://stackoverflow.com/a/70397779/8609847 – Satish Patro Dec 21 '21 at 07:51