codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>29.1. Determining Disk Usage</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="diskusage.html" title="Chapter 29. Monitoring Disk Usage" /><link rel="next" href="disk-full.html" title="29.2. Disk Full Failure" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">29.1. Determining Disk Usage</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="diskusage.html" title="Chapter 29. Monitoring Disk Usage">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="diskusage.html" title="Chapter 29. Monitoring Disk Usage">Up</a></td><th width="60%" align="center">Chapter 29. Monitoring Disk Usage</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="disk-full.html" title="29.2. Disk Full Failure">Next</a></td></tr></table><hr /></div><div class="sect1" id="DISK-USAGE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">29.1. Determining Disk Usage <a href="#DISK-USAGE" class="id_link">#</a></h2></div></div></div><a id="id-1.6.16.3.2" class="indexterm"></a><p>3   Each table has a primary heap disk file where most of the data is4   stored. If the table has any columns with potentially-wide values,5   there also might be a <acronym class="acronym">TOAST</acronym> file associated with the table,6   which is used to store values too wide to fit comfortably in the main7   table (see <a class="xref" href="storage-toast.html" title="73.2. TOAST">Section 73.2</a>).  There will be one valid index8   on the <acronym class="acronym">TOAST</acronym> table, if present. There also might be indexes9   associated with the base table.  Each table and index is stored in a10   separate disk file — possibly more than one file, if the file would11   exceed one gigabyte.  Naming conventions for these files are described12   in <a class="xref" href="storage-file-layout.html" title="73.1. Database File Layout">Section 73.1</a>.13  </p><p>14   You can monitor disk space in three ways:15   using the SQL functions listed in <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-DBSIZE" title="Table 9.96. Database Object Size Functions">Table 9.96</a>,16   using the <a class="xref" href="oid2name.html" title="oid2name"><span class="refentrytitle">oid2name</span></a> module, or17   using manual inspection of the system catalogs.18   The SQL functions are the easiest to use and are generally recommended.19   The remainder of this section shows how to do it by inspection of the20   system catalogs.21  </p><p>22   Using <span class="application">psql</span> on a recently vacuumed or analyzed database,23   you can issue queries to see the disk usage of any table:24</p><pre class="programlisting">25SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'customer';26 27 pg_relation_filepath | relpages28----------------------+----------29 base/16384/16806     |       6030(1 row)31</pre><p>32   Each page is typically 8 kilobytes. (Remember, <code class="structfield">relpages</code>33   is only updated by <code class="command">VACUUM</code>, <code class="command">ANALYZE</code>, and34   a few DDL commands such as <code class="command">CREATE INDEX</code>.)  The file path name35   is of interest if you want to examine the table's disk file directly.36  </p><p>37   To show the space used by <acronym class="acronym">TOAST</acronym> tables, use a query38   like the following:39</p><pre class="programlisting">40SELECT relname, relpages41FROM pg_class,42     (SELECT reltoastrelid43      FROM pg_class44      WHERE relname = 'customer') AS ss45WHERE oid = ss.reltoastrelid OR46      oid = (SELECT indexrelid47             FROM pg_index48             WHERE indrelid = ss.reltoastrelid)49ORDER BY relname;50 51       relname        | relpages52----------------------+----------53 pg_toast_16806       |        054 pg_toast_16806_index |        155</pre><p>56  </p><p>57   You can easily display index sizes, too:58</p><pre class="programlisting">59SELECT c2.relname, c2.relpages60FROM pg_class c, pg_class c2, pg_index i61WHERE c.relname = 'customer' AND62      c.oid = i.indrelid AND63      c2.oid = i.indexrelid64ORDER BY c2.relname;65 66      relname      | relpages67-------------------+----------68 customer_id_index |       2669</pre><p>70  </p><p>71   It is easy to find your largest tables and indexes using this72   information:73</p><pre class="programlisting">74SELECT relname, relpages75FROM pg_class76ORDER BY relpages DESC;77 78       relname        | relpages79----------------------+----------80 bigtable             |     329081 customer             |     314482</pre><p>83  </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="diskusage.html" title="Chapter 29. Monitoring Disk Usage">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="diskusage.html" title="Chapter 29. Monitoring Disk Usage">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="disk-full.html" title="29.2. Disk Full Failure">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 29. Monitoring Disk Usage </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 29.2. Disk Full Failure</td></tr></table></div></body></html>