From 5e45211a64149b3c659b90ff2de6fa982a5a93ed Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 14:17:33 +0200 Subject: Adding upstream version 15.5. Signed-off-by: Daniel Baumann --- doc/src/sgml/diskusage.sgml | 144 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 144 insertions(+) create mode 100644 doc/src/sgml/diskusage.sgml (limited to 'doc/src/sgml/diskusage.sgml') diff --git a/doc/src/sgml/diskusage.sgml b/doc/src/sgml/diskusage.sgml new file mode 100644 index 0000000..7546758 --- /dev/null +++ b/doc/src/sgml/diskusage.sgml @@ -0,0 +1,144 @@ + + + + Monitoring Disk Usage + + + This chapter discusses how to monitor the disk usage of a + PostgreSQL database system. + + + + Determining Disk Usage + + + 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 ). 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 . + + + + You can monitor disk space in three ways: + using the SQL functions listed in , + using the 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 + + + + + + Disk Full Failure + + + The most important disk monitoring task of a database administrator + is to make sure the disk doesn't become full. A filled data disk will + not result in data corruption, but it might prevent useful activity + from occurring. If the disk holding the WAL files grows full, database + server panic and consequent shutdown might occur. + + + + If you cannot free up additional space on the disk by deleting + other things, you can move some of the database files to other file + systems by making use of tablespaces. See for more information about that. + + + + + Some file systems perform badly when they are almost full, so do + not wait until the disk is completely full to take action. + + + + + If your system supports per-user disk quotas, then the database + will naturally be subject to whatever quota is placed on the user + the server runs as. Exceeding the quota will have the same bad + effects as running out of disk space entirely. + + + -- cgit v1.2.3