Is it possible in PostgreSQL to restrict changes for files whose data is not actually changed?

Question

Problem: We have a table “test”, consists of sections “test_202309”, “test_202310”, “test_202311”. The sections store data for September 2023, October 2023 and November 2023.

1. I using the command “oid2name -d <db_name>” to get filenames for sections (filenode)
2. Change directory to directory containing the database files:

cd <datadirectory>/<oid_db>

3. Get hash sum for the files:

md5sum <filenode>

4. Execute sql command VACUUM with options verbose, analyze, freeze:

VACUUM (verbose, analyze, freeze) test;

5. Get hash sum for the files again:

md5sum <filenode>

The hash sum for sections test_202309 and test_202310 is different, but the data in sections did not change.

It still change when i run VACUUM (freeze) second time, and also it is relevant for sections which don't containing dead rows.

For the database, incremental copying is performed at the file system level, this files will be considered changed and will be included in the incremental copy, increasing the copy size accordingly.

Question: Is it possible in PostgreSQL to restrict changes to files whose data is not actually changed?

P.S. For Oracle, a similar problem was solved by placing table partitions in separate tablespaces and make them into read only tablespaces

Answer 1

No, you cannot do that. PostgreSQL needs write access to the data files. At the very least, it will run an anti-wraparound autovacuum run every autovacuum_freeze_max_age transactions that won't do anything much, but still changes the file (as you observed).

However, that occurs only rarely, and all-frozen tables might not change for long stretches of time, so the increased backup volume shouldn't be much of a problem.