PostgreSQL 实现 多版本并发控制 (MVCC) 的方式是为每次 更新 和 删除 创建新的行版本，而不是就地覆盖数据。这种设计消除了读取锁，但在堆中留下了 "死元组"，这些元组必须由 VACUUM 过程回收。当一个表经历高更新频率的工作负载——如频繁的待处理记录状态更新或软删除切换——死元组的积累超出了真空处理的速度，导致表膨胀、索引膨胀和查询性能下降。
核心问题在于，VACUUM 必须扫描整个表以识别死元组，而在单一的多TB表中，这项操作变得受限于 I/O，可能在事务 ID 包装之前无法完成。在没有干预的情况下，尽管实际行数保持稳定，表却无限增长，占用过多存储并减慢索引扫描。
解决方案是使用 范围 策略实施 声明性分区，基于时间或逻辑键（如创建日期）将表拆分为更小的物理分区（例如，按月分段）。通过将表划分为较小的物理分区，真空操作可以在单个分区上进行，而不是整个表。此外，分区修剪 确保查询仅访问相关片段，老旧的分区可以被分离并存档，瞬间回收存储而无需真空开销。
-- 使用范围分区的父表 CREATE TABLE iot_sensor_data ( sensor_id INT NOT NULL, temperature NUMERIC, recorded_at TIMESTAMP NOT NULL, status VARCHAR(20) ) PARTITION BY RANGE (recorded_at); -- 最近高更新频率数据的按月分区 CREATE TABLE iot_sensor_data_2024_06 PARTITION OF iot_sensor_data FOR VALUES FROM ('2024-06-01') TO ('2024-07-01'); -- 在分区键上的索引 CREATE INDEX idx_sensor_date ON iot_sensor_data (recorded_at);
一个智能制造客户运营着一个 PostgreSQL 14 数据库，从 50,000 个 IoT 传感器中获取遥测数据，每天生成 1,000 万条读数，其中 30% 需要在 24 小时内进行状态更新。由于 AUTOVACUUM 无法快速处理表以回收因不断状态标志更新而产生的死元组，sensor_logs 表在六个月内膨胀至 2TB。查询延迟激增至 30 秒，pg_class 显示该表相对于其逻辑大小膨胀了 400%。
评估了三种架构解决方案。第一种方法涉及积极的 VACUUM 调优，将 autovacuum_vacuum_scale_factor 降至 0.02，并将 maintenance_work_mem 增加到 2GB。尽管这略有帮助，但该过程仍需持续扫描整个 2TB 表，导致与生产查询的严重 I/O 冲突，并且在夜间维护窗口内无法完成。第二种策略建议使用排除旧数据的 部分索引，这减少了索引膨胀，但并没有解决基础的堆膨胀或表中死元组的积累。
第三种解决方案通过 RANGE 在 recorded_at 时间戳上实施 声明性分区，创建每个约 300GB 的按月分区。这种方法之所以被选中，是因为它将高更新频率的活动局限于当前月份的分区，允许 VACUUM 在每次运行时处理 300GB，而不是 2TB。此外，超过一年的分区可以被 分离 并移至冷存储，回收空间而不产生昂贵的 DELETE 操作。实施后，真空持续时间从 8 小时下降到 45 分钟，事务 ID 包装风险消失，通过分区修剪查询性能提高了十倍。
PostgreSQL 的 HOT（仅堆元组）机制如何减少索引膨胀，并在什么特定条件下无法应用？
HOT 更新发生在更新行但未修改任何索引列时，并且同一 8KB 数据页中存在足够的空闲空间。在这种情况下，PostgreSQL 将新元组链到同一堆中的旧元组，而不会创建新的索引条目，从而大幅减少索引维护开销。然而，如果更新修改了任何存在于任何索引中的列，或者如果页面在 fillfactor 阈值下缺乏空闲空间，则 HOT 会立即失效，迫使 PostgreSQL 写入新的索引指针并造成膨胀。候选人通常认为所有的 UPDATE 操作对索引的影响相同，而没有意识到在 HOT 成功时更新非索引列的成本要低得多。
VACUUM、VACUUM FULL 和 CLUSTER 在锁定行为和表可用性方面的确切区别是什么？
VACUUM 与所有操作并行运行，将死元组标记为可重用空间，而不将存储返回给操作系统；它对用户数据不持有锁。VACUUM FULL 重写整个表文件以彻底消除膨胀，但会获得一个 ACCESS EXCLUSIVE 锁，这会阻塞所有读取和写入，持续时间可能在大型表上持续数小时。CLUSTER 物理上重新排序表以匹配索引顺序，也需要一个 ACCESS EXCLUSIVE 锁，并且通常比 VACUUM FULL 在纯空间回收方面更慢，但保持未来聚类索引扫描的排序。候选人常常危险地推荐 VACUUM FULL 作为日常维护，而不了解这会导致表完全不可用。
可见性图如何影响索引仅扫描性能，以及为什么真空频率对这种访问方法很重要？
可见性图 是存储在表旁边的二进制位图，跟踪哪些堆页面仅包含对所有当前和未来事务可见的元组。一种 索引仅扫描 可以仅使用索引和可见性图满足查询，而无需获取堆元组，但仅在图确认该页面上的所有行都是可见的情况下。如果一个页面包含任何死元组或未提交的事务，则可见性位将未设置，迫使数据库验证单个堆元组。频繁的 VACUUM 更新可见性图位，允许真正的索引仅访问；如果没有它，即使使用覆盖索引的查询也会出现随机 I/O 来检查元组可见性，抵消优化的目的。