PostgreSQL - 查看表膨胀空间

一边学习一边哭

已于 2024-03-11 19:56:45 修改

阅读量1.7k

点赞数 25

文章标签： postgresql 数据库

于 2024-03-11 17:30:43 首次发布

本文链接：https://blog.youkuaiyun.com/qq522044637/article/details/135661242

版权

使用pgstattuple插件查看表膨胀空间

查看表和其关联的TOAST表的oid的关系

方法一

方法二

参考文档

使用pgstattuple插件查看表膨胀空间

select *,1.0 - tuple_len::numeric / table_len as bloat from pgstattuple('table_name');

table_len bigint	物理关系长度（以字节为单位）
tuple_count bigint	存活元组数量
tuple_len bigint	活动元组的总长度（以字节为单位）
tuple_percent float8	存活元组的百分比
dead_tuple_count bigint	死元组数量
dead_tuple_len bigint	死元组的总长度（以字节为单位）
dead_tuple_percent float8	死亡元组的百分比
free_space bigint	总可用空间（以字节为单位）
free_percent float8	可用空间百分比
bloat	表膨胀系数

死元组&膨胀系数清理

使用vacuum命令，可以清理死元组，但是膨胀系数不会降低。因为该命令不会将磁盘空间返回给操作系统，但是新的数据可以复用空间。
使用vacuum full命令，可以降低膨胀系数。因为该命令会将磁盘空间返回给操作系统，但是会产生独立锁，导致清理期间表不可用。且清理期间磁盘使用空间最多会翻倍，因为操作是将有用的数据写到一个新的表里，结束后删除旧表，释放空间。

其他详细死元组&膨胀系数清理的信息，可以参考：postgresql磁盘空间清理

查看表占用磁盘空间大小是如何组成的

select pg_relation_size('table_name', 'main') as main, \
                pg_relation_size('table_name', 'fsm') as fsm, \
                pg_relation_size('table_name', 'vm') as vm, \
                pg_relation_size('table_name', 'init') as init, \
                pg_relation_size('table_name') as pg_relation_size, \
                pg_total_relation_size('toast_oid') as toast, \
                pg_table_size('table_name') as pg_table_size, \
                pg_indexes_size('table_name') as pg_indexes_size, \
                pg_total_relation_size('table_name') as pg_total_size;

main	fsm	vm	init	pg_relation_size	toast	pg_table_size	pg_indexes_size	pg_total_size
315623104512	77602816	9027584	0	315623104512	2504810496	318214545408	137802727424	456017272832

关联关系：

main = pg_relation_size ；其实pg_relation_size('table_name')就是pg_relation_size('table_name', 'main')的简写
表占空间的大小 = main + fsm + vm + init + toast = pg_table_size
索引占空间的大小 = pg_indexes_size
表真实占用磁盘空间 = 表占空间的大小 + 索引占空间的大小 = pg_table_size + pg_indexes_size

什么是fms和vm？

空闲空间映射(FSM) - free space maps
每一个表和索引（除了哈希索引）都有一个空闲空间映射（FSM）来保持对关系中可用空间的跟踪；
它伴随着主关系数据被存储在一个独立的关系分支中，以关系的文件节点号加上一个_fsm后缀命名；

FSM文件是执行VACUUM操作时，或者是为了插入行而第一次查询FSM文件时才会创建；

可见性映射(VM) - visual map
每一个表都有一个可见性映射（VM）用来跟踪哪些页面只包含已知对所有活动事务可见的元组，它也跟踪哪些页面只包含未被冻结的元组；
它伴随着主关系数据被存储在一个独立的关系分支中，以该关系的文件节点号加上一个_vm后缀来命名。

VM文件中为每个数据块设置了一个标志位，用来标记数据块中是否存在需要清理的行。
有了这个文件后，通过VACUUM命令扫描这个文件时，如果发现VM文件中这个数据块上的位表示该数据块没有需要清理的行，则会跳过对这个数据块的扫描，从而加快VACUUM清理的速度。

什么是TOAST？

TOAST（超尺寸属性存储技术－The Oversized-Attribute Storage Technique）。

PostgreSQL使用固定的页面尺寸（通常是8kB)，并且不允许元组跨越多个额页面。因此不可能直接存储非常大的域值。为了克服这个限制，大的域值会被压缩并/或分解成多个物理行。这些处理对用户都是透明的，只是在大部分的后端代码上有一些小的影响。这个技术的昵称是TOAST（或者“切片面包之后的最好的东西”）。TOAST 机制也被用来提升内存中大型数据值的处理。

查看表和其关联的TOAST表的oid的关系

数据库表和TOAST表的信息都在pg_class表中

数据库表信息所在行记录了：表的oid，表名，表关联的TOAST表的oid

TOAST表信息所在的行记录了：TOAST表的oid，TOAST表的表名

方法一

一条语句直接导出表和其TOAST表的关联关系

SELECT
  c.relname AS "main_table",
  c.oid AS "main_table_oid",
  t.relname AS "toast_table",
  t.oid AS "toast_table_oid"
FROM pg_class c
JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind = 'r';

方法二

1.查询表、表oid，和其关联的TOAST表的oid

select oid,relname,reltoastrelid from pg_class where relname = 'table_name';

2.使用TOAST表的iod，查询TOAST表的信息

(其实一般情况下，有了TOAST表的oid就够用了)

select oid,relname from pg_class where oid = 'toast_iod';

参考文档

Pgsql表膨胀的产生及处理(oracle,postgres) - AI牛丝

pg_relation_size,pg_table_size,pg_indexes_size,pg_total_relation_size之间的关系-优快云博客

PostgreSQL 之 FSM和VM的理解_vacuum命令通过扫描后缀为_fsm的文件可以加快vacuum进程-优快云博客