Re: PG17 optimizations to vacuum

On 03.09.2024 00:11, Heikki Linnakangas wrote:
> 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,Thankyouvery much foryour helpandtimespent!Sorryforthe noisebeforethe releaseof PG17.

=====
I don't thinkit'snecessaryanymore.Butjust incase.

Non-default settings for v16

postgres(at)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(at)%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(at)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