Monitoring Disk Usage

Chapter 11. Monitoring Disk Usage

Table of Contents
11.1. Determining Disk Usage
11.2. Disk Full Failure

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.

11.1. Determining Disk Usage

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 contrib/dbsize, and from the command line using contrib/oid2name. Using psql on a recently vacuumed (or analyzed) database, you can issue queries to see the disk usage of any table:

play=# SELECT relfilenode, relpages
play-# FROM pg_class
play-# 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 based on the heap relfilenode shown above:

play=# SELECT relname, relpages
play-# FROM pg_class
play-# WHERE relname = 'pg_toast_16806' OR
play-#       relname = 'pg_toast_16806_index'
play-# ORDER BY relname;
       relname        | relpages 
 pg_toast_16806       |        0
 pg_toast_16806_index |        1

You can easily display index usage too:

play=# SELECT c2.relname, c2.relpages
play-# FROM pg_class c, pg_class c2, pg_index i
play-# WHERE c.relname = 'customer' AND
play-#       c.oid = i.indrelid AND
play-#       c2.oid = i.indexrelid
play-# ORDER BY c2.relname;
       relname        | relpages 
 customer_id_indexdex |       26

It is easy to find your largest files using psql:

play=# SELECT relname, relpages
play-# FROM pg_class
play-# 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/ANALYZE.

You can also use contrib/oid2name to show disk usage. See README.oid2name for examples. It includes a script that shows disk usage for each database.

© Copyright 2003-2023 The ultimate PHP Editor and PHP IDE site.