The pg_stat_checkpointer view in Postgres 17
Today, we're going to talk about the new pg_stat_checkpointer in Postgres 17, and how that changed the pg_stat_bgwriter, or background writer, statistics. We are also looking at the impact these changes have on shared buffers, the Postgres WAL, and pg_stat_io.
Share this episode: Click here to share this episode on LinkedIn or on twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel.
Transcript
pg_stat_checkpointer and its impact on pg_stat_bgwriter
To start with, we’ll have a look at this blog post by Daniel Westermann on the DBI Services blog. In this blog post, Daniel talks about a recent change to Postgres 17, next year's Postgres release, where they've clarified the statistics for the background writer and checkpointer processes in Postgres.
How this works in Postgres 16
To recap, today, in Postgres 16, you have a couple of columns in this view. They all relate to write activity in Postgres.
But, there are different processes in Postgres that are independent of each other. The most important thing to understand here, for context, is that back in 2011 there was a change in Postgres where background writer and checkpointer were split into two separate processes.
As that commit message from back then said, the background writer is now a much less important process. Historically, the background writer did everything, that's why the stats view was called like that.
The role of the checkpointer process
But in the last 12 years that's not been the case anymore. For those of you who are not familiar, the difference in those two processes is that the checkpointer process issues what are called checkpoints in Postgres, these checkpoints occur either on a time-based basis, or based on the amount of WAL that has been written.
The role of a checkpoint is that you can recover your database after a crash to a consistent starting point where we know the exact state of the data directory and then we can replay the WAL since that point in time to come to a consistent state in memory.
Now, the checkpointer process issues a lot of I/O because it has to do a lot of fsyncs and write data to disk. It's something where you do care a lot about how often the checkpoints run, why they run, and you want to spread out the I/O load over time. It's a very important process to monitor from a write I/O perspective in Postgres.
The role of the background writer
The background writer on the other hand is a very special purpose process. For that, it's important to take a quick step back and remember how pages work in Postgres shared buffers.
When you're making a write in Postgres, the write will update the Postgres WAL, which is the part that's essentially persisted right away in order to guarantee durability. Additionally to the WAL, the shared buffers is also updated, and so you have your page from your table in shared buffers, that one is also updated, but it may not be written to disk yet, and so it's marked as dirtied in the shared buffers.
Now, there are actually multiple ways a dirtied page in shared buffers can move to disk. Either the checkpointer can do it when it comes time to do a checkpoint, or individual connections can do it because sometimes when you're reading data, you need space in shared buffers and if the only thing that you can evict easily is a dirtied page, then Postgres will actually make a write whilst you're doing a read. And so that second class is actually a very particular odd performance behavior.
Sometimes, you're doing a SELECT and suddenly you're seeing a write. It's because of that dirtied page in shared buffers. Now last, and that's really where the background writer comes in, the background writer periodically writes out these dirtied pages in between checkpoints. The motivation there is that it avoids individual connections having that overhead of having to write out a dirty page just to be able to read or write their own data.
pg_stat_bgwriter in Postgres 17
Coming back to the change in Postgres 17, we can see that this is a lot clearer now. Instead of having all these rows in the pg_stat_bgwriter view, we just have statistics around background writer in pg_stat_bgwriter, so you can know how much the background writer has written and then more importantly for the checkpointer, which oftentimes you do need to tune. It is very clear now how many checkpoints were time based checkpoints, versus WAL based checkpoints and how long they took and how many buffers they wrote.
One other thing I'll mention: this comes back to these three different kinds of ways of how dirtied pages actually get to disk. You'll notice that there's actually two columns that are no longer in either pg_stat_bgwriter or pg_stat_checkpointer. Those are those buffers_backend and buffers_backend_fsync columns. Those two are now removed from pg_stat_bgwriter, and instead you can find that information in pg_stat_io.
Overall, I think this is a great change. There was some discussion on backwards compatibility issues by breaking monitoring queries, but I do think that making this very clear to end users and saying that if they're interested in checkpointer statistics to look at pg_stat_checkpointer, is a good change and will make it much less confusing for those of us who try to understand when the background writer is active, when the checkpointer is active, or when an individual connection has to make an unexpected write.
If you liked E90 of 5mins of Postgres, feel free to subscribe to our YouTube channel, sign up for our newsletter and follow us on LinkedIn and Twitter to get updates about new episodes!