pg_table_size()

A function for returning the size of a relation

pg_table_size() is a system function for determining the on-disk size of a relation (a table, an index or a materialized view).

pg_table_size() was added in PostgreSQL 9.0.

Usage

pg_table_size ( regclass ) → bigint

For tables, the size reported includes the actual table data, any TOAST table data, the free space map and the visibility map. The size of any indexes is not included in the total.

The caller does not require any permissions on the relation to determine its size. An ACCESS EXCLUSIVE LOCK on the table will however cause the function to stall until the lock is released.

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. A function pg_relation_size() does exist, but for historical reasons it will only report the size of a particular relation fork.

Source code

The source code for pg_table_size() is contained in src/backend/utils/adt/dbsize.c; the main work is carried out by calculate_table_size().

Change history

Examples

Basic usage example for pg_table_size():

postgres=# SELECT pg_size_pretty(pg_table_size('object_property'));
 pg_size_pretty 
----------------
 475 MB
(1 row)

Retrieving the size of an index:

postgres=# SELECT pg_size_pretty(pg_table_size('object_property_pkey'));
 pg_size_pretty 
----------------
 5008 kB
(1 row)

Attempting to retrieve the size of a non-existent relation:

postgres=# SELECT pg_table_size('foo');
ERROR:  relation "foo" does not exist
LINE 1: SELECT pg_table_size('foo');

Categories

System function

See also

pg_relation_size(), pg_total_relation_size(), pg_indexes_size()