Postgresql硬盘空间查询

一、查看所有表，索引占用空间

-- 表、索引占用空间
SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

二、查看数据库占用空间

-- 查询指定数据库占用空间
select pg_size_pretty (pg_database_size('test_database'));

-- 查询所有数据库占用空间
select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database;

三、查看当前数据库所有表占用空间

-- 查询当前数据库所有表占用空间
select
    table_full_name,
    pg_size_pretty(size)
from
(
        SELECT
            table_schema || '.' || table_name AS table_full_name,
            pg_total_relation_size(
                '"' || table_schema || '"."' || table_name || '"'
            ) AS size
        FROM
            information_schema.tables
        ORDER BY
            size DESC
    ) as T