我要评论1、应用场景
场景1：查看数据库占用空间大小
select pg_size_pretty(pg_database_size('database_name'));场景2：查看每张表占用空间大小
select
table_schema || '.' || table_name as table,
#仅表数据
pg_size_pretty(pg_relation_size(table_schema || '.' || table_name)) as size
#表数据+索引数据
#pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) as size
from information_schema.tables
where
table_schema = 'public'
order by
pg_relation_size(table_schema || '.' || table_name) desc;
#pg_total_relation_size(table_schema || '.' || table_name) desc;查看特定表占用大小， 可用：
#仅表数据
select pg_size_pretty(pg_relation_size('schemal_test.table_test'));
#表数据+索引数据
select pg_size_pretty(pg_total_relation_size('schemal_test.table_test'));2、postgresql 空间大小知多少
表空间(table space)
#查找 postgresql 表空间大小
select pg_size_pretty (pg_tablespace_size ('tablespace_name'));
#所有表空间的名称和大小
select spcname, pg_size_pretty(pg_tablespace_size(spcname)) as size from pg_tablespace;
数据库(database)
#查找单个 postgresql 数据库大小
select pg_size_pretty(pg_database_size('db_name'));
#所有数据库的总大小，以易读的格式显示
select pg_size_pretty(sum(pg_database_size(datname))) from pg_database;
#查看所有数据库的列表及其大小（以 gb 为单位），降序
select
pg_database.datname as db_name,
pg_database_size(pg_database.datname)/1024/1024/1024 as db_size
from pg_database order by db_size desc;
#或 pg_size_pretty用修饰大小
select
pg_database.datname as db_name,
pg_size_pretty(pg_database_size(pg_database.datname)) as db_size
from pg_database order by pg_database_size(pg_database.datname) desc;
#查看所有数据库的名称、所有者以及它们各自的大小
select
db.datname as db_name,
pg_catalog.pg_get_userbyid(db.datdba) as owner,
case
when pg_catalog.has_database_privilege(db.datname, 'connect') then
pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(db.datname))
else 'no access'
end as size
from pg_catalog.pg_database db
order by case
when pg_catalog.has_database_privilege(db.datname, 'connect') then
pg_catalog.pg_database_size(db.datname)
end;
模式(schema)
#当前模式或任何模式中所有表的大小、表相关对象的大小以及总表大小 select stats.relname as table_name, pg_size_pretty(pg_relation_size(statios.relid)) as table_size, pg_size_pretty(pg_total_relation_size(statios.relid) - pg_relation_size(statios.relid)) as external_size, pg_size_pretty(pg_total_relation_size(statios.relid)) as total_table_size, stats.n_live_tup as live_rows from pg_catalog.pg_statio_user_tables as statios join pg_stat_user_tables as stats using (relname) where stats.schemaname = 'schema_name' -- 替换成模式名称 union all select 'total' as table_name, pg_size_pretty(sum(pg_relation_size(statios.relid))) as table_size, pg_size_pretty(sum(pg_total_relation_size(statios.relid) - pg_relation_size(statios.relid))) as external_size, pg_size_pretty(sum(pg_total_relation_size(statios.relid))) as total_table_size, sum(stats.n_live_tup) as live_rows from pg_catalog.pg_statio_user_tables as statios join pg_stat_user_tables as stats using (relname) where stats.schemaname = 'schema_name' -- 替换成模式名称 order by live_rows asc;
表(table-relation)
#查看postgresql 数据库的单个表大小-不包括依赖项大小：
select pg_size_pretty(pg_relation_size('schema_test.table_name'));
#select pg_size_pretty(pg_relation_size('table_name'));
#查看postgresql 数据库的单个表大小-包括依赖项大小：
select pg_size_pretty(pg_total_relation_size('schema_test.table_name'));
#select pg_size_pretty(pg_total_relation_size('table_name'));
#查找当前数据库中每张表大小，包含索引
select
table_schema || '.' || table_name as table_name,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) as table_size
from information_schema.tables
order by
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') desc
#查找当前数据库中每张表和索引大小，包含索引
select
table_name,
pg_size_pretty(pg_table_size(table_name)) as table_size,
pg_size_pretty(pg_indexes_size(table_name)) as index_size,
pg_size_pretty(pg_total_relation_size(table_name)) as total_size
from (
select ('"' || table_schema || '"."' || table_name || '"') as table_name from information_schema.tables) as tables
order by 4 desc
#查看表大小以及依赖项大小
select schemaname as schema_name,
relname as table_name,
pg_size_pretty(pg_total_relation_size(relid)) as table_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as external_size
from pg_catalog.pg_statio_user_tables
order by pg_total_relation_size(relid) desc;
#查看所有表的行数
select relname as table_name, reltuples as rows from pg_class where relkind = ‘r' order by rowcounts desc索引(index-relation)
#postgresql数据库的单个索引大小：
select pg_size_pretty(pg_indexes_size('index_name'));
#列出数据库中每个索引的大小
select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes;
列(column)
#postgresql 列值大小, 要查找存储特定值需要多少空间，可以使用 pg_column_size() 函数，例如： select pg_column_size(5::smallint); select pg_column_size(5::int); select pg_column_size(5::bigint);
#获取oid
select * from pg_class where relname='table_name';
select oid, datname from pg_database;
#查看文件地址
select pg_relation_filepath('table_name');函数说明
| 函数名 | 返回类型 | 描述 |
| pg_column_size(any) | int | 存储一个指定的数值需要的字节数（可能压缩过） |
| pg_database_size(oid) | bigint | 指定oid的数据库使用的磁盘空间 |
| pg_database_size(name) | bigint | 指定名称的数据库使用的磁盘空间 |
| pg_indexes_size(regclass) | bigint | 关联指定表oid或表名的表索引的使用总磁盘空间 |
| pg_relation_size(relation regclass, fork text) | bigint | 指定oid或名的表或索引，通过指定fork('main', 'fsm' 或'vm')所使用的磁盘空间 |
| pg_relation_size(relation regclass) | bigint | pg_relation_size(..., 'main')的缩写 |
| pg_size_pretty(bigint) | text | 把以字节计算的数值转换成一个人类易读的单位 |
| pg_size_pretty(numeric) | text | 把以字节计算的数值转换成一个人类易读的单位 |
| pg_table_size(regclass) | bigint | 指定表oid或表名的表使用的磁盘空间，除去索引（但是包含toast，自由空间映射和可视映射） |
| pg_tablespace_size(oid) | bigint | 指定oid的表空间使用的磁盘空间 |
| pg_tablespace_size(name) | bigint | 指定名称的表空间使用的磁盘空间 |
| pg_total_relation_size(regclass) | bigint | 指定表oid或表名使用的总磁盘空间，包括所有索引和toast数据 |
oid获取
#获取数据表的oid
select oid,relname from pg_class where relname='table_name';
#获取数据库的oid
select oid, datname from pg_database;
#获取数据表的文件路径
select pg_relation_filepath('table_name');总结
到此这篇关于postgresql如何查看数据库及表中数据占用空间大小的文章就介绍到这了,更多相关postgresql查看数据占用空间大小内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论