IT评测·应用市场-qidao123.com

标题: PostgreSQL检察表的大小 [打印本页]
作者: 去皮卡多    时间: 2024-7-29 14:15
标题: PostgreSQL检察表的大小
PostgreSQL检察表的大小

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

  1. white=# SELECT pg_relation_size('yewu1.t1');
  2. pg_relation_size
  3. ------------------
  4.              8192
  5. (1 row)
复制代码
  1. white=# SELECT pg_table_size('yewu1.t1');
  2. pg_table_size
  3. ---------------
  4.          40960
  5. (1 row)
复制代码
  1. white=# SELECT pg_indexes_size('yewu1.t1');
  2. pg_indexes_size
  3. -----------------
  4.            16384
  5. (1 row)
复制代码
  1. white=# SELECT pg_total_relation_size('yewu1.t1');
  2. pg_total_relation_size
  3. ------------------------
  4.                   57344
  5. (1 row)
复制代码
  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企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/) Powered by Discuz! X3.4