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.
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.
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.
The source code for
pg_table_size() is contained in src/backend/utils/adt/dbsize.c; the main work is carried out by
Basic usage example for
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');