PostgreSQL查看表的大小

文牧之

已于 2024-07-25 22:10:21 修改

阅读量2.3k

点赞数 11

分类专栏： postgresql 文章标签： postgresql 数据库运维

于 2024-06-18 12:19:08 首次发布

本文链接：https://blog.csdn.net/lee_vincent1/article/details/139769598

版权

postgresql 专栏收录该内容

174 篇文章 5 订阅

订阅专栏

PostgreSQL查看表的大小

在 PostgreSQL 中，可以使用一系列函数和系统视图来查看表的大小。这些工具可以帮助您获取表的基本存储大小、包括索引和 TOAST 的总大小等信息。下列方法演示了如何获取这些信息。

使用函数查看表的大小

pg_relation_size()：返回表的基础存储大小（不包括索引和 TOAST 数据）。

white=# SELECT pg_relation_size('yewu1.t1');
 pg_relation_size 
------------------
             8192
(1 row)

pg_table_size()：返回表的总大小，包括基础存储和 TOAST 数据，但不包括索引。

white=# SELECT pg_table_size('yewu1.t1');
 pg_table_size 
---------------
         40960
(1 row)

pg_indexes_size()：返回表的所有索引的总大小。

white=# SELECT pg_indexes_size('yewu1.t1');
 pg_indexes_size 
-----------------
           16384
(1 row)

pg_total_relation_size()：返回表的总大小，包括基础存储、TOAST 数据和所有索引。

white=# SELECT pg_total_relation_size('yewu1.t1');
 pg_total_relation_size 
------------------------
                  57344
(1 row)

pg_size_pretty()：将大小值转换为可读格式，可以与上述函数结合使用。

white=# SELECT pg_size_pretty(pg_relation_size('yewu1.t1'));
 pg_size_pretty 
----------------
 8192 bytes
(1 row)

示例1

如何综合使用这些函数来获取表的详细存储信息

SELECT 
  pg_size_pretty(pg_relation_size('yewu1.t1')) AS base_size,
  pg_size_pretty(pg_table_size('yewu1.t1')) AS table_size,
  pg_size_pretty(pg_indexes_size('yewu1.t1')) AS indexes_size,
  pg_size_pretty(pg_total_relation_size('yewu1.t1')) AS total_size;

输出结果

white=# SELECT 
white-#   pg_size_pretty(pg_relation_size('yewu1.t1')) AS base_size,
white-#   pg_size_pretty(pg_table_size('yewu1.t1')) AS table_size,
white-#   pg_size_pretty(pg_indexes_size('yewu1.t1')) AS indexes_size,
white-#   pg_size_pretty(pg_total_relation_size('yewu1.t1')) AS total_size;
 base_size  | table_size | indexes_size | total_size 
------------+------------+--------------+------------
 8192 bytes | 40 kB      | 16 kB        | 56 kB
(1 row)

示例2

使用系统视图查看大小

可以使用系统视图 pg_class、pg_namespace 以及函数 pg_size_pretty 结合来获取数据库中所有表的大小信息。

SELECT 
  ns.nspname AS schema_name,
  cls.relname AS table_name,
  pg_size_pretty(pg_relation_size(cls.oid)) AS base_size,
  pg_size_pretty(pg_total_relation_size(cls.oid)) AS total_size
FROM 
  pg_class cls
JOIN 
  pg_namespace ns ON cls.relnamespace = ns.oid
WHERE 
  cls.relkind = 'r'  -- 仅选择普通表
  AND ns.nspname NOT IN ('pg_catalog', 'information_schema')  -- 排除系统模式
ORDER BY 
  pg_total_relation_size(cls.oid) DESC;

示例结果

white=# SELECT 
white-#   ns.nspname AS schema_name,
white-#   cls.relname AS table_name,
white-#   pg_size_pretty(pg_relation_size(cls.oid)) AS base_size,
white-#   pg_size_pretty(pg_total_relation_size(cls.oid)) AS total_size
white-# FROM 
white-#   pg_class cls
white-# JOIN 
white-#   pg_namespace ns ON cls.relnamespace = ns.oid
white-# WHERE 
white-#   cls.relkind = 'r'  -- 仅选择普通表
white-#   AND ns.nspname NOT IN ('pg_catalog', 'information_schema')  -- 排除系统模式
white-# ORDER BY 
white-#   pg_total_relation_size(cls.oid) DESC;
 schema_name |    table_name    | base_size  | total_size 
