SELECT pg_size_pretty(pg_database_size('database_name'));

SELECT
    table_schema || '.' || table_name AS table,
    #僅表數(shù)據(jù)
    pg_size_pretty(pg_relation_size(table_schema || '.' || table_name)) AS size
    #表數(shù)據(jù)+索引數(shù)據(jù)
    #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;

#僅表數(shù)據(jù)
SELECT pg_size_pretty(pg_relation_size('schemal_test.table_test'));
#表數(shù)據(jù)+索引數(shù)據(jù)
SELECT pg_size_pretty(pg_total_relation_size('schemal_test.table_test'));

#查找 postgresql 表空間大小
SELECT pg_size_pretty (pg_tablespace_size ('tablespace_name'));

#所有表空間的名稱(chēng)和大小
SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) as size from pg_tablespace;

#查找單個(gè) postgresql 數(shù)據(jù)庫(kù)大小
SELECT pg_size_pretty(pg_database_size('db_name'));

#所有數(shù)據(jù)庫(kù)的總大小，以易讀的格式顯示
SELECT pg_size_pretty(SUM(pg_database_size(datname))) FROM pg_database;


#查看所有數(shù)據(jù)庫(kù)的列表及其大?。ㄒ?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;

#查看所有數(shù)據(jù)庫(kù)的名稱(chēng)、所有者以及它們各自的大小
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;

#當(dāng)前模式或任何模式中所有表的大小、表相關(guān)對(duì)象的大小以及總表大小
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'  -- 替換成模式名稱(chēng)
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'  -- 替換成模式名稱(chēng)
ORDER BY live_rows ASC;

#查看postgresql 數(shù)據(jù)庫(kù)的單個(gè)表大小-不包括依賴(lài)項(xiàng)大小：
SELECT pg_size_pretty(pg_relation_size('schema_test.table_name'));
#SELECT pg_size_pretty(pg_relation_size('table_name'));

#查看postgresql 數(shù)據(jù)庫(kù)的單個(gè)表大小-包括依賴(lài)項(xiàng)大?。?
SELECT pg_size_pretty(pg_total_relation_size('schema_test.table_name'));
#SELECT pg_size_pretty(pg_total_relation_size('table_name'));

#查找當(dāng)前數(shù)據(jù)庫(kù)中每張表大小，包含索引
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

#查找當(dāng)前數(shù)據(jù)庫(kù)中每張表和索引大小，包含索引
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

#查看表大小以及依賴(lài)項(xiàng)大小
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;

#查看所有表的行數(shù)
select relname as table_name, reltuples as rows from pg_class where relkind = ‘r' order by rowCounts desc

#postgresql數(shù)據(jù)庫(kù)的單個(gè)索引大?。?
SELECT pg_size_pretty(pg_indexes_size('index_name'));

#列出數(shù)據(jù)庫(kù)中每個(gè)索引的大小
SELECT indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes;

#PostgreSQL 列值大小, 要查找存儲(chǔ)特定值需要多少空間，可以使用 pg_column_size() 函數(shù)，例如：
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');

#獲取數(shù)據(jù)表的OID
select oid,relname from pg_class where relname='table_name';
#獲取數(shù)據(jù)庫(kù)的OID
select oid, datname from pg_database;
#獲取數(shù)據(jù)表的文件路徑
select pg_relation_filepath('table_name');