Postgresql 中文操作指南

29.1. Determining Disk Usage #

每个表都有一个主堆栈磁盘文件，其中存储了大部分的数据。如果表有具有潜在宽值的任何列，则可能还会与表关联一个 TOAST 文件，该文件用于存储在主表中难以轻松容纳的值（参见 Section 73.2）。如果存在，TOAST 表上将有一个有效的索引。也可能会有与基础表关联的索引。每个表和索引存储在单独的磁盘文件中 — 可能有多个文件，如果文件将超过一个千兆字节。这些文件的命名约定在 Section 73.1中描述。

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 Section 73.2). 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 Section 73.1.

可以通过三种方式来监控磁盘空间：使用 Table 9.96 中列出的 SQL 函数，使用 oid2name 模块或手动检查系统目录。SQL 函数最易于使用，并且通常建议使用。此部分的其余内容显示了如何通过检查系统目录来执行此操作。

You can monitor disk space in three ways: using the SQL functions listed in Table 9.96, using the oid2name 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.

在最近已进行 vacuum 或分析的数据库上使用 psql，你可以发出查询以查看任何表所使用的磁盘：

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)

每个页面通常为 8 KB。（请记住，relpages 仅通过 VACUUM、ANALYZE，和少数几个诸如 CREATE INDEX 的 DDL 命令来更新。）如果你想直接检查表的磁盘文件，那么该文件路径名称将会特别有用。

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.

要显示 TOAST 表所使用的空间，请使用如下的查询：

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