nbtree_wal_test.sql

/*
 * Note: In theory it shouldn't matter wether or not "wal_compression" is on or
 * off, because we shouldn't need to have any FPIs here.  And indeed, that's
 * what we see with master/REL_12_STABLE/REL_11_STABLE (at least on my
 * machine/with my settings, where a timed checkpoint won't start during the
 * test).
 *
 * However, the patch currently ends up using compressable FPIs by logging
 * entire pages within _bt_dedup_one_page(), so it seems necessary to give
 * separate "wal_compression = off" and "wal_compression = on" for the patch
 * only.  Once the WAL-logging for the patch is improved, WAL compression won't
 * matter there either.
 */
\echo 'nbtree WAL diff test'
show autovacuum;
show wal_compression;

drop table if exists land_small_wal;
create table land_small_wal(county text, city text, locality text);
checkpoint;
\echo 'getting WAL volume without index'
\o /dev/null
SELECT  pg_current_wal_insert_lsn() AS prev_lsn; \gset
insert into land_small_wal select county, city, locality from land_registry_price_paid_uk;
SELECT  pg_current_wal_insert_lsn()::pg_lsn AS lsn; \gset
SELECT pg_wal_lsn_diff(:'lsn'::pg_lsn, :'prev_lsn'::pg_lsn) AS wal_diff_table; \gset
\o

SELECT pg_size_pretty(:wal_diff_table::numeric) as table_wal_volume_no_index;

truncate land_small_wal;
create index composite_wal on land_small_wal(county, city, locality);
checkpoint;
\echo 'getting WAL volume with index'
\o /dev/null
SELECT  pg_current_wal_insert_lsn() AS prev_lsn; \gset
insert into land_small_wal select county, city, locality from land_registry_price_paid_uk;
SELECT  pg_current_wal_insert_lsn()::pg_lsn AS lsn; \gset
SELECT pg_wal_lsn_diff(:'lsn'::pg_lsn, :'prev_lsn'::pg_lsn) AS wal_diff; \gset
\o
SELECT pg_size_pretty(:wal_diff::numeric) as final_wal_volume;
SELECT pg_size_pretty(:wal_diff::numeric - :wal_diff_table::numeric) as nbtree_wal_volume;

/*

master, patch, and REL_11_STABLE:
=================================

┌───────────────────────────┐
│ table_wal_volume_no_index │
├───────────────────────────┤
│ 1812 MB                   │
└───────────────────────────┘
(1 row)

master:
=======

┌──────────────────┐
│ final_wal_volume │
├──────────────────┤
│ 3823 MB          │
└──────────────────┘
(1 row)

┌───────────────────┐
│ nbtree_wal_volume │
├───────────────────┤
│ 2011 MB           │
└───────────────────┘
(1 row)

patch with "wal_compression = on":
==================================

┌──────────────────┐
│ final_wal_volume │
├──────────────────┤
│ 8314 MB          │
└──────────────────┘
(1 row)

┌───────────────────┐
│ nbtree_wal_volume │
├───────────────────┤
│ 6502 MB           │
└───────────────────┘
(1 row)

patch with "wal_compression = off":
===================================

┌──────────────────┐
│ final_wal_volume │
├──────────────────┤
│ 9474 MB          │
└──────────────────┘
(1 row)

Time: 1.911 ms
┌───────────────────┐
│ nbtree_wal_volume │
├───────────────────┤
│ 7662 MB           │
└───────────────────┘
(1 row)

REL_11_STABLE:
==============

┌──────────────────┐
│ final_wal_volume │
├──────────────────┤
│ 4356 MB          │
└──────────────────┘
(1 row)

Time: 0.209 ms
┌───────────────────┐
│ nbtree_wal_volume │
├───────────────────┤
│ 2544 MB           │
└───────────────────┘
(1 row)

*/