Menu
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?
412
pg_relation_size accepts the OID or name of a table, index or toast table, and 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.)
Use pg_total_relation_size() to obtain the total table size, including any indexes; pg_indexes_size() will return the total size of any indexes associated with the table. Note that somewhat this function's naming is somewhat counterintuitive, as it can be used to retrieve data for any relation, not just tables.
pg_size_pretty() A function for displaying sizes in bytes in human-readable format. pg_size_pretty() is a system function for displaying a size in bytes into human-readable format. pg_size_pretty() was added in PostgreSQL 8.1.
I understand that External Size is the size that related objects of this table (like indices) take. Upon issuing the command truncate links_groupseen; , only 394 MB disk space was released (I checked right after, via df -h ).
The pg_relation_size () function is used to get the size of a table. To make the result readable, one can use the pg_size_pretty () function. The pg_size_pretty () function takes the result of another function and formats it using bytes, kB, MB, GB or TB as required.
pg_total_relation_size: Total size of a table. pg_relation_size: The size of an object (table index, etc.) on disk. It is possible to get more detailed information from this function with additional parameters. pg_size_pretty: Other functions return results in bytes. Converts this into readable format (kb, mb, gb)
pg_relation_size accepts the OID or name of a table, index or toast table, and 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 ...
(TOAST space, free space map, and visibility map are included.) pg_indexes_size accepts the OID or name of a table and returns the total disk space used by all the indexes attached to that table. pg_database_size and pg_tablespace_size accept the OID or name of a database or tablespace, and return the total disk space used therein.
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.
179
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With