如何查找Postgres/PostgreSQL表及其索引的磁盘大小

Question

我从 Oracle 转到 Postgres，想要找到一种以字节/MB/GB等单位表示的表和索引大小的方法，最好是所有表的大小。在 Oracle 中，我有一个冗长的查询，它查看 user_lobs 和 user_segments 来提供答案。

我猜在 Postgres 中，我可以使用 information_schema 表中的某些东西，但我没有看到它的具体位置。

- mmrobins

---

http://wiki.postgresql.org/wiki/Disk_Usage - Vadzim

Answer 1

尝试使用数据库对象大小函数。 以下是一个示例：

SELECT pg_size_pretty(pg_total_relation_size('"<schema>"."<table>"'));

对于所有的表格，可以使用类似以下方法：

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

编辑：以下是@phord提交的查询语句，为了方便起见:

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

我稍微修改了代码，使用pg_table_size()来包括元数据并使得各个大小加起来。

- aib

---

顺便提一下，如果有人知道如何给这个重复出现的大表达式取别名，我很乐意听听。 - aib

你不能给它取别名，但是你可以在子查询中运行它，例如： SELECT table_full_name,pg_size_pretty(size) FROM ( SELECT .. AS table_full_name, .. AS size FROM .... ) x ORDER BY size - Magnus Hagander

建议：将'"' || table_schema || '"."' || table_name || '"'改为format('%I.%I', table_schema, table_name)。 - jpmc26

如果您想以编程方式处理原始字节计数，例如为自己的JavaScript仪表板，请跳过pg_size_pretty并直接执行内部查询。 - Sergey Orshanskiy

Answer 2

展示数据库大小：

```\l+```

=> \l+
 berbatik_prd_commerce    | berbatik_prd     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 19 MB   | pg_default | 
 berbatik_stg_commerce    | berbatik_stg     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8633 kB | pg_default | 
 bursasajadah_prd         | bursasajadah_prd | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 1122 MB | pg_default | 

显示表大小：

\d+

例如：

=> \d+
 public | tuneeca_prd | table | tomcat | 8192 bytes | 
 public | tuneeca_stg | table | tomcat | 1464 kB    | 

仅适用于 psql。

(@zkutch 的回答摘要。)

- Hendy Irawan

---

如果需要查看表和索引，可以使用\dti+命令。 - tomasz

这个返回的是按名字排序，而最佳答案返回的是按大小降序排序 - Izkata

Answer 3

如果数据库名称是snort，以下句子给出了它的大小：

psql -c "\l+ snort" | awk -F "|" '{print $7}'

- zkutch

---

查看大小的最简单答案是使用一个名为“dbsize”的shell函数。 - RichVel

此外，您可以在 psql 命令中添加 -t 以避免输出中的列标题（这对于自动化非常有用），例如：psql -c "\l+ snort" -t | awk -F "|" '{print $7}'。 - bebyx

Answer 4

尝试这个：（索引大小/使用统计）

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

- Ahmed MANSOUR

Answer 5

PostgreSQL表有三个组成部分：表本身、任何在其上的索引，以及潜在的TOAST数据。这里有几个示例展示了如何以不同的方式切分和处理可用信息：http://wiki.postgresql.org/wiki/Disk_Usage

- Greg Smith

Answer 6

仅供参考，我从 @aib 那里得到了出色的答案，并稍微修改了一下：

• 仅获取 "public" 模式下的表
• 同时显示物化视图数据和索引大小

在物化视图中，我们可以使用索引来并发刷新物化视图，这样就可以在更新时使用它们。

好的，我的查询如下：

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        -- tables from 'public'
        SELECT table_name
        FROM information_schema.tables
        where table_schema = 'public' and table_type = 'BASE TABLE'
        union
        -- materialized views
        SELECT oid::regclass::text as table_name
        FROM pg_class
        WHERE relkind = 'm'
        order by table_name
    ) AS all_tables
    -- ORDER BY total_size DESC
    order by table_name
) AS pretty_sizes

- Ciges

---

我认为在“公共”子查询中，“table_name”需要转换为文本。 在进行了这种更改后，该查询对我有效。 如果没有这样做，我会在“pg_table_size”函数上得到一个通用类型错误。“table_name”的类型是“information_schema.sql_identifier”。 - David Groomes

Answer 7

请查看这个维基页面。https://wiki.postgresql.org/wiki/Disk_Usage

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a

- Uma

Answer 8

以下查询将为您提供服务。

SELECT nspname || '.' || relname AS "relation",
  pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND C.relkind <> 'i'
  AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;

请查看此链接：https://wiki.postgresql.org/wiki/Disk_Usage

- Sajeev

Answer 9

表格大小的运行百分比：

WITH 
T AS
(
SELECT table_schema, table_name, 
       pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') / (1024.0 * 1024.0) AS TABLE_SIZE_MB,
       SUM(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) OVER() / (1024.0 * 1024.0) AS TOTAL_TABLE_SIZE_MB,
       ROW_NUMBER() OVER(ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"'), table_schema, table_name) AS N
FROM   information_schema.tables
WHERE  table_type = 'BASE TABLE'
)
SELECT table_schema, table_name, CAST(TABLE_SIZE_MB AS DECIMAL(16, 2)) AS TABLE_SIZE_MB,
       CAST(TOTAL_TABLE_SIZE_MB AS DECIMAL(16, 2))  AS TOTAL_TABLE_SIZE_MB,
       CAST(100.0 * TABLE_SIZE_MB / TOTAL_TABLE_SIZE_MB AS DECIMAL(5,2)) AS PERCENT_TABLE_SIZE,
       CAST(100.0 * SUM(TABLE_SIZE_MB / TOTAL_TABLE_SIZE_MB) OVER(ORDER BY N DESC) AS DECIMAL(5,2)) AS RUNNING_PERCENT_SIZE
FROM   T
ORDER  BY 3 desc;

- SQLpro

Answer 10

尝试使用此脚本查找所有表的大小：

SELECT
    table_schema || '.' || table_name AS TableName,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS TableSize
FROM information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC

如果您想在PostgreSQL中查找其他不同脚本的大小，请访问以下网址： http://www.dbrnd.com/2015/05/how-to-find-size-of-database-and-table-in-postgresql/

- Anvesh