|The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 274 pages
RRP £9.95 ($14.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
14.1 Determining Disk Usage
Each table has a primary heap disk file where most of the data is stored. If the table has any columns with potentially-wide values, there also might be a TOAST file associated with the table, which is used to store values too wide to fit comfortably in the main table (see TOAST). There will be one index on the TOAST table, if present. There also might be indexes associated with the base table. Each table and index is stored in a separate disk file--possibly more than one file, if the file would exceed one gigabyte. Naming conventions for these files are described in Database File Layout.
You can monitor disk space in three ways: using the SQL functions listed in Volume 1A: Table 7-58, using the tools in ‘contrib/oid2name’, or using manual inspection of the system catalogs. The SQL functions are the easiest to use and are generally recommended. ‘contrib/oid2name’ is described in oid2name. The remainder of this section shows how to do it by inspection of the system catalogs.
Using psql on a recently vacuumed or analyzed database, you can issue queries to see the disk usage of any table:
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'customer'; pg_relation_filepath | relpages ----------------------+---------- base/16384/16806 | 60 (1 row)
Each page is typically 8 kilobytes. (Remember,
is only updated by
a few data definition language (DDL) commands such as
CREATE INDEX.) The file path name
is of interest if you want to examine the table's disk file directly.
To show the space used by TOAST tables, use a query like the following:
SELECT relname, relpages FROM pg_class, (SELECT reltoastrelid FROM pg_class WHERE relname = 'customer') AS ss WHERE oid = ss.reltoastrelid OR oid = (SELECT reltoastidxid FROM pg_class WHERE oid = ss.reltoastrelid) ORDER BY relname; relname | relpages ----------------------+---------- pg_toast_16806 | 0 pg_toast_16806_index | 1
You can easily display index sizes, too:
SELECT c2.relname, c2.relpages FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = 'customer' AND c.oid = i.indrelid AND c2.oid = i.indexrelid ORDER BY c2.relname; relname | relpages ----------------------+---------- customer_id_indexdex | 26
It is easy to find your largest tables and indexes using this information:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC; relname | relpages ----------------------+---------- bigtable | 3290 customer | 3144
|ISBN 9781906966072||The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide||See the print edition|