PostgreSQL 查看数据库及表中数据占用空间大小

fuqying

已于 2025-01-07 13:47:36 修改

阅读量3.1k

点赞数 3

CC 4.0 BY-SA版权

文章标签：数据库 postgresql oracle

于 2025-01-07 10:42:46 首次发布

本文链接：https://blog.csdn.net/weixin_47470990/article/details/144970440

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,