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 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.
