1、應用場景

場景1：查看數據庫占用空間大小

SELECT pg_size_pretty(pg_database_size('database_name'));

場景2：查看每張表占用空間大小

SELECTtable_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 BYpg_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 為單位），降序
SELECTpg_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用修飾大小
SELECTpg_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,CASEWHEN pg_catalog.has_database_privilege(db.datname, 'CONNECT') THENpg_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 CASEWHEN pg_catalog.has_database_privilege(db.datname, 'CONNECT') THENpg_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#查找當前數據庫中每張表和索引大小，包含索引
SELECTtable_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');