PostgreSQL查看表的大小

PostgreSQL查看表的大小

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

使用函数查看表的大小

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

SELECT pg_relation_size('your_table_name');

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

SELECT pg_table_size('your_table_name');

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

SELECT pg_indexes_size('your_table_name');

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

SELECT pg_total_relation_size('your_table_name');

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

SELECT pg_size_pretty(pg_relation_size('your_table_name'));

示例查询

以下是一个综合示例，演示如何使用这些函数来获取表的详细存储信息。

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

示例结果

 base_size | table_size | indexes_size | total_size
-----------+------------+--------------+------------
 8192 bytes | 8192 bytes | 16384 bytes  | 24576 bytes

使用系统视图查看大小

可以使用系统视图 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;

示例结果

 schema_name | table_name | base_size | total_size
-------------+------------+-----------+------------
 public      | large_table| 1024 MB   | 2048 MB
 public      | small_table| 128 MB    | 256 MB

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

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

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;

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

您也可以写一个脚本来循环遍历所有数据库并查询每个数据库的表大小。例如，可以使用以下 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")

通过这种方式，可以轻松自动化地获取多个数据库中所有表的大小信息。

总结

使用 PostgreSQL 提供的函数和系统视图，可以有效地获取数据库中表的各种尺寸信息。这对于数据库管理、性能优化和容量规划非常有用。如果有更复杂的需求或遇到任何问题，随时提问！