更新 PostgreSQL 表带来的数据膨胀

由 John Doe 十二月 6, 2024

摘要：在本文中，我们将了解更新 PostgreSQL 表带来的数据膨胀问题，以及相关的解决方案。

目录

表数据更新场景: 用例

下面我们在 PostgreSQL 的数据库中，创建一个表，插入一些数据，并对表数据进行更新操作，查看表占用的存储空间在更新前后是否会发生变化。

CREATE TABLE t_large (id integer, name text);
CREATE INDEX large_idx ON t_large (id);

INSERT INTO t_large (id, name)
    SELECT i, 'xxx' FROM generate_series(1, 1000000) AS s(i);

SELECT pg_size_pretty(pg_table_size('t_large'));
 pg_size_pretty
----------------
 35 MB

UPDATE t_large SET id = id + 1;
UPDATE t_large SET name = 'yyy';

SELECT pg_size_pretty(pg_table_size('t_large'));
 pg_size_pretty
----------------
 104 MB

表数据更新场景: 内部原理

下面是我们在 PostgreSQL 中进行表更新时，单个页面内数据记录发生的变化：

Update Bloat

  1. 单条数据会有多个版本。
  2. 每个事务都有一个事务 ID，它有点像数据库系统的运行时间。
  3. 每行都有两个默认的隐藏属性 xmin 和 xmax，这两个属性分别保存了创建和更改它们的事务 ID。

表数据更新场景: 思考

问题影响

  1. 数据更新需要占用原来 2 倍的磁盘空间。
  2. 增加了垃圾数据清理和空间回收的工作量。
  3. 基于索引扫描和全表扫描的查询性能都会受到影响。

应对方案

  1. 调整垃圾清理的调度策略，如触发条件、清理节奏、并行度。
  2. 调整表结构，将经常更新的列移动到单独的表中。
  3. 合并更新，将多次更新合并成单次更新。

Redrock Postgres 的解决方案

Redrock Postgres 引入了撤消日志，修改元组时会标记删除索引记录，实现了就地更新。另外，在执行删除或非就地更新的事务提交后，会立即重用空间。因而在更新表数据后，并不会占用更多的存储空间。

下面我们在 Redrock Postgres 的数据库中，创建和上面相同的表，并插入同样的数据，对表数据进行更新操作，查看表占用的存储空间在更新前后是否会发生变化。

CREATE TABLE t_large (id integer, name text);
CREATE INDEX large_idx ON t_large (id);

INSERT INTO t_large (id, name)
  SELECT i, 'xxx' FROM generate_series(1, 1000000) AS s(i);

SELECT pg_size_pretty(pg_table_size('t_large'));
 pg_size_pretty
----------------
 19 MB

UPDATE t_large SET id = id + 1;
UPDATE t_large SET name = 'yyy';

SELECT pg_size_pretty(pg_table_size('t_large'));
 pg_size_pretty
----------------
 19 MB

从上面的测试结果来看，在我们对表t_large进行全表更新完后，该表占用的存储空间没有发生变化。

搜索

分类

标签