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 relnametable_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)bigintpg_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 relnametable_name; #获取数据库的OID select oid, datname from pg_database; #获取数据表的文件路径 select pg_relation_filepath(table_name);