掌握pg_size_pretty，轻松提升PostgreSQL性能！

创作时间:

作者:

@小白创作中心

掌握pg_size_pretty，轻松提升PostgreSQL性能！

引用

来源

https://neon.tech/postgresql/postgresql-administration/postgresql-database-indexes-table-size

https://www.baremon.eu/backup-large-postgresql-database/

https://www.zhangbj.com/p/1817.html

https://www.postgresql.org/docs/16/release-16-4.html

https://www.alibabacloud.com/help/en/hologres/query-the-storage-sizes-of-tables-and-databases

在数据驱动的时代，高效管理PostgreSQL数据库的存储空间至关重要。通过使用pg_size_pretty函数，你可以轻松查询并优化数据库和表的空间占用情况，从而显著提高系统性能。了解这些技巧不仅能帮助你避免磁盘空间不足的问题，还能发现不必要的冗余数据或过时索引，进一步释放宝贵的存储资源。掌握这些方法，让你的PostgreSQL数据库运行得更加顺畅和高效。

pg_size_pretty函数基础

pg_size_pretty是PostgreSQL中一个非常实用的函数，用于将字节表示的大小转换为更易读的格式（如KB、MB、GB等）。它接受一个整数参数（表示字节数），并返回一个格式化后的字符串结果。例如：

SELECT pg_size_pretty(123456789);

执行上述查询后，结果可能显示为117.7 MB。

此外，pg_size_pretty还可以与其它系统函数结合使用，以更直观地展示数据库对象的大小信息。比如，我们可以查看特定表的大小：

SELECT pg_size_pretty(pg_relation_size('actor'));

这将返回actor表的大小，结果可能显示为16 kB。需要注意的是，pg_relation_size函数只返回表本身的大小，不包括索引。如果要获取表及其索引的总大小，可以使用pg_total_relation_size函数：

SELECT pg_size_pretty(pg_total_relation_size('actor'));

这将返回包含索引在内的总大小，例如72 kB。

检查数据库空间使用情况

使用pg_size_pretty和相关函数，我们可以轻松检查数据库中各个对象的空间使用情况。以下是一些常用的查询示例：

查看单个表的大小

SELECT
    relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM
    pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
    nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
    AND relname = 'your_table_name';

将your_table_name替换为实际的表名，即可查看该表的总大小。

查看数据库中最大的几张表

SELECT
    relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM
    pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
    nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
ORDER BY
    pg_total_relation_size(C.oid) DESC
LIMIT 5;

这条查询将返回数据库中最大的5张表及其大小。

查看整个数据库的大小

SELECT
    pg_size_pretty(pg_database_size('your_database_name')) AS size;

将your_database_name替换为实际的数据库名，即可查看该数据库的总大小。

基于空间使用情况的优化建议

了解了数据库的空间使用情况后，我们可以采取一些措施来优化性能：

使用TOAST存储技术

PostgreSQL的TOAST（The Oversized-Attribute Storage Technique）机制用于处理大块数据。当数据超过2KB时，PostgreSQL会自动进行压缩或将其存储在单独的TOAST表中。对于VARCHAR、TEXT、JSON等变长数据类型，合理设置TOAST策略（PLAIN、EXTENDED、EXTERNAL、MAIN）可以有效节省空间。