-------------+------------------+------------+------------
 public      | pgbench_accounts | 128 MB     | 150 MB
 public      | pgbench_history  | 13 MB      | 13 MB
 yewu1       | t4               | 8192 bytes | 88 kB
 public      | pgbench_tellers  | 8192 bytes | 56 kB
 yewu2       | t4               | 8192 bytes | 56 kB
 yewu1       | t1               | 8192 bytes | 56 kB
 public      | pgbench_branches | 8192 bytes | 56 kB
 yewu1       | t2               | 0 bytes    | 0 bytes
(8 rows)

示例3

通过视图查看特定数据库中所有表的大小

上述查询展示了如何在特定数据库中查看所有表的大小。如果你想仅导出一个特定模式下的表数据或包含更多详细信息，可以调整查询。

SELECT 
  ns.nspname AS schema_name,
  cls.relname AS table_name,
  pg_size_pretty(pg_relation_size(cls.oid)) AS base_size,
  pg_size_pretty(pg_total_relation_size(cls.oid)) AS total_size,
  pg_size_pretty(pg_indexes_size(cls.oid)) AS indexes_size,
  pg_size_pretty(pg_table_size(cls.oid)) AS table_size
FROM 
  pg_class cls
JOIN 
  pg_namespace ns ON cls.relnamespace = ns.oid
WHERE 
  cls.relkind = 'r'  -- 仅选择普通表
  AND ns.nspname = 'public'  -- 替换为你要查询的模式
ORDER BY 
  pg_total_relation_size(cls.oid) DESC;

输出结果

white=# SELECT 
white-#   ns.nspname AS schema_name,
white-#   cls.relname AS table_name,
white-#   pg_size_pretty(pg_relation_size(cls.oid)) AS base_size,
white-#   pg_size_pretty(pg_total_relation_size(cls.oid)) AS total_size,
white-#   pg_size_pretty(pg_indexes_size(cls.oid)) AS indexes_size,
white-#   pg_size_pretty(pg_table_size(cls.oid)) AS table_size
white-# FROM 
white-#   pg_class cls
white-# JOIN 
white-#   pg_namespace ns ON cls.relnamespace = ns.oid
white-# WHERE 
white-#   cls.relkind = 'r'  -- 仅选择普通表
white-#   AND ns.nspname = 'public'  -- 替换为你要查询的模式
white-# ORDER BY 
white-#   pg_total_relation_size(cls.oid) DESC;
 schema_name |    table_name    | base_size  | total_size | indexes_size | table_size 
-------------+------------------+------------+------------+--------------+------------
 public      | pgbench_accounts | 128 MB     | 150 MB     | 21 MB        | 128 MB
 public      | pgbench_history  | 13 MB      | 13 MB      | 0 bytes      | 13 MB
 public      | pgbench_branches | 8192 bytes | 56 kB      | 16 kB        | 40 kB
 public      | pgbench_tellers  | 8192 bytes | 56 kB      | 16 kB        | 40 kB
(4 rows)

自动化查询所有数据库中的表大小

您也可以写一个脚本来循环遍历所有数据库并查询每个数据库的表大小。例如，可以使用以下 Python 脚本：

import psycopg2
import sys

def get_table_sizes(dbname, user, password, host):
    try:
        conn = psycopg2.connect(dbname=dbname, user=user, password=password, host=host)
        cur = conn.cursor()

        query = """
        SELECT 
          ns.nspname AS schema_name,
          cls.relname AS table_name,
          pg_size_pretty(pg_relation_size(cls.oid)) AS base_size,
          pg_size_pretty(pg_total_relation_size(cls.oid)) AS total_size,
          pg_size_pretty(pg_indexes_size(cls.oid)) AS indexes_size,
          pg_size_pretty(pg_table_size(cls.oid)) AS table_size
        FROM 
          pg_class cls
        JOIN 
          pg_namespace ns ON cls.relnamespace = ns.oid
        WHERE 
          cls.relkind = 'r'  -- 仅选择普通表
          AND ns.nspname NOT IN ('pg_catalog', 'information_schema')  -- 排除系统模式
        ORDER BY
          pg_total_relation_size(cls.oid) DESC;
        """
        
        cur.execute(query)
        rows = cur.fetchall()
        for row in rows:
            print(row)

        cur.close()
        conn.close()
    except Exception as e:
        print(f"Error connecting to database {dbname}: {e}", file=sys.stderr)

# 替换为实际的数据库名称、用户名、密码和主机
databases = ["db1", "db2"]
user = "your_user"
password = "your_password"
host = "your_host"

for db in databases:
    print(f"Database: {db}")
    get_table_sizes(db, user, password, host)
    print("\n")