On Thu, Sep 29, 2022 at 2:36 AM Robert Haas <robertmh...@gmail.com> wrote:
> 2. WAL Size. Block references in the WAL are by RelFileLocator, so if > you make RelFileLocators bigger, WAL gets bigger. We'd have to test > the exact impact of this, but it seems a bit scary I have done some testing around this area to see the impact on WAL size especially when WAL sizes are smaller, with a very simple test with insert/update/delete I can see around an 11% increase in WAL size [1] then I did some more test with pgbench with smaller scale factor(1) there I do not see a significant increase in the WAL size although it increases WAL size around 1-2%. [2]. [1] checkpoint; do $$ declare lsn1 pg_lsn; lsn2 pg_lsn; diff float; begin select pg_current_wal_lsn() into lsn1; CREATE TABLE test(a int); for counter in 1..1000 loop INSERT INTO test values(1); UPDATE test set a=a+1; DELETE FROM test where a=1; end loop; DROP TABLE test; select pg_current_wal_lsn() into lsn2; select pg_wal_lsn_diff(lsn2, lsn1) into diff; raise notice '%', diff/1024; end; $$; wal generated head: 66199.09375 kB wal generated patch: 73906.984375 kB wal-size increase: 11% [2] ./pgbench -i postgres ./pgbench -c1 -j1 -t 30000 -M prepared postgres wal generated head: 30780 kB wal generated patch: 31284 kB wal-size increase: ~1-2% I have done further analysis to know why on pgbench workload the wal size is increasing by 1-2%. So with waldump I could see that wal size per transaction size increased from 566 (on head) to 590 (with patch), so that is around 4% but when we see total wal size difference after 30k transaction then it is just 1-2% and I think that is because there would be other records which are not impacted like FPI Conclusion: So as suspected with very small WAL sizes with a very targeted test case we can see a significant 11% increase in WAL size but with pgbench kind of workload the increase in WAL size is very less. -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com