Ottimizzazione di WAL e checkpoint per l’acquisizione

Every change you commit in PostgreSQL is first written to the Write-Ahead Log (WAL) before the data files are updated. This guarantees durability and crash recovery, but during heavy bulk loads the WAL becomes a major source of I/O.

• Each INSERT or COPY generates WAL records.
• Periodically a checkpoint flushes dirty pages from shared buffers to disk.
• If checkpoints fire too often, you pay double I/O and get stalls.

Tuning WAL and checkpoint behaviour is the key to sustaining high write rates without I/O spikes.

Before changing anything, look at what your server is running with. The relevant knobs live in pg_settings and can be queried with SHOW.

The most important ingestion-related parameters are max_wal_size, checkpoint_timeout, checkpoint_completion_target, and wal_compression.

A checkpoint is triggered either by checkpoint_timeout elapsing or by WAL volume reaching max_wal_size. During a big load the default (often 1 GB) is hit constantly, forcing checkpoint after checkpoint.

• Raising max_wal_size lets WAL accumulate longer between checkpoints.
• Fewer checkpoints means dirty pages get coalesced and written once instead of repeatedly.

For an ingestion window, values like 8–32 GB are common.

checkpoint_completion_target controls how much of the interval PostgreSQL uses to spread out the checkpoint writes. A value of 0.9 means the writes are smeared across 90% of the time until the next checkpoint, avoiding a sharp I/O burst.

Combined with a longer checkpoint_timeout, this turns spiky checkpoint storms into a smooth, sustained write stream.

When full-page images are written after a checkpoint (the first modification of a page), they bloat the WAL. wal_compression compresses those full-page images, trading a little CPU for substantially less WAL volume and disk I/O.

• On modern PostgreSQL you can choose the algorithm, e.g. lz4 or zstd.
• Less WAL written also means faster replication and fewer checkpoints from the size trigger.

An unlogged table writes no WAL at all. For staging tables in an ETL pipeline this can dramatically increase throughput, because you bypass the single biggest write cost.

• Data is still written to disk, but not durably logged.
• Trade-off: the table is truncated automatically after a crash and is not replicated to standbys.

Perfect for re-buildable staging data; never for the system of record.

A robust ETL design loads raw rows into a fast unlogged staging table, transforms them, then moves the cleaned result into the durable final table.

• The bulk COPY hits the unlogged table at full speed.
• The final INSERT ... SELECT writes WAL only once, for validated data.

You get speed where durability does not matter and safety where it does.

If a staging table needs to become durable after the load completes, you can convert it in place instead of copying rows. Setting it to LOGGED rewrites the table and begins WAL-logging it.

• The conversion itself generates WAL for the whole table, so do it once at the end.
• Going back to UNLOGGED before the next load avoids per-row WAL again.

Even with WAL tuned, how you load matters. COPY batches rows into far fewer, larger WAL records than thousands of individual INSERT statements, and avoids per-statement parse and plan overhead.

Combine COPY with an unlogged staging table and you reach the highest sustainable ingest rate.

To know whether your tuning worked, watch the checkpoint statistics. The key signal is the ratio of requested (size-triggered) checkpoints to timed ones.

• Many requested checkpoints means max_wal_size is still too small for your load.
• Mostly timed checkpoints means WAL volume is comfortably within budget.

In newer versions these counters live in pg_stat_checkpointer; older versions use pg_stat_bgwriter.

Aggressive ingestion settings are great during a load window but waste recovery time and disk afterwards. Once the batch finishes, restore conservative values and force a clean checkpoint so the next crash recovery is fast.

• Lower max_wal_size and checkpoint_timeout back to steady-state values.
• Run a manual CHECKPOINT to flush everything immediately.

To sustain high write rates without I/O stalls:

• Raise max_wal_size and checkpoint_timeout so checkpoints fire less often, and set checkpoint_completion_target near 0.9 to spread the writes.
• Enable wal_compression to shrink full-page images.
• Use UNLOGGED staging tables to skip WAL for re-buildable data, then move validated rows into the durable table.
• Prefer COPY over row-by-row inserts.
• Monitor pg_stat_checkpointer for requested-vs-timed checkpoints, and reset conservative values after the load.