Re: PG17 optimizations to vacuum - Mailing list pgsql-general

Pavel, did you test v17 with checksums enabled and v16 with checksums disabled, by any chance?

Exactly, You are right!

My v16 cluster comes from the default Ubuntu distribution.
I forgot that checksums disabled by default.
But when I initialize the master cluster, I automatically set -k option.

More accurate results for the test:

CREATE TABLE t(id integer) WITH (autovacuum_enabled = off);
INSERT INTO t SELECT gen.id FROM generate_series(1,3_500_000) gen(id);
VACUUM FREEZE t;
UPDATE t SET id = id + 1;
VACUUM FREEZE VERBOSE t;

checksums disabled
v16.4	WAL usage: 77437 records, 20832 full page images, 110654467 bytes
master	WAL usage: 61949 records, 20581 full page images, 92549229 bytes

checksums enabled
v16.4	WAL usage: 92848 records, 20985 full page images, 194863720 bytes
master	WAL usage: 76520 records, 20358 full page images, 181867154 bytes

This a great optimization!

Peter, Melanie, Heikki, 
Thank you very much for your help and time spent!
Sorry for the noise before the release of PG17.

=====
I don't think it's necessary anymore. But just in case.

Non-default settings for v16

postgres@postgres(16.4)=# \dconfig              List of non-default configuration parameters         Parameter          |                  Value                  
----------------------------+----------------------------------------- application_name           | psql client_encoding            | UTF8 cluster_name               | 16/main config_file                | /etc/postgresql/16/main/postgresql.conf data_directory             | /var/lib/postgresql/16/main DateStyle                  | ISO, DMY default_text_search_config | pg_catalog.english external_pid_file          | /var/run/postgresql/16-main.pid hba_file                   | /etc/postgresql/16/main/pg_hba.conf ident_file                 | /etc/postgresql/16/main/pg_ident.conf lc_messages                | en_US.UTF-8 lc_monetary                | ru_RU.UTF-8 lc_numeric                 | ru_RU.UTF-8 lc_time                    | ru_RU.UTF-8 log_line_prefix            | %m [%p] %q%u@%d  log_timezone               | Europe/Moscow port                       | 5433 ssl                        | on ssl_cert_file              | /etc/ssl/certs/ssl-cert-snakeoil.pem ssl_key_file               | /etc/ssl/private/ssl-cert-snakeoil.key TimeZone                   | Europe/Moscow
(21 rows)

Building options and non-default settings for master:

./configure --silent --prefix=/home/pal/master --with-pgport=5401 --with-lz4 --with-icu --with-zstd --enable-nls --with-libxml --with-llvm
make world --silent -j
make --silent install-world

initdb -k -U postgres

postgres@postgres(18.0)=# \dconfig             List of non-default configuration parameters         Parameter          |                 Value                 
----------------------------+--------------------------------------- application_name           | psql client_encoding            | UTF8 config_file                | /home/pal/master/data/postgresql.conf data_directory             | /home/pal/master/data DateStyle                  | ISO, DMY default_text_search_config | pg_catalog.english hba_file                   | /home/pal/master/data/pg_hba.conf ident_file                 | /home/pal/master/data/pg_ident.conf lc_messages                | en_US.UTF-8 lc_monetary                | ru_RU.UTF-8 lc_numeric                 | ru_RU.UTF-8 lc_time                    | ru_RU.UTF-8 log_timezone               | Europe/Moscow TimeZone                   | Europe/Moscow
(14 rows)

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com