2
$PostgreSQL: pgsql/doc/src/sgml/diskusage.sgml,v 1.14 2005-01-10 00:04:38 tgl Exp $
5
<chapter id="diskusage">
6
<title>Monitoring Disk Usage</title>
9
This chapter discusses how to monitor the disk usage of a
10
<productname>PostgreSQL</> database system.
13
<sect1 id="disk-usage">
14
<title>Determining Disk Usage</Title>
16
<indexterm zone="disk-usage">
17
<primary>disk usage</primary>
21
Each table has a primary heap disk file where most of the data is
22
stored. If the table has any columns with potentially-wide values,
23
there is also a <acronym>TOAST</> file associated with the table,
24
which is used to store values too wide to fit comfortably in the main
25
table (see <xref linkend="storage-toast">). There will be one index on the
26
<acronym>TOAST</> table, if present. There may also be indexes associated
27
with the base table. Each table and index is stored in a separate disk
28
file — possibly more than one file, if the file would exceed one
29
gigabyte. Naming conventions for these files are described in <xref
30
linkend="storage-file-layout">.
34
You can monitor disk space from three places: from
35
<application>psql</> using <command>VACUUM</> information, from
36
<application>psql</> using the tools in <filename>contrib/dbsize</>, and from
37
the command line using the tools in <filename>contrib/oid2name</>. Using
38
<application>psql</> on a recently vacuumed or analyzed database,
39
you can issue queries to see the disk usage of any table:
41
SELECT relfilenode, relpages FROM pg_class WHERE relname = 'customer';
43
relfilenode | relpages
44
-------------+----------
48
Each page is typically 8 kilobytes. (Remember, <structfield>relpages</>
49
is only updated by <command>VACUUM</>, <command>ANALYZE</>, and
50
a few DDL commands such as <command>CREATE INDEX</>.) The
51
<structfield>relfilenode</> value is of interest if you want to examine
52
the table's disk file directly.
56
To show the space used by <acronym>TOAST</> tables, use a query
59
SELECT relname, relpages
61
(SELECT reltoastrelid FROM pg_class
62
WHERE relname = 'customer') ss
63
WHERE oid = ss.reltoastrelid
64
OR oid = (SELECT reltoastidxid FROM pg_class
65
WHERE oid = ss.reltoastrelid)
69
----------------------+----------
71
pg_toast_16806_index | 1
76
You can easily display index sizes, too:
78
SELECT c2.relname, c2.relpages
79
FROM pg_class c, pg_class c2, pg_index i
80
WHERE c.relname = 'customer'
81
AND c.oid = i.indrelid
82
AND c2.oid = i.indexrelid
86
----------------------+----------
87
customer_id_indexdex | 26
92
It is easy to find your largest tables and indexes using this
95
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
98
----------------------+----------
105
<filename>contrib/dbsize</> loads functions into your database that allow
106
you to find the size of a table or database from inside
107
<application>psql</> without the need for <command>VACUUM</> or <command>ANALYZE</>.
111
You can also use <filename>contrib/oid2name</> to show disk usage. See
112
<filename>README.oid2name</> in that directory for examples. It includes a script that
113
shows disk usage for each database.
117
<sect1 id="disk-full">
118
<title>Disk Full Failure</title>
121
The most important disk monitoring task of a database administrator
122
is to make sure the disk doesn't grow full. A filled data disk will
123
not result in data corruption, but it may well prevent useful activity
124
from occurring. If the disk holding the WAL files grows full, database
125
server panic and consequent shutdown may occur.
129
If you cannot free up additional space on the disk by deleting
130
other things, you can move some of the database files to other file
131
systems by making use of tablespaces. See <xref
132
linkend="manage-ag-tablespaces"> for more information about that.
137
Some file systems perform badly when they are almost full, so do
138
not wait until the disk is completely full to take action.
143
If your system supports per-user disk quotas, then the database
144
will naturally be subject to whatever quota is placed on the user
145
the server runs as. Exceeding the quota will have the same bad
146
effects as running out of space entirely.
151
<!-- Keep this comment at the end of the file
156
sgml-minimize-attributes:nil
157
sgml-always-quote-attributes:t
160
sgml-parent-document:nil
161
sgml-default-dtd-file:"./reference.ced"
162
sgml-exposed-tags:nil
163
sgml-local-catalogs:("/usr/lib/sgml/catalog")
164
sgml-local-ecat-files:nil