Postgres 19 is just a smorgasbord of new functionality; it's genuinely hard to believe they packed all these new features into a single release. To that laundry-list of new capabilities, it adds something I almost missed. We all know and love the Postgres Write Ahead Log (WAL), where all writes begin their lives. I even recently wrote about how the background checkpoint system can overwhelm storage when not properly tuned.

What about the times when a DBA wants to purposefully invoke a manual CHECKPOINT to flush any pending writes to the heap? Just one quick command and Postgres invokes an immediate flush, then returns control once the dust settles. Until Postgres 19, that was the entire interface. CHECKPOINT. It's like a Zen koan.

It turns out there were a few tricks we could teach this old dog.

A Blunt Instrument

A bare CHECKPOINT means "reconcile everything immediately, and don't come back until it's done." There are a few obvious scenarios where DBAs may execute this kind of targeted checkpoint:

  • Before taking a backup so the base image starts from a known-good state.

  • Prior to a pg_upgrade so the old cluster is fully settled.

  • Between benchmark passes so each run starts from the same clean slate. We even leverage that approach several times in this very article.

In every one of those cases, "as fast as possible" is precisely the intent. Given that only superusers or members of the pg_checkpoint predefined role can use it, that seems about right.

The trouble is that its single behavior was also its only behavior. When the only tool you have is a hammer, everything becomes a nail. As we learned from the write storm article, sometimes a full and immediate flush is the last thing a busy server needs. If our checkpoint_timeout means the next checkpoint won't happen for a while, maybe there needs to be an option to start the process early.

And now with Postgres 19, there is.

A Challenger Appears

Improvements to the CHECKPOINT command in Postgres 19 came in three stages. The first added nothing user-visible at all. It simply taught the command to accept a parenthesized option list, the same ( option value, ... ) design from VACUUM, COPY, and EXPLAIN. The actual options arrived in the two commits that followed.

The new grammar looks like this:

CHECKPOINT [ ( option [, ...] ) ]

Two options are recognized so far: MODE and FLUSH_UNLOGGED. It's possible to combine them in a single statement separated by a comma, in either order:

CHECKPOINT (MODE SPREAD, FLUSH_UNLOGGED FALSE);

Postgres enforces the grammar in a few ways. The parentheses are optional, but can't be empty if provided. Postgres also flags any invalid or unrecognized parameters:

CHECKPOINT (WAIT FALSE);
ERROR:  unrecognized CHECKPOINT option "wait"

CHECKPOINT (MODE BOGUS);
ERROR:  unrecognized value for CHECKPOINT option "mode": "bogus"

Newer psql clients also contain the usual helpful inline documentation:

\h CHECKPOINT

Command:     CHECKPOINT
Description: force a write-ahead log checkpoint
Syntax:
CHECKPOINT [ ( option [, ...] ) ]

where option can be one of:

    FLUSH_UNLOGGED [ boolean ]
    MODE { FAST | SPREAD }

URL: https://www.postgresql.org/docs/19/sql-checkpoint.html

As usual, that's one of the reasons Postgres veterans find it hard to eschew psql in favor of other clients. It's just such a good CLI already!

A La MODE

The second phase of our new CHECKPOINT introduced the MODE option. It takes one of two values.

  • FAST is the default, and it's the old behavior to the letter: complete the checkpoint as quickly as the hardware allows, blocking the session until every dirty buffer is synced. This is still what CHECKPOINT does by default, but the implied command is actually CHECKPOINT (MODE FAST).

  • SPREAD is the new capability. Instead of flooding the writes through all at once, it paces them out over a longer window, as defined by checkpoint_completion_target and checkpoint_timeout. It's essentially the same thing as an automatic background checkpoint, just triggered earlier than usual.

We can even examine how these modes differ in practice. Let's create a table with two-million rows, that ends up being about 517MB:

CREATE TABLE bench (id bigint PRIMARY KEY, payload text);

INSERT INTO bench
SELECT g, repeat('x', 200)
  FROM generate_series(1, 2000000) g;

CHECKPOINT;

SELECT pg_size_pretty(pg_total_relation_size('bench'));

pg_size_pretty 
----------------
 517 MB

Then we can dirty half of the pages in the table and request another checkpoint:

\timing on

UPDATE bench
   SET payload = repeat('y', 200)
 WHERE id % 2 = 0;

Time: 11390.109 ms (00:11.390)

CHECKPOINT (MODE FAST);

Time: 414.732 ms

A little over four hundred milliseconds, and the I/O all happened in that brief window. Now let’s try the same amount of dirty data, flushed with SPREAD (after recreating the table for a fair comparison):

\timing on

UPDATE bench 
   SET payload = repeat('z', 200)
 WHERE id % 2 = 1;

Time: 11166.044 ms (00:11.166)

CHECKPOINT (MODE SPREAD);

Time: 130135.722 ms (02:10.136)

That's over two minutes to flush the same amount of writes. Exactly as expected since Postgres is deliberately pacing the writes so the storage subsystem never sees a spike. The command still waits for completion before returning, so there's no ambiguity over when the flush has truly finished.

Two Men Enter, One Man Leaves

There's an interesting little quirk here that's easy to miss. What happens when two (or more) of these checkpoint requests collide? As it turns out, the server may consolidate them. 

Consider two sessions seeking a checkpoint at nearly the same moment. If one requests FAST and the other prefers SPREAD, Postgres doesn't run two checkpoints back to back. Instead, it runs a single checkpoint with the attributes of the most urgent request. In this case, FAST wins out and we end up with an immediate checkpoint. The same logic applies to FLUSH_UNLOGGED, which we’ll cover in depth soon. If one session requests flushing unlogged buffers, that gets applied to the consolidated checkpoint.

As a result, these options are more of a request than a contract. We can request a spread checkpoint or choose to ignore unlogged buffers, but any other session can effectively override those preferences. It's not totally unexpected, but something to keep in mind.

Unlogged Tables

The third and final phase of our new CHECKPOINT added a second option called FLUSH_UNLOGGED. For the unfamiliar, Postgres provides unlogged tables which skip writing changes to the WAL. That makes writes dramatically cheaper with one major caveat: contents don't survive a crash. After an unclean shutdown, every unlogged table gets truncated to empty, because without WAL there's no way to guarantee its pages are consistent. Additionally, such tables can't be replicated.

That bargain has a consequence for checkpoints. Since an unlogged table's data is forfeit on a crash anyway, an ordinary checkpoint doesn't bother flushing its dirty buffers to disk. Why pay for I/O on data that crash recovery will simply discard? As a result, those buffers sit in memory, ignored by checkpoint after checkpoint, and only get written out at a clean shutdown. 

We can watch that happen using the buffers_written counter from pg_stat_checkpointer. Consider this short experiment using an unlogged table:

CREATE UNLOGGED TABLE u_bench (id bigint PRIMARY KEY, payload text);

INSERT INTO u_bench
SELECT g, repeat('x', 200)
  FROM generate_series(1, 2000000) g;

CHECKPOINT;

Then like the logged variant above, we can modify the contents and invoke a plain checkpoint:

SELECT buffers_written FROM pg_stat_checkpointer;

 buffers_written 
-----------------
          203700

UPDATE u_bench
   SET payload = repeat('y', 200)
 WHERE id % 2 = 0;

CHECKPOINT;

SELECT buffers_written FROM pg_stat_checkpointer;

 buffers_written 
-----------------
          203700

No change. The checkpoint ran, dutifully reported success, and wrote none of those pages. For contrast, the identical operation against a regular logged table:

SELECT buffers_written FROM pg_stat_checkpointer;

 buffers_written 
-----------------
          219726

UPDATE bench
   SET payload = repeat('y', 200)
 WHERE id % 2 = 0;

CHECKPOINT;

SELECT buffers_written FROM pg_stat_checkpointer;

 buffers_written 
-----------------
          236537

Now we see a difference of 16,811 buffers. Logged data always gets flushed; unlogged data gets skipped. Now we can incur the new FLUSH_UNLOGGED option against the same dirty unlogged table:

SELECT buffers_written FROM pg_stat_checkpointer;

 buffers_written 
-----------------
          241991

UPDATE u_bench
   SET payload = repeat('y', 200)
 WHERE id % 2 = 0;

CHECKPOINT (FLUSH_UNLOGGED TRUE);

SELECT buffers_written FROM pg_stat_checkpointer;

 buffers_written 
-----------------
          255980

This time we see a total of 13,989 buffers written, showing the pages actually land on disk. While it's disabled by default, there are a few scenarios where it might make sense to flush an unlogged table manually. Perhaps we're about to do a controlled, clean shutdown and would rather pay the flush cost early. Maybe data staged in an unlogged table should be physically persisted before some external process inspects the data directory. Whatever the reason, the choice is now available rather than forced by the engine.

Looking Forward

None of this changes what a checkpoint fundamentally is. The automatic checkpointer still hums along on its schedule, and the vast majority of installations will never need to type any of these new options. This is a small feature, and the documentation page is barely longer than it was before.

But it's the kind of incremental improvement that DBAs probably notice. The manual checkpoint went from a single blunt instrument to something suitable to finer precision. Fast for hard flushes before a backup or an upgrade, spread to nudge the system without jolting it, and an option to flush unlogged tables to avoid unexpected shutdown delays. Christoph Berg's work here decorates the formerly unadorned CHECKPOINT we know and love, and it does so without breaking a single existing script.

Postgres 19 is replete with such quality-of-life improvements, and we'll be exploring a few more of them in the weeks ahead. For now, if you've got a beta build handy, spin up a table and watch MODE SPREAD take its sweet time. Sometimes slower really is better.