PostgreSQL如何查看数据库及表中数据占用空间大小详解
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">1、应用场景</a></li><li><a href="#_label1">2、PostgreSQL 空间大小知多少</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">表空间(Table Space)</a></li><li><a href="#_lab2_1_1">数据库(Database)</a></li><li><a href="#_lab2_1_2">模式(Schema)</a></li><li><a href="#_lab2_1_3">表(Table-Relation)</a></li><li><a href="#_lab2_1_4">索引(Index-Relation)</a></li><li><a href="#_lab2_1_5">列(Column)</a></li></ul><li><a href="#_label2">函数说明</a></li><ul class="second_class_ul"></ul><li><a href="#_label3">oid获取</a></li><ul class="second_class_ul"></ul><li><a href="#_label4">总结 </a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>1、应用场景</h2><p>场景1：查看数据库占用空间大小</p>
<div class="jb51code"><pre class="brush:sql;">SELECT pg_size_pretty(pg_database_size('database_name'));</pre></div>
<p>场景2：查看每张表占用空间大小</p>
<div class="jb51code"><pre class="brush:sql;">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;</pre></div>
<p>查看特定表占用大小， 可用：</p>
<div class="jb51code"><pre class="brush:sql;">#仅表数据
SELECT pg_size_pretty(pg_relation_size('schemal_test.table_test'));
#表数据+索引数据
SELECT pg_size_pretty(pg_total_relation_size('schemal_test.table_test'));</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>2、PostgreSQL 空间大小知多少</h2>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>表空间(Table Space)</h3>
<div class="jb51code"><pre class="brush:sql;">#查找 postgresql 表空间大小
SELECT pg_size_pretty (pg_tablespace_size ('tablespace_name'));
#所有表空间的名称和大小
SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) as size from pg_tablespace;
</pre></div>
<p class="maodian"><a name="_lab2_1_1"></a></p><h3>数据库(Database)</h3>
<div class="jb51code"><pre class="brush:sql;">#查找单个 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;
</pre></div>
<p class="maodian"><a name="_lab2_1_2"></a></p><h3>模式(Schema)</h3>
<div class="jb51code"><pre class="brush:sql;">#当前模式或任何模式中所有表的大小、表相关对象的大小以及总表大小
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;</pre></div>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>表(Table-Relation)</h3>
<div class="jb51code"><pre class="brush:sql;">#查看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</pre></div>
<p class="maodian"><a name="_lab2_1_4"></a></p><h3>索引(Index-Relation)</h3>
<div class="jb51code"><pre class="brush:sql;">#postgresql数据库的单个索引大小：
SELECT pg_size_pretty(pg_indexes_size('index_name'));
#列出数据库中每个索引的大小
SELECT indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes;
</pre></div>
<p class="maodian"><a name="_lab2_1_5"></a></p><h3>列(Column)</h3>
<div class="jb51code"><pre class="brush:sql;">#PostgreSQL 列值大小, 要查找存储特定值需要多少空间，可以使用 pg_column_size() 函数，例如：
select pg_column_size(5::smallint);
select pg_column_size(5::int);
select pg_column_size(5::bigint);</pre></div>
<div class="jb51code"><pre class="brush:sql;">#获取OID
select * from pg_class where relname='table_name';
select oid, datname from pg_database;
#查看文件地址
select pg_relation_filepath('table_name');</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>函数说明</h2>
<table><tbody><tr><td><strong>函数名</strong></td><td><strong>返回类型</strong></td><td><strong>描述</strong></td></tr><tr><td>pg_column_size(any)</td><td>int</td><td>存储一个指定的数值需要的字节数（可能压缩过）</td></tr><tr><td>pg_database_size(oid)</td><td>bigint</td><td>指定OID的数据库使用的磁盘空间</td></tr><tr><td>pg_database_size(name)</td><td>bigint</td><td>指定名称的数据库使用的磁盘空间</td></tr><tr><td>pg_indexes_size(regclass)</td><td>bigint</td><td>关联指定表OID或表名的表索引的使用总磁盘空间</td></tr><tr><td>pg_relation_size(relation regclass, fork text)</td><td>bigint</td><td>指定OID或名的表或索引，通过指定fork('main', 'fsm' 或'vm')所使用的磁盘空间</td></tr><tr><td>pg_relation_size(relation regclass)</td><td>bigint</td><td>pg_relation_size(..., 'main')的缩写</td></tr><tr><td>pg_size_pretty(bigint)</td><td>text</td><td>把以字节计算的数值转换成一个人类易读的单位</td></tr><tr><td>pg_size_pretty(numeric)</td><td>text</td><td>把以字节计算的数值转换成一个人类易读的单位</td></tr><tr><td>pg_table_size(regclass)</td><td>bigint</td><td>指定表OID或表名的表使用的磁盘空间，除去索引（但是包含TOAST，自由空间映射和可视映射）</td></tr><tr><td>pg_tablespace_size(oid)</td><td>bigint</td><td>指定OID的表空间使用的磁盘空间</td></tr><tr><td>pg_tablespace_size(name)</td><td>bigint</td><td>指定名称的表空间使用的磁盘空间</td></tr><tr><td>pg_total_relation_size(regclass)</td><td>bigint</td><td>指定表OID或表名使用的总磁盘空间，包括所有索引和TOAST数据</td></tr></tbody></table>
<p class="maodian"><a name="_label3"></a></p><h2>oid获取</h2>
<div class="jb51code"><pre class="brush:sql;">#获取数据表的OID
select oid,relname from pg_class where relname='table_name';
#获取数据库的OID
select oid, datname from pg_database;
#获取数据表的文件路径
select pg_relation_filepath('table_name');</pre></div>
<p class="maodian"><a name="_label4"></a></p><h2>总结 </h2>
頁:
[1]