29.1. Determining Disk Usage

29.1. Determining Disk Usage
Prev	Up	Chapter 29. Monitoring Disk Usage	Home	Next

29.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 Section 73.2). There will be one valid 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 Section 73.1. +

+ You can monitor disk space in three ways: + using the SQL functions listed in Table 9.96, + using the oid2name module, or + using manual inspection of the system catalogs. + The SQL functions are the easiest to use and are generally recommended. + 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, relpages + is only updated by VACUUM, ANALYZE, and + a few 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 indexrelid
+             FROM pg_index
+             WHERE indrelid = 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_index |       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
+