PostgreSQL 表膨胀原因和解决方案

在 PostgreSQL 中，表膨胀是一个常见的问题，它会导致数据库性能下降，甚至在极端情况下会耗尽磁盘空间。了解表膨胀的原因及其解决方案，对于维护数据库性能和稳定性至关重要。

表膨胀的原因

MVCC (多版本并发控制)

PostgreSQL 使用 MVCC 机制来处理并发访问，允许读取操作在不锁定表的情况下进行，从而提高了并发性能。当一条记录被更新或删除时，原始记录不会立即从磁盘上移除。相反，它会被标记为不可见，而新的记录（在更新的情况下）会被添加到表中。这意味着随着时间的推移，如果不进行适当的维护，表上的“死”行会不断累积，从而导致表膨胀。

频繁的更新和删除操作

频繁的更新和删除操作直接导致了表中大量的“死”行。在高更新和删除率的环境中，表膨胀尤为严重，因为每次这些操作发生时，都会留下不再可达的行。

查看表占用空间

如下SQL可以查询当前数据库中以 a_ 开头的表所占用的空间

-- 查出表大小按大小含索引
SELECT
short_name,
"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,
short_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,
table_name as short_name,
table_schema