Hello, While playing with optimizations to vacuum in v17 I can't understand how to measure this one: "Allow vacuum to more efficiently remove and freeze tuples". My test script and results: CREATE TABLE t(id integer) WITH (autovacuum_enabled = off); INSERT INTO t SELECT gen.id FROM generate_series(1,3_500_000) gen(id); CREATE INDEX t_id ON t(id); SET maintenance_work_mem = '1MB'; UPDATE t SET id = id + 1; VACUUM FREEZE VERBOSE t; v16.4 INFO: aggressively vacuuming "postgres.public.t" INFO: finished vacuuming "postgres.public.t": index scans: 21 pages: 0 removed, 30974 remain, 30974 scanned (100.00% of total) tuples: 3500000 removed, 3500000 remain, 0 are dead but not yet removable removable cutoff: 1675, which was 0 XIDs old when operation ended new relfrozenxid: 1675, which is 4 XIDs ahead of previous value frozen: 15488 pages from table (50.00% of total) had 3500000 tuples frozen index scan needed: 15487 pages from table (50.00% of total) had 3500000 dead item identifiers removed index "t_id": pages: 19196 in total, 0 newly deleted, 0 currently deleted, 0 reusable avg read rate: 473.207 MB/s, avg write rate: 92.511 MB/s buffer usage: 212718 hits, 267930 misses, 52380 dirtied WAL usage: 96585 records, 42819 full page images, 198029405 bytes system usage: CPU: user: 3.17 s, system: 0.48 s, elapsed: 4.42 s VACUUM master INFO: aggressively vacuuming "postgres.public.t" INFO: finished vacuuming "postgres.public.t": index scans: 1 pages: 0 removed, 30974 remain, 30974 scanned (100.00% of total) tuples: 3500000 removed, 3500000 remain, 0 are dead but not yet removable removable cutoff: 950, which was 0 XIDs old when operation ended new relfrozenxid: 950, which is 4 XIDs ahead of previous value frozen: 15488 pages from table (50.00% of total) had 3500000 tuples frozen index scan needed: 15487 pages from table (50.00% of total) had 3500000 dead item identifiers removed index "t_id": pages: 19196 in total, 0 newly deleted, 0 currently deleted, 0 reusable avg read rate: 101.121 MB/s, avg write rate: 120.530 MB/s buffer usage: 48900 hits, 47749 reads, 56914 dirtied WAL usage: 125391 records, 46626 full page images, 330547751 bytes system usage: CPU: user: 2.90 s, system: 0.27 s, elapsed: 3.68 s VACUUM I see a perfectly working TID-store optimization. With reduced maintenance_work_mem it used only one 'vacuuming indexes' phase instead of 21 in v16. But I also expected to see a reduction in the number of WAL records and the total size of the WAL. Instead, WAL numbers have significantly degraded. What am I doing wrong?
-- Pavel Luzanov Postgres Professional: https://postgrespro.com
