PostgreSQL能否限制未实际变更数据的文件发生改动？

PostgreSQL提供了多种方式避免无数据变更的分区文件被VACUUM等操作修改，以下是常用方案：

1. 独立只读表空间（推荐用于历史冷分区） #

这是最接近Oracle解决方案的方式，适合确定不再进行写入操作的历史分区：

• 为目标分区创建独立表空间：

  CREATE TABLESPACE ts_test_202309 LOCATION '/path/to/your/storage/ts_test_202309';
  CREATE TABLESPACE ts_test_202310 LOCATION '/path/to/your/storage/ts_test_202310';
  

• 将分区移动到对应表空间：

  ALTER TABLE test MOVE PARTITION test_202309 TABLESPACE ts_test_202309;
  ALTER TABLE test MOVE PARTITION test_202310 TABLESPACE ts_test_202310;
  

• 将表空间设置为只读：

  ALTER TABLESPACE ts_test_202309 SET READ ONLY;
  ALTER TABLESPACE ts_test_202310 SET READ ONLY;
  

当表空间处于只读状态时，VACUUM（包括带freeze选项的操作）无法修改该表空间内的数据文件，文件哈希值不会再变化，增量备份也不会将这些未改动的文件标记为变更。

2. 禁用分区的自动VACUUM #

如果需要保留分区的写入能力，但想避免自动VACUUM触发的文件修改：

• 先手动冻结目标分区，确保所有元组都已完成冻结：

  VACUUM FREEZE test_202309;
  VACUUM FREEZE test_202310;
  

• 禁用该分区的自动VACUUM：

  ALTER TABLE test ALTER PARTITION test_202309 SET (autovacuum_enabled = false);
  ALTER TABLE test ALTER PARTITION test_202310 SET (autovacuum_enabled = false);
  

注意：后续如果该分区有数据写入，需要重新开启autovacuum并处理冻结，否则可能引发事务ID回卷风险，需定期监控分区的事务ID使用情况。

3. 调整分区的冻结参数 #

通过设置极高的冻结阈值，让VACUUM不会触发冻结操作：

ALTER TABLE test ALTER PARTITION test_202309 SET (
  freeze_min_age = 1000000000,
  freeze_table_age = 1000000000
);
ALTER TABLE test ALTER PARTITION test_202310 SET (
  freeze_min_age = 1000000000,
  freeze_table_age = 1000000000
);

这种方式不需要设置表空间只读，也能避免VACUUM因冻结修改文件，但需根据数据库的事务ID增长速率合理调整参数，防止事务ID回卷。

关键注意点 #

• 只读表空间无法进行任何写入操作，仅适用于冷数据分区；
• 禁用autovacuum或调整冻结参数时，必须监控事务ID的使用情况，避免出现回卷问题；
• 如果后续需要修改只读分区的数据，需先将表空间恢复为读写状态：ALTER TABLESPACE ts_test_202309 SET READ WRITE;

内容的提问来源于stack exchange，提问作者Anastasia