PostgreSQL 7.4.3 Documentation | ||||
---|---|---|---|---|
Prev | Fast Backward | Fast Forward | Next |
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.