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,
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
