On Wed, Nov 18, 2026 at 10:42 AM Zhijie Hou <[email protected]> wrote: > > On Wed, Nov18, 2026 at 06:47 AM Melanie Plageman wrote: > > > Ah, okay. I'll do that. The original test feels awfully fragile. Is > > there something we could do to strengthen it? Like check that one of > > the buffers is marked BM_PERMANENT somehow? It seems very trivial to > > accidentally eliminate the coverage. And there are of course lots of > > ways for those buffers to get evicted. > > Maybe we could add a few comments above the CREATE DATABASE command to > give developers a hint, and if it's worth it, we could add a check after > the command to verify that pg_class's pages stay in the buffer cache > (But note that if we add this check, we'd better disable autovaccum to > prevent background access): > > # Verify that scanning template1's pg_class performs zero disk reads, > # confirming its pages were not evicted by CREATE DATABASE. > is( $node->safe_psql( > $db_template, q{ > SELECT pg_stat_get_xact_blocks_fetched('pg_class'::regclass) - > pg_stat_get_xact_blocks_hit('pg_class'::regclass) > FROM pg_class > HAVING sum(relpages) >= 0; > }), > "0", > "template pg_class pages remain cached after CREATE DATABASE");
I decided to just add a comment to the test instead. I've now committed and backpatched the main fix. The remaining patch attached is targeted at master only and applies the optimization to RelationCopyStorage(). I've added a few things since the last version including a test. - Melanie
From 60af27fc884d96e46b83bf0166575835a520565c Mon Sep 17 00:00:00 2001 From: Melanie Plageman <[email protected]> Date: Thu, 19 Nov 2026 16:34:56 -0500 Subject: [PATCH v4] Reduce WAL generated when copying relation storage RelationCopyStorage() included the region between pd_lower and pd_upper in full-page images, even when that region was known to be free space. It conservatively treated every page as nonstandard, which, while correct, could generate unnecessary WAL. db26517e79c fixed RelationCopyStorageUsingBuffer(), which previously treated every page as standard and could therefore omit meaningful data. RelationCopyStorageUsingBuffer() now omits that region only for heap and B-tree main-fork pages, which are known to use the standard page layout, or when the region contains only zeroes. Apply the same policy to RelationCopyStorage(). This reduces WAL volume for known standard page layouts while preserving the full contents of unknown layouts unless omission is demonstrably lossless. Reviewed-by: surya poondla <[email protected]> Author: Melanie Plageman <[email protected]> Discussion: https://postgr.es/m/CAAKRu_b9r-Pz9gzYvC%2B5EHnXqNgfsJhcH%3DhuUGbXzvcnbHtxCg%40mail.gmail.com --- src/backend/access/heap/heapam_handler.c | 11 ++++++++--- src/backend/catalog/storage.c | 20 +++++++++++++++----- src/backend/commands/tablecmds.c | 11 ++++++++--- src/include/catalog/storage.h | 3 ++- src/test/recovery/t/018_wal_optimize.pl | 13 +++++++++++++ 5 files changed, 46 insertions(+), 12 deletions(-) diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c index 05a81e4557a..4146b46a333 100644 --- a/src/backend/access/heap/heapam_handler.c +++ b/src/backend/access/heap/heapam_handler.c @@ -31,6 +31,7 @@ #include "access/xact.h" #include "catalog/catalog.h" #include "catalog/index.h" +#include "catalog/pg_am_d.h" #include "catalog/storage.h" #include "catalog/storage_xlog.h" #include "commands/progress.h" @@ -553,9 +554,13 @@ heapam_relation_copy_data(Relation rel, const RelFileLocator *newrlocator) */ dstrel = RelationCreateStorage(*newrlocator, rel->rd_rel->relpersistence, true); - /* copy main fork */ + /* + * Other table AMs can reuse this callback, so assume standard main-fork + * pages only for the built-in heap AM. + */ RelationCopyStorage(RelationGetSmgr(rel), dstrel, MAIN_FORKNUM, - rel->rd_rel->relpersistence); + rel->rd_rel->relpersistence, + rel->rd_rel->relam == HEAP_TABLE_AM_OID); /* copy those extra forks that exist */ for (ForkNumber forkNum = MAIN_FORKNUM + 1; @@ -574,7 +579,7 @@ heapam_relation_copy_data(Relation rel, const RelFileLocator *newrlocator) forkNum == INIT_FORKNUM)) log_smgrcreate(newrlocator, forkNum); RelationCopyStorage(RelationGetSmgr(rel), dstrel, forkNum, - rel->rd_rel->relpersistence); + rel->rd_rel->relpersistence, false); } } diff --git a/src/backend/catalog/storage.c b/src/backend/catalog/storage.c index e443a4993c5..d8b669d8aa3 100644 --- a/src/backend/catalog/storage.c +++ b/src/backend/catalog/storage.c @@ -476,7 +476,7 @@ RelationPreTruncate(Relation rel) */ void RelationCopyStorage(SMgrRelation src, SMgrRelation dst, - ForkNumber forkNum, char relpersistence) + ForkNumber forkNum, char relpersistence, bool page_std) { bool use_wal; bool copying_initfork; @@ -508,6 +508,7 @@ RelationCopyStorage(SMgrRelation src, SMgrRelation dst, for (blkno = 0; blkno < nblocks; blkno++) { BulkWriteBuffer buf; + bool allow_page_std = page_std; int piv_flags; bool checksum_failure; bool verified; @@ -551,11 +552,20 @@ RelationCopyStorage(SMgrRelation src, SMgrRelation dst, } /* - * Queue the page for WAL-logging and writing out. Unfortunately we - * don't know what kind of a page this is, so we have to log the full - * page including any unused space. + * For page layouts we don't recognize, hole removal is still safe if + * the exact range that WAL would omit contains only zeroes. */ - smgr_bulk_write(bulkstate, blkno, buf, false); + if (use_wal && !allow_page_std && + PageFreeSpaceBoundsAreValid((Page) buf)) + { + PageHeader phdr = (PageHeader) buf; + + allow_page_std = + pg_memory_is_all_zeros((char *) buf + phdr->pd_lower, + phdr->pd_upper - phdr->pd_lower); + } + + smgr_bulk_write(bulkstate, blkno, buf, allow_page_std); } smgr_bulk_finish(bulkstate); } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index b6700929459..9786a5af58a 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -18529,9 +18529,14 @@ index_copy_data(Relation rel, RelFileLocator newrlocator) */ dstrel = RelationCreateStorage(newrlocator, rel->rd_rel->relpersistence, true); - /* copy main fork */ + /* + * Assume standard main-fork pages only for B-tree. Other index AMs may + * use standard pages today but still have nonstandard pages from older + * versions (e.g. GIN). + */ RelationCopyStorage(RelationGetSmgr(rel), dstrel, MAIN_FORKNUM, - rel->rd_rel->relpersistence); + rel->rd_rel->relpersistence, + rel->rd_rel->relam == BTREE_AM_OID); /* copy those extra forks that exist */ for (ForkNumber forkNum = MAIN_FORKNUM + 1; @@ -18550,7 +18555,7 @@ index_copy_data(Relation rel, RelFileLocator newrlocator) forkNum == INIT_FORKNUM)) log_smgrcreate(&newrlocator, forkNum); RelationCopyStorage(RelationGetSmgr(rel), dstrel, forkNum, - rel->rd_rel->relpersistence); + rel->rd_rel->relpersistence, false); } } diff --git a/src/include/catalog/storage.h b/src/include/catalog/storage.h index 70f619a6d6f..68211e51c1e 100644 --- a/src/include/catalog/storage.h +++ b/src/include/catalog/storage.h @@ -30,7 +30,8 @@ extern void RelationPreserveStorage(RelFileLocator rlocator, bool atCommit); extern void RelationPreTruncate(Relation rel); extern void RelationTruncate(Relation rel, BlockNumber nblocks); extern void RelationCopyStorage(SMgrRelation src, SMgrRelation dst, - ForkNumber forkNum, char relpersistence); + ForkNumber forkNum, char relpersistence, + bool page_std); extern bool RelFileLocatorSkippingWAL(RelFileLocator rlocator); extern Size EstimatePendingSyncsSpace(void); extern void SerializePendingSyncs(Size maxSize, char *startAddress); diff --git a/src/test/recovery/t/018_wal_optimize.pl b/src/test/recovery/t/018_wal_optimize.pl index 8f25b5dd165..ffe868e82f5 100644 --- a/src/test/recovery/t/018_wal_optimize.pl +++ b/src/test/recovery/t/018_wal_optimize.pl @@ -391,6 +391,19 @@ wal_skip_threshold = 0 check_orphan_relfilenodes($node, "wal_level = $wal_level, no orphan relfilenode remains"); + # Move the table back, this time with a populated visibility map. + $node->safe_psql('postgres', q{ + CREATE EXTENSION pg_visibility; + VACUUM (FREEZE) moved; + ALTER TABLE moved SET TABLESPACE pg_default; + }); + $node->stop('immediate'); + $node->start; + $result = $node->safe_psql('postgres', + "SELECT all_visible, all_frozen FROM pg_visibility_map('moved', 0)"); + is($result, 't|t', + "wal_level = $wal_level, moved visibility map survives recovery"); + return; } -- 2.43.0
