ReaderMX.
PostgreSQL码农集散地

PG 19 支持 FPI 跟踪, 再也不怕垃圾回收偷存储空间了

Pdigoal约 4 分钟
什么? 垃圾回收偷跑存储空间? 这话从何说起?

PG 19 支持 FPI 跟踪, 再也不怕垃圾回收偷跑存储空间了

checkpoint 的逻辑开始位置后第一次被修改的block, 需要在wal中记录full page, 这么做是防止因为断电等原因产生的数据库crash后, data block出现partial write, 导致数据损坏.

也可以防止在线物理备份时的patrial read的坏块.

因为checkpoint是有跨度的, 例如10点开始, 10点5分结束, 那到底是哪个位置呢?

checkpoint 的完成位置指的是逻辑开始位置(redo point),而不是逻辑结束位置。

判断是否需要记录 full page image 的关键逻辑是:比较 page 的 LSN 与 RedoRecPtr。

具体来说:

  1. RedoRecPtr 是 checkpoint 的 redo point,即 WAL replay 的起始位置,这是 checkpoint 的逻辑开始位置。

  2. 判断逻辑: 如果 page_lsn <= RedoRecPtr,则需要记录 full page image。

  3. 在线 checkpoint 流程:

  • 首先插入 XLOG_CHECKPOINT_REDO 记录,其 LSN 成为新的 redo point
  • 然后刷新所有脏页
  • 最后插入 XLOG_CHECKPOINT_ONLINE 记录标记 checkpoint 完成

  • 实际应用: 在 checkpoint 开始时,RedoRecPtr 就被更新为新的 redo point。 之后所有 page 的第一次修改都会与这个 RedoRecPtr 比较来决定是否需要 full page write。

    • Notes

    文档中明确说明: "We can detect the 'first change after checkpoint' by noting whether the page's old LSN precedes the end of WAL as of the last checkpoint (the RedoRecPtr)."  这里的 RedoRecPtr 就是 checkpoint 的 redo point, 即逻辑开始位置。

    在序列(sequence)的实现中也可以看到同样的逻辑: PageGetLSN(page) <= redoptr 用于判断是否需要记录 WAL。

    PostgreSQL 19 preview - 跟踪 vacuum, analyze 生成的 wal full page write (fpi)

    https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f9a09aa29520

    Add wal_fpi_bytes to pg_stat_wal and pg_stat_get_backend_wal()  
    author  Michael Paquier <[email protected]>   
    Tue, 28 Oct 2025 07:21:51 +0000 (16:21 +0900)  
    committer Michael Paquier <[email protected]>   
    Tue, 28 Oct 2025 07:21:51 +0000 (16:21 +0900)  
    commit  f9a09aa2952039a9956b44d929b9df74d62a4cd4  
    tree  c68534ad90e49cc537e99fa5193c74454ec9f3b1  tree  
    parent  3e8e05596a020f043f1efd6406e4511ea85170bd  commit | diff  
    Add wal_fpi_bytes to pg_stat_wal and pg_stat_get_backend_wal()  
    This new counter, called "wal_fpi_bytes", tracks the total amount in
    bytes of full page images (FPIs) generated in WAL.  This data becomes  
    available globally via pg_stat_wal, and for backend statistics via  
    pg_stat_get_backend_wal().  
    Previously, this information could only be retrieved with pg_waldump or  
    pg_walinspect, which may not be available depending on the environment,  
    and are expensive to execute.  It offers hints about how much FPIs  
    impact the WAL generated, which could be a large percentage for some  
    workloads, as well as the effects of wal_compression or page holes.  
    Bump catalog version.  
    Bump PGSTAT_FILE_FORMAT_ID, due to the addition of wal_fpi_bytes in
    PgStat_WalCounters.  
    Author: Shinya Kato <[email protected]>  
    Reviewed-by: Michael Paquier <[email protected]>  
    Discussion: https://postgr.es/m/CAOzEurQtZEAfg6P0kU3Wa-f9BWQOi0RzJEMPN56wNTOmJLmfaQ@mail.gmail.com  

    https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ad25744f436ed7809fc754e1a44630b087812fbc

    Add wal_fpi_bytes to VACUUM and ANALYZE logs  
    author  Michael Paquier <[email protected]>   
    Mon, 3 Nov 2025 10:42:03 +0000 (19:42 +0900)  
    committer Michael Paquier <[email protected]>   
    Mon, 3 Nov 2025 10:42:03 +0000 (19:42 +0900)  
    commit  ad25744f436ed7809fc754e1a44630b087812fbc  
    tree  9ba1920a0b18af1de21061edbbfcc4138712d22f  tree  
    parent  fce7c73fba4e5e3014c27b8980aa07511d6e0f85  commit | diff  
    Add wal_fpi_bytes to VACUUM and ANALYZE logs  
    The new wal_fpi_bytes counter calculates the total amount of full page  
    images inserted in WAL records, in bytes.  This commit adds this  
    information to VACUUM and ANALYZE logs alongside the existing counters,  
    building upon f9a09aa29520.  
    Author: Shinya Kato <[email protected]>  
    Reviewed-by: Michael Paquier <[email protected]>  
    Discussion: https://postgr.es/m/[email protected]  

    KEEP THE THREAD GOING

    更多来源文章