PostgreSQL检察表的大小

PostgreSQL检察表的大小

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

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

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

复制代码

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

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

复制代码

• pg_indexes_size()：返回表的全部索引的总大小。
  

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

复制代码

• pg_total_relation_size()：返回表的总大小，包括底子存储、TOAST 数据和全部索引。
  

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

复制代码

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

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

复制代码

示例1

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

1. SELECT
2.   pg_size_pretty(pg_relation_size('yewu1.t1')) AS base_size,
3.   pg_size_pretty(pg_table_size('yewu1.t1')) AS table_size,
4.   pg_size_pretty(pg_indexes_size('yewu1.t1')) AS indexes_size,
5.   pg_size_pretty(pg_total_relation_size('yewu1.t1')) AS total_size;

复制代码

输出效果

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

复制代码

示例2

利用体系视图检察大小
可以利用体系视图 pg_class、pg_namespace 以及函数 pg_size_pretty 联合来获取数据库中全部表的大小信息。

1. SELECT
2.   ns.nspname AS schema_name,
3.   cls.relname AS table_name,
4.   pg_size_pretty(pg_relation_size(cls.oid)) AS base_size,
5.   pg_size_pretty(pg_total_relation_size(cls.oid)) AS total_size
6. FROM
7.   pg_class cls
8. JOIN
9.   pg_namespace ns ON cls.relnamespace = ns.oid
10. WHERE
11.   cls.relkind = 'r'  -- 仅选择普通表
12.   AND ns.nspname NOT IN ('pg_catalog', 'information_schema')  -- 排除系统模式
13. ORDER BY
14.   pg_total_relation_size(cls.oid) DESC;

复制代码

示例效果

1. white=# SELECT
2. white-#   ns.nspname AS schema_name,
3. white-#   cls.relname AS table_name,
4. white-#   pg_size_pretty(pg_relation_size(cls.oid)) AS base_size,
5. white-#   pg_size_pretty(pg_total_relation_size(cls.oid)) AS total_size
6. white-# FROM
7. white-#   pg_class cls
8. white-# JOIN
9. white-#   pg_namespace ns ON cls.relnamespace = ns.oid
10. white-# WHERE
11. white-#   cls.relkind = 'r'  -- 仅选择普通表
12. white-#   AND ns.nspname NOT IN ('pg_catalog', 'information_schema')  -- 排除系统模式
13. white-# ORDER BY
14. white-#   pg_total_relation_size(cls.oid) DESC;
15. schema_name |    table_name    | base_size  | total_size
16. -------------+------------------+------------+------------
17. public      | pgbench_accounts | 128 MB     | 150 MB
18. public      | pgbench_history  | 13 MB      | 13 MB
19. yewu1       | t4               | 8192 bytes | 88 kB
20. public      | pgbench_tellers  | 8192 bytes | 56 kB
21. yewu2       | t4               | 8192 bytes | 56 kB
22. yewu1       | t1               | 8192 bytes | 56 kB
23. public      | pgbench_branches | 8192 bytes | 56 kB
24. yewu1       | t2               | 0 bytes    | 0 bytes
25. (8 rows)

复制代码

示例3

通过视图检察特定命据库中全部表的大小
上述查询展示了如安在特定命据库中检察全部表的大小。假如你想仅导出一个特定模式下的表数据或包含更多详细信息，可以调整查询。

1. SELECT
2.   ns.nspname AS schema_name,
3.   cls.relname AS table_name,
4.   pg_size_pretty(pg_relation_size(cls.oid)) AS base_size,
5.   pg_size_pretty(pg_total_relation_size(cls.oid)) AS total_size,
6.   pg_size_pretty(pg_indexes_size(cls.oid)) AS indexes_size,
7.   pg_size_pretty(pg_table_size(cls.oid)) AS table_size
8. FROM
9.   pg_class cls
10. JOIN
11.   pg_namespace ns ON cls.relnamespace = ns.oid
12. WHERE
13.   cls.relkind = 'r'  -- 仅选择普通表
14.   AND ns.nspname = 'public'  -- 替换为你要查询的模式
15. ORDER BY
16.   pg_total_relation_size(cls.oid) DESC;

复制代码

输出效果

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

复制代码

自动化查询全部数据库中的表大小

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

1. import psycopg2
2. import sys
4. def get_table_sizes(dbname, user, password, host):
5.     try:
6.         conn = psycopg2.connect(dbname=dbname, user=user, password=password, host=host)
7.         cur = conn.cursor()
9.         query = """
10.         SELECT
11.           ns.nspname AS schema_name,
12.           cls.relname AS table_name,
13.           pg_size_pretty(pg_relation_size(cls.oid)) AS base_size,
14.           pg_size_pretty(pg_total_relation_size(cls.oid)) AS total_size,
15.           pg_size_pretty(pg_indexes_size(cls.oid)) AS indexes_size,
16.           pg_size_pretty(pg_table_size(cls.oid)) AS table_size
17.         FROM
18.           pg_class cls
19.         JOIN
20.           pg_namespace ns ON cls.relnamespace = ns.oid
21.         WHERE
22.           cls.relkind = 'r'  -- 仅选择普通表
23.           AND ns.nspname NOT IN ('pg_catalog', 'information_schema')  -- 排除系统模式
24.         ORDER BY
25.           pg_total_relation_size(cls.oid) DESC;
26.         """
27.         
28.         cur.execute(query)
29.         rows = cur.fetchall()
30.         for row in rows:
31.             print(row)
33.         cur.close()
34.         conn.close()
35.     except Exception as e:
36.         print(f"Error connecting to database {dbname}: {e}", file=sys.stderr)
38. # 替换为实际的数据库名称、用户名、密码和主机
39. databases = ["db1", "db2"]
40. user = "your_user"
41. password = "your_password"
42. host = "your_host"
44. for db in databases:
45.     print(f"Database: {db}")
46.     get_table_sizes(db, user, password, host)
47.     print("\n")

复制代码

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

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。