0Pricing
PostgreSQL Performance & Query Optimization · 课时

WAL 生成与写放大

测量并减少每个事务产生的 WAL 量，降低 I/O 和复制成本。

WAL 生成与写放大 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

Why WAL Volume Matters

Every change in PostgreSQL is first written to the Write-Ahead Log (WAL) before it touches the data files. WAL guarantees durability and crash recovery, but the bytes it produces are not free.

  • Disk I/O: WAL is fsynced on commit, so high WAL volume means more write throughput pressure.
  • Replication: every WAL byte must be shipped to replicas and read by logical decoders.
  • Backups: archived WAL (via archive_command or pg_receivewal) grows storage and restore time.

This lesson is about measuring the WAL each transaction generates and reducing it without sacrificing durability.

What Actually Generates WAL

WAL records are emitted for far more than just your row changes. Knowing the sources is the first step to cutting volume.

  • Heap/index changes: inserts, updates, deletes, and the index entries they touch.
  • Full Page Images (FPIs): the first write to a page after a checkpoint logs the entire 8 KB page, not just the change.
  • HOT pruning and visibility map updates.
  • Hint bit / freeze writes during VACUUM.

FPIs are usually the single biggest and most surprising contributor to write amplification.

Measuring WAL Per Statement with EXPLAIN

Since PostgreSQL 13, EXPLAIN (ANALYZE, WAL) reports exactly how much WAL a statement produced: the number of records, the number of full page images, and the total bytes.

This is the most precise tool for attributing WAL to a specific query. Watch the fpi count closely — a high FPI count signals checkpoint-driven amplification.

EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE orders
SET status = 'shipped'
WHERE shipped_at IS NULL
  AND created_at < now() - interval '1 day';

Reading the WAL Line

The WAL line in the plan looks like this:

  • WAL: records=12043 fpi=512 bytes=4823104

Interpretation:

  • records: total WAL records emitted (one or more per tuple touched).
  • fpi: full page images — each is roughly 8 KB, so 512 FPIs ≈ 4 MB of the total alone.
  • bytes: the grand total written to WAL.

If fpi × 8192 is a large fraction of bytes, your amplification is dominated by full page images, not by the logical change itself.

Cluster-Wide WAL with pg_stat_wal

For a cluster-level view, pg_stat_wal (PostgreSQL 14+) aggregates WAL generation. Sample it, run a workload, sample again, and diff.

Key columns: wal_records, wal_fpi, and wal_bytes. A rising wal_fpi rate between checkpoints confirms FPI-driven amplification at the system level.

SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) AS wal_size
FROM pg_stat_wal;

Measuring Raw Volume with WAL LSNs

You can also measure WAL produced over any interval by differencing the current Log Sequence Number (LSN). The LSN is a monotonic byte offset into the WAL stream.

Capture pg_current_wal_lsn() before and after a workload, then subtract. This counts ALL WAL, including background work like autovacuum.

SELECT pg_size_pretty(
  pg_wal_lsn_diff('0/9A3B1200', '0/95C40000')
) AS wal_generated;

Full Page Images and Checkpoint Timing

An FPI is written on the first modification of a page after a checkpoint. So checkpoints that fire too often force the same hot pages to be re-imaged repeatedly.

The fix is to spread checkpoints out:

  • Raise max_wal_size so checkpoints are triggered by volume less aggressively.
  • Raise checkpoint_timeout (e.g. 15min) so fewer time-based checkpoints occur.
  • Keep checkpoint_completion_target near 0.9 to smooth the flush, not to reduce FPIs.

Fewer checkpoints means each hot page is imaged once across a longer window — a direct cut in WAL bytes.

ALTER SYSTEM SET max_wal_size = '8GB';
ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
SELECT pg_reload_conf();

wal_compression: Shrinking FPIs

When FPIs are unavoidable, you can compress them. wal_compression compresses full page images before they are written to WAL.

  • off: no compression (default on older versions).
  • pglz: cheap, modest ratio.
  • lz4 / zstd (PostgreSQL 15+): better ratios, zstd for maximum reduction.

This trades a little CPU for potentially large WAL savings on FPI-heavy workloads. It does NOT compress regular WAL records, only the page images.

ALTER SYSTEM SET wal_compression = 'zstd';
SELECT pg_reload_conf();

-- Verify the change took effect
SHOW wal_compression;

HOT Updates Cut Index WAL

A Heap-Only Tuple (HOT) update avoids writing new index entries when no indexed column changes and the new tuple fits on the same page. Fewer index writes means less WAL.

Two levers maximize HOT:

  • Don't update indexed columns when you don't have to — narrow your SET list.
  • Leave free space on pages with a lower fillfactor so the new tuple version stays on the same page.

Check the n_tup_hot_upd ratio to confirm HOT is actually firing.

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 10;

Batching and Unlogged Tables

Two more structural reductions:

  • Batch writes: one large INSERT ... SELECT or COPY produces far less WAL overhead than thousands of single-row transactions, because per-record and per-commit overhead is amortized.
  • UNLOGGED tables: skip WAL entirely for scratch, staging, or derived data. The tradeoff is that the table is truncated after a crash and is NOT replicated.

Use unlogged tables only where losing the data on crash is acceptable — ETL staging, materialized scratch, session caches.

CREATE UNLOGGED TABLE staging_events (
    id        bigint,
    payload   jsonb,
    loaded_at timestamptz DEFAULT now()
);

Reducing Write Amplification by Design

Beyond knobs, schema and access patterns drive WAL volume:

  • Avoid wide updates: updating a row rewrites the whole tuple plus FPIs for its page. Split rarely-updated wide columns into a side table.
  • Lower fillfactor on hot tables (e.g. 80) to keep HOT updates alive.
  • Prune redundant indexes: every index multiplies write WAL.
  • Use COPY for bulk loads and consider the COPY ... FREEZE path for fresh tables.

Each design choice compounds: fewer FPIs, fewer index entries, fewer commits.

ALTER TABLE orders SET (fillfactor = 80);
-- Existing rows take effect after a rewrite
VACUUM FULL orders;

Quick Check: Diagnosing High WAL

You run EXPLAIN (ANALYZE, WAL) on a batch UPDATE and see WAL: records=20000 fpi=9800 bytes=82000000. The FPIs account for roughly 80 MB of the 82 MB total. Which single change most directly reduces this WAL volume?

Recap: Measure Then Reduce

You now have a full WAL-reduction toolkit:

  • Measure: EXPLAIN (ANALYZE, WAL) per statement, pg_stat_wal cluster-wide, and pg_wal_lsn_diff() for raw volume over an interval.
  • Read the signal: high fpi means checkpoint-driven amplification; high records with low fpi means logical change volume.
  • Reduce FPIs: raise max_wal_size and checkpoint_timeout; enable wal_compression (zstd/lz4).
  • Reduce records: favor HOT updates (narrow SET lists, lower fillfactor), batch writes, drop redundant indexes.
  • Skip WAL: UNLOGGED tables for disposable data.

Always measure first — attribute the bytes before you turn any knob.

常见问题解答

「WAL 生成与写放大」课时是免费的吗？

是的 — 「WAL 生成与写放大」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「WAL 生成与写放大」这节课中我会学到什么？

测量并减少每个事务产生的 WAL 量，降低 I/O 和复制成本。 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。

「WAL 生成与写放大」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 元组可见性、xmin 与 xmax
  2. HOT 更新与仅堆元组链
  3. 可见性映射与仅索引扫描
  4. WAL 生成与写放大
← 返回 PostgreSQL Performance & Query Optimization