This chapter discusses how to monitor the disk usage of a PostgreSQL database system. In the current release, the database administrator does not have much control over the on-disk storage layout, so this chapter is mostly informative and can give you some ideas how to manage the disk usage with operating system tools.
Each table has a primary heap disk file where most of the data is stored. To store long column values, there is also a TOAST file associated with the table, named based on the table's OID (actually pg_class.relfilenode), and an index on the TOAST table. There also may be indexes associated with the base table.
You can monitor disk space from three places: from psql using VACUUM information, from psql using the tools in contrib/dbsize, and from the command line using the tools in contrib/oid2name. Using psql on a recently vacuumed or analyzed database, you can issue queries to see the disk usage of any table:
SELECT relfilenode, relpages FROM pg_class WHERE relname = 'customer'; relfilenode | relpages -------------+---------- 16806 | 60 (1 row)
Each page is typically 8 kilobytes. (Remember, relpages is only updated by VACUUM and ANALYZE.)
To show the space used by TOAST tables, use a query like the following, substituting the relfilenode number of the heap (determined by the query above):
SELECT relname, relpages FROM pg_class WHERE relname = 'pg_toast_16806' OR relname = 'pg_toast_16806_index' 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
contrib/dbsize loads functions into your database that allow you to find the size of a table or database from inside psql without the need for VACUUM or ANALYZE.
You can also use contrib/oid2name to show disk usage. See README.oid2name in that directory for examples. It includes a script that shows disk usage for each database.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.