更新表或索引时会在数据库中创建bloat，更新本质上是一种删除和插入操作。删除所使用的磁盘空间可重复使用，但无法回收，因此会产生bloat。PostgreSQL数据库也是如此，频繁的UPDATE和DELETE操作会在磁盘上的表或索引关系文件中留下大量未使用的空间。随着时间的流逝，这个空间可能会累积并导致表和索引的性能下降。这种积累称为bloat表或索引。
该博客将探讨如何在数据库中创建blot以及如何解决bloat问题。
怎么发生的?
PostgreSQL系统实现了MVCC（多版本并发控制）和MVCC的实现方式，它可以在您的系统中引入bloat。
在PostgreSQL中使用UPDATE或DELETE语句时，它实际上不会从磁盘上删除该行。在UPDATE情况下，它将受影响的行标记为不可见，并插入这些行的新版本。尽管DELETE有点简单，但受影响的行只是标记为不可见。这些不可见行也称为死行或死元组。
这是什么意思？为什么有什么意义呢？随着时间的流逝，这些死元组可能会大量积累，在某些最坏的情况下，这种积累甚至可能更大，以至于表中的实际行变得不可用。
您会看到，这些行被标记为不可见，但它们仍然是表的一部分，并且正在占用磁盘空间……假设表中有一百万行，其中每一行占用100字节的磁盘空间。假定该表消耗了大约100MB的磁盘空间。现在，假设表中存在30％的不可见行，这意味着无效的元组正在使用130MB的磁盘空间。这看似微不足道，但考虑到现实情况，即表使用GB / TB的数据，这成为一个严重的问题。
那么如何发现bloat呢？
有两种方法可以检测出bloat并识别数据库中有问题的区域。我们将研究一种我认为更准确的特定方法。
pgstattuple
这是一个扩展模块，可用于获取表的清晰图片。postgresql中提供了一些统计信息，可用于弄清表和索引中的bloat。但是，基于这些的估计可能很少。但是，使用pgstattuple，我们可以在索引和表中获得准确的实际bloat图。让我们考虑以下示例：
postgres=# CREATE TABLE test as SELECT x, md5(random()::text) as y FROM generate_Series(1, 1000000) x;
SELECT 1000000
postgres=# CREATE INDEX ON test (x);
CREATE INDEX
postgres=#
postgres=# SELECT pg_size_pretty(pg_relation_size('test')) as table_size,
postgres-# pg_size_pretty(pg_relation_size('test_x_idx')) as index_size,
postgres-# (pgstattuple('test')).dead_tuple_percent;
table_size | index_size | dead_tuple_percent
------------+------------+--------------------
65 MB | 21 MB | 0
(1 row)
postgres=#
postgres=# DELETE FROM test WHERE x % 3 = 0;
DELETE 333333
postgres=# ANALYZE test;
ANALYZE
postgres=# SELECT pg_size_pretty(pg_relation_size('test')) as table_size,
postgres-# pg_size_pretty(pg_relation_size('test_x_idx')) as index_size,
postgres-# (pgstattuple('test')).dead_tuple_percent;
table_size | index_size | dead_tuple_percent
------------+------------+--------------------
65 MB | 21 MB | 29.78
(1 row)
看到表大小保持不变，但是pgstattuple的输出显示浪费了29.78％的磁盘空间。它占用了表中的空间，但不再可用。
现在让我们看一下索引。
postgres=# SELECT pg_relation_size('test') as table_size,
postgres-# pg_relation_size('test_x_idx') as index_size,
postgres-# 100-(pgstatindex('test_x_idx')).avg_leaf_density as bloat_ratio;
table_size | index_size | bloat_ratio
------------+------------+-------------------
68272128 | 22487040 | 9.939999999999998
(1 row)
postgres=# UPDATE test SET x = x + 2 WHERE x % 2 = 0;
UPDATE 333334
postgres=# SELECT pg_relation_size('test') as table_size,
postgres-# pg_relation_size('test_x_idx') as index_size,
postgres-# 100-(pgstatindex('test_x_idx')).avg_leaf_density as bloat_ratio;
table_size | index_size | bloat_ratio
------------+------------+-------------
69976064 | 44941312 | 41.08
(1 row)
经过上述操作，指数已become肿至41.08％。这意味着该索引的性能将降低，因为很多条目要么为空，要么指向死元组。
如何消除肿？
那么，如何从数据库中删除bloat？有几种方法可以用来避免表和索引中的bloat。
真空
第一件事应该是常规使用VACUUM命令。这将 通过在可用空间映射中添加该空间来删除无效元组，以便数据库系统可以重新使用它。它还将尝试删除指向这些无效元组的索引条目。但是，该空间不会返回给操作系统，也不会缩小表的大小。
通常，自动真空后台处理可以解决这一问题。每当表中的真空度更改为20％时，默认设置都将设置为真空度表。因此，如果您经常看到肿，请确保启用了autovavuum，并根据数据大小调整设置。
物理重新排序
如果真空度无法维持并有助于避免bloat，那么您将不得不对表进行物理重新排序。物理重新排序涉及重写整个表。有两种方法可以实现此目的。
1. VACUUM FULL
VACUUM FULL将完全删除表中的所有bloat及其关联的索引，并将磁盘空间回收给OS。这将减小磁盘上的表大小。它可以通过重写整个表来完成所有操作，但是这是一个扩展操作，并且它将在此操作期间锁定表或索引，这在大多数情况下都是不希望的。
2. CLUSTER
另一个选择是使用CLUSTER命令。这也将重写表，但它会根据指定的索引来执行此操作。除此之外，它还需要锁定表并阻止对表的读写操作，直到完成CLUSTER操作为止。
3. pg_repack
这个扩展名在VACUUM FULL或CLUSTER可能无法工作的情况下很有用。此扩展通过根据the肿表的数据创建一个全新的表来重组表。在跟踪对原始表所做的更改时，最终会将新表与原始表交换。
此方法不会为任何读取或写入操作锁定表，并且比VACUUM FULL或CLUSTER命令快得多。
4. REINDEX
此选项可用于从索引中删除bloat。此命令将重建指定的索引或表上的所有索引。此选项不会阻止读取，但会阻止写入。但是，可以使用CONCURRENTLY选项来避免这种情况，但完成时间可能比标准索引创建要长。
结论
在本博客中，我们讨论了数据库bloat的含义，如何创建bloat以及在PostgreSQL中监视bloat的不同方法以及如何解决bloat。数据库bloat是一个严重的数据库问题，由于它可能阻止数据库运行或严重导致性能下降，因此可能会对业务的连续性造成严重影响。我相信已经尝试解决PG中的数据库bloat问题，而zheap项目的主要目的之一就是处理数据库bloat。
作者简介：Asif Rehman
Asif Rehman是HighGo Software的高级软件工程师。他于2005年加入Enterprise PostgreSQL，这是一家Enterprise PostgreSQL的公司，并开始了他的开源开发工作，尤其是PostgreSQL。Asif的贡献范围包括开发与oracle兼容性相关的内部功能，以及围绕PostgreSQL开发工具。他于2018年9月加入HighGo Software。
文章来源：https://www.highgo.ca/2021/03/20/how-to-check-and-resolve-bloat-in-postgresql/
