doc/src/sgml/diskusage.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <chapter id="diskusage">
   4  <title>Monitoring Disk Usage</title>
   5
   6  <para>
   7   This chapter discusses how to monitor the disk usage of a
   8   <productname>PostgreSQL</> database system.
   9  </para>
  10
  11  <sect1 id="disk-usage">
  12   <title>Determining Disk Usage</Title>
  13
  14   <indexterm zone="disk-usage">
  15    <primary>disk usage</primary>
  16   </indexterm>
  17
  18   <para>
  19    Each table has a primary heap disk file where most of the data is
  20    stored. If the table has any columns with potentially-wide values,
  21    there is also a <acronym>TOAST</> file associated with the table,
  22    which is used to store values too wide to fit comfortably in the main
  23    table (see <xref linkend="storage-toast">).  There will be one index on the
  24    <acronym>TOAST</> table, if present. There might also be indexes associated
  25    with the base table.  Each table and index is stored in a separate disk
  26    file &mdash; possibly more than one file, if the file would exceed one
  27    gigabyte.  Naming conventions for these files are described in <xref
  28    linkend="storage-file-layout">.
  29   </para>
  30
  31   <para>
  32    You can monitor disk space from three ways:  using
  33    SQL functions listed in <xref linkend="functions-admin-dbsize">,
  34    using <command>VACUUM</> information, and from the command line
  35    using the tools in <filename>contrib/oid2name</>.  The SQL functions
  36    are the easiest to use and report information about tables, tables with
  37    indexes and long value storage (TOAST), databases, and tablespaces.
  38   </para>
  39
  40   <para>
  41    Using <application>psql</> on a recently vacuumed or analyzed database,
  42    you can issue queries to see the disk usage of any table:
  43 <programlisting>
  44 SELECT relfilenode, relpages FROM pg_class WHERE relname = 'customer';
  45
  46  relfilenode | relpages
  47 -------------+----------
  48        16806 |       60
  49 (1 row)
  50 </programlisting>
  51    Each page is typically 8 kilobytes. (Remember, <structfield>relpages</>
  52    is only updated by <command>VACUUM</>, <command>ANALYZE</>, and
  53    a few DDL commands such as <command>CREATE INDEX</>.)  The
  54    <structfield>relfilenode</> value is of interest if you want to examine
  55    the table's disk file directly.
  56   </para>
  57
  58   <para>
  59    To show the space used by <acronym>TOAST</> tables, use a query
  60    like the following:
  61 <programlisting>
  62 SELECT relname, relpages
  63     FROM pg_class,
  64          (SELECT reltoastrelid FROM pg_class
  65           WHERE relname = 'customer') ss
  66     WHERE oid = ss.reltoastrelid
  67        OR oid = (SELECT reltoastidxid FROM pg_class
  68                  WHERE oid = ss.reltoastrelid)
  69     ORDER BY relname;
  70
  71        relname        | relpages
  72 ----------------------+----------
  73  pg_toast_16806       |        0
  74  pg_toast_16806_index |        1
  75 </programlisting>
  76   </para>
  77
  78   <para>
  79    You can easily display index sizes, too:
  80 <programlisting>
  81 SELECT c2.relname, c2.relpages
  82     FROM pg_class c, pg_class c2, pg_index i
  83     WHERE c.relname = 'customer'
  84         AND c.oid = i.indrelid
  85         AND c2.oid = i.indexrelid
  86     ORDER BY c2.relname;
  87
  88        relname        | relpages
  89 ----------------------+----------
  90  customer_id_indexdex |       26
  91 </programlisting>
  92   </para>
  93
  94   <para>
  95    It is easy to find your largest tables and indexes using this
  96    information:
  97 <programlisting>
  98 SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
  99
 100        relname        | relpages
 101 ----------------------+----------
 102  bigtable             |     3290
 103  customer             |     3144
 104 </programlisting>
 105   </para>
 106
 107   <para>
 108    You can also use <filename>contrib/oid2name</> to show disk usage. See
 109    <filename>README.oid2name</> in that directory for examples. It includes a script that
 110    shows disk usage for each database.
 111   </para>
 112  </sect1>
 113
 114  <sect1 id="disk-full">
 115   <title>Disk Full Failure</title>
 116
 117   <para>
 118    The most important disk monitoring task of a database administrator
 119    is to make sure the disk doesn't grow full.  A filled data disk will
 120    not result in data corruption, but it might prevent useful activity
 121    from occurring. If the disk holding the WAL files grows full, database
 122    server panic and consequent shutdown might occur.
 123   </para>
 124
 125   <para>
 126    If you cannot free up additional space on the disk by deleting
 127    other things, you can move some of the database files to other file
 128    systems by making use of tablespaces. See <xref
 129    linkend="manage-ag-tablespaces"> for more information about that.
 130   </para>
 131
 132   <tip>
 133    <para>
 134     Some file systems perform badly when they are almost full, so do
 135     not wait until the disk is completely full to take action.
 136    </para>
 137   </tip>
 138
 139   <para>
 140    If your system supports per-user disk quotas, then the database
 141    will naturally be subject to whatever quota is placed on the user
 142    the server runs as.  Exceeding the quota will have the same bad
 143    effects as running out of space entirely.
 144   </para>
 145  </sect1>
 146 </chapter>