PostgreSQL查看表的大小
在 PostgreSQL 中，可以使用一系列函数和系统视图来查看表的大小。这些工具可以帮助您获取表的基本存储大小、包括索引和 TOAST 的总大小等信息。下列方法演示了如何获取这些信息。
使用函数查看表的大小
- **
pg_relation_size()**：返回表的基础存储大小（不包括索引和 TOAST 数据）。
white=# SELECT pg_relation_size('yewu1.t1');
pg_relation_size
------------------8192(1row)
- **
pg_table_size()**：返回表的总大小，包括基础存储和 TOAST 数据，但不包括索引。
white=# SELECT pg_table_size('yewu1.t1');
pg_table_size
---------------40960(1row)
- **
pg_indexes_size()**：返回表的所有索引的总大小。
white=# SELECT pg_indexes_size('yewu1.t1');
pg_indexes_size
-----------------16384(1row)
- **
pg_total_relation_size()**：返回表的总大小，包括基础存储、TOAST 数据和所有索引。
white=# SELECT pg_total_relation_size('yewu1.t1');
pg_total_relation_size
------------------------57344(1row)
- **
pg_size_pretty()**：将大小值转换为可读格式，可以与上述函数结合使用。
white=# SELECT pg_size_pretty(pg_relation_size('yewu1.t1'));
pg_size_pretty
----------------8192 bytes
(1row)
示例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 NOTIN('pg_catalog','information_schema')-- 排除系统模式ORDERBY
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'-- 替换为你要查询的模式ORDERBY
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
(4rows)
自动化查询所有数据库中的表大小
您也可以写一个脚本来循环遍历所有数据库并查询每个数据库的表大小。例如，可以使用以下 Python 脚本：
import psycopg2
import sys
defget_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 提供的函数和系统视图，可以有效地获取数据库中表的各种尺寸信息。这对于数据库管理、性能优化和容量规划非常有用。如果有更复杂的需求或遇到任何问题，随时提问！
本文转载自: https://blog.csdn.net/lee_vincent1/article/details/139769598
版权归原作者 文牧之 所有， 如有侵权，请联系我们删除。
版权归原作者 文牧之 所有， 如有侵权，请联系我们删除。