[译] 了解 PostgreSQL Vacuums 中的高水位锁定问题

我最近有个客户，他想利用只读副本来确保他们的读取查询不会因为主实例上的工作而受到影响，并且要求服务等级协议 (SLA) 至少能保证几秒钟的延迟。最终，他们没有达到 SLA 的要求，我和我的同事被要求查看到底发生了什么。

我们首先了解到的是，主数据库的工作模式是相当频繁的大型DELETE语句，然后通过针对具有 16 个哈希分区的分区表的COPY从STDIN命令完成数据刷新。

我们观察到的问题是，只读副本上执行的SELECT 语句会周期性地超时，从而无法满足 SLA 的要求。经过调查，我们发现只读副本上的“启动”进程会周期性地请求某个随机分区上的“排他锁”。这个排他锁会阻塞SELECT 语句（该语句不感知分区），从而导致超时。那么，是什么导致了超时呢？

经过一段时间的调查，团队最终将排他锁与主服务器上发生的例行“自动清理”关联起来。但为什么会锁住呢？检查 WAL 后，发现问题出在清理过程中的一个步骤上，该步骤试图将表末尾的空闲页面返回给操作系统，即截断高水位标记 (HWM)。本质上，主服务器上会请求锁，然后通过 WAL 传输到副本服务器，以便表保持一致。

为了确认这确实是VACUUM中截断 HWM 的步骤，我们决定修改表的每个分区以允许VACUUM跳过该步骤：

	
ALTER TABLE [table name / partition name] SET (vacuum_truncate = false);

运行 24 小时后，我们实际上没有看到进一步的阻塞锁导致副本上的 SLA 未命中。我们应该担心缩小高水位线 (HWM)吗？就像 IT 中的所有事情一样，这取决于具体情况。其他 DBMS 引擎（例如 Oracle）不会缩小高水位线 (HWM)，通常使用 DBMS_REDEF 或 ALTER TABLE … SHRINK SPACE / SHRINK SPACE COMPACT 等维护操作来处理这个问题。既然我们在讨论 PostgreSQL，我们需要担心它吗？这时 pg_freespacemap 扩展就可以提供帮助。我们可以使用此扩展和脚本来检查高水位线 (HWM)实际上是在增长还是保持不变。如果它在增长，我们只需执行常规 VACUUM 并使用名为 TRUNCATE 的附加选项来处理它：

VACUUM (verbose, truncate) [schema].[table name];

执行此操作时，您将在 VACUUM 输出中看到一条附加消息，表示 VACUUM 截断了高水位标记（HWM）：

INFO:  table "large_table": truncated 302534 to 302233 pages

正如我之前所说，我们可以使用 pg_freespacemap 来查看是否真的需要担心高水位线 (HWM) 的增长。我本来可以花很多时间编写脚本来解决这个问题，但我还是使用了 Google Gemini 来查看它的结果。经过几次迭代，输出结果几乎完美！

CREATE EXTENSION pg_freespacemap;
 
CREATE OR REPLACE FUNCTION show_empty_pages(p_table_name TEXT)
RETURNS VOID AS $$
DECLARE
    -- Core processing variables
    table_oid_regclass  REGCLASS;
    block_size          BIGINT;
    fsm_granularity     BIGINT;
    max_fsm_free_space  BIGINT;
    total_pages         BIGINT;
    high_water_mark     BIGINT := 0;
 
    -- Variables for the final summary
    first_empty_block   BIGINT;
    free_pages_at_end   BIGINT;
    free_space_at_end   TEXT;
BEGIN
    -- Setup
    table_oid_regclass := p_table_name::regclass;
    block_size  := current_setting('block_size')::bigint;
    SELECT relpages INTO total_pages FROM pg_class WHERE oid = table_oid_regclass;
    fsm_granularity    := block_size / 256;
    max_fsm_free_space := floor((block_size - 24) / fsm_granularity) * fsm_granularity;
 
    --------------------------------------------------------------------------------
    -- PASS 1: FIND THE HIGH-WATER MARK (last page with data)
    --------------------------------------------------------------------------------
    FOR i IN REVERSE (total_pages - 1)..0 LOOP
        IF pg_freespace(table_oid_regclass, i) < max_fsm_free_space THEN
            high_water_mark := i;
            EXIT;
        END IF;
    END LOOP;
 
    --------------------------------------------------------------------------------
    -- FINAL STEP: CALCULATE AND RAISE THE SUMMARY NOTICE
    --------------------------------------------------------------------------------
    first_empty_block := high_water_mark + 1;
    free_pages_at_end := total_pages - first_empty_block;
    IF free_pages_at_end < 0 THEN
        free_pages_at_end := 0;
    END IF;
    free_space_at_end := pg_size_pretty(free_pages_at_end * block_size);
 
    RAISE NOTICE '-------------------------------------------------------------';
    RAISE NOTICE 'Summary for table: %', p_table_name;
    RAISE NOTICE '-------------------------------------------------------------';
    RAISE NOTICE 'The High Water Mark (HWM) is at page: %', total_pages;
    IF total_pages <> first_empty_block THEN
        RAISE NOTICE 'First potentially empty page is at: %', first_empty_block;
        RAISE NOTICE 'Total Pages in Table: %', total_pages;
        RAISE NOTICE 'Number of potentially truncatable pages at the end: %', free_pages_at_end;
        RAISE NOTICE 'Amount of free space at the end of the table: %', free_space_at_end;
    ELSE
        RAISE NOTICE 'There are no empty pages to truncate';
    END IF;
    RAISE NOTICE '-------------------------------------------------------------';
END;
$$ LANGUAGE plpgsql;

这个方便的脚本可以定期执行来检查高水位标记（HWM），并将产生以下输出：

(postgres@10.3.1.17:5432) [postgres] > SELECT * FROM show_empty_pages('public.large_table');
NOTICE:  -------------------------------------------------------------
NOTICE:  Summary for table: public.large_table
NOTICE:  -------------------------------------------------------------
NOTICE:  The High Water Mark (HWM) is at page: 302534
NOTICE:  First potentially empty page is at: 302233
NOTICE:  Total Pages in Table: 302534
NOTICE:  Number of potentially truncatable pages at the end: 301
NOTICE:  Amount of free space at the end of the table: 2408 kB
NOTICE:  -------------------------------------------------------------

如果最后一个完整块后没有可用空间，则输出将如下所示：

NOTICE:  -------------------------------------------------------------
NOTICE:  Summary for table: public.large_table
NOTICE:  -------------------------------------------------------------
NOTICE:  The High Water Mark (HWM) is at page: 302233
NOTICE:  There are no empty pages to truncate
NOTICE:  -------------------------------------------------------------

因此，虽然没有正确答案来解决这个问题，但请确保您了解流程中每个步骤的含义。在本例中，我们决定将“vacuum_truncation”选项设置为 false，但也许还有其他选择，例如以其他方式调整清理操作，例如提高或降低清理频率。请务必评估自身情况，但无论如何，了解执行某些命令时数据库中发生的情况总是有益的。

原文地址：https://shaneborden.com/2025/06/06/understanding-high-water-mark-locking-issues-in-postgresql-vacuums/
原文作者：ShanBorden