One of the changes that came with PostgreSQL V.15 was that the configuration setting log_checkpoints had its default value changed from 0 to 1, meaning that now by default it was on. Ok, so what does this setting actually do?
Well it does exactly as the name suggest, it logs all checkpoints that occur in the database.
So what is exactly logged and is this information useful?
As well as logging all checkpoints and server restarts, there is also information logged about the number of buffers that were written to disk, as well as the duration of these writes.
There had been a lot of debate in the Postgres community about enabling this configuration setting by default but the general consensus was that having it on by default was probably better as it could be very useful in a debugging scenario.
Ok, so are there any downsides to this?
Well the obvious one being that you will have increased data being written to your log file. For the most part this should not really be an issue. By default in Postgres the configuration setting checkpoint_timeout which controls the time between automatic checkpoints is set to 5 minutes, but this can be as low as 30 seconds depending on your system.
Another configuration parameter that will impact the number of checkpoints that occur is the max_wal_size which determines the size that the WAL can grow to before a checkpoint will occur in between the period of your automatic checkpoints. The default of this is 1gb. So provided these are are setting to reasonable values in most cases the increased logging should not be an issue.
Ok, all sounds good so far, but in the heading you mentioned Aurora, does the same apply here? Well this is where things get a little weird.
Aurora PostgreSQL differs from Postgres in that there are two separate layers, the Database Engine and the Storage Engine, with the Storage engine handling a bunch of things that in most database would be handled by the database engine and yes you guessed it one of these is checkpoints.
Without getting to deep in the architecture of Aurora PostgreSQL, that would be a blog on its own, most relational databases are page structured, which means they have a log buffer in memory that is written to, its contents are written to disk either when it becomes full, a transaction is committed or during a checkpoint, writing to the log on disk ensures durability. A checkpoint is also responsible for writing out the dirty (modified) data pages from memory to disk, these could 4kb or 8kb pages.
Aurora differs from these databases systems in that it is log-structured, which means that it does not contain a log buffer, all log changes are written straight to disk and no dirty pages are ever written from memory to disk, i.e. so no need for any checkpoints. Wait what? So yes you read that correctly no checkpoints occur in the database engine. But! And this is a big but, checkpoints do occur in the storage engine. But this is completely handled by AWS and again as this is not a post about Aurora architecture I’ll leave it at that. (On a side note, log-structured database systems, can give you more consistent write performance, yes you are writing to disk for each write which will increase I/O’s as you do not have a log buffer but you don’t periodically pay the price of doing a checkpoint where you have to do these large writes of 4kb or 8kb pages.)
So what does this mean for the configuration setting log_checkpoints in Aurora?
Well, there is not much point in having it enabled since no checkpoints occur in the database engine and so essentially what gets logged is useless See example.
LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.001 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=0 kBSeeing the above every couple of minutes in your log file is not particularly useful.
So I know what you are thinking, Amazon must take care of this and in Aurora this setting is off by default, am that would be a No! Ok, bit of a pain but we can turn it off right, can’t we? What, we can’t?
Yes that is correct, if you look into your Parameter Groups the setting for log_checkpoints is not there, so it cannot be turned off. Not an ideal situation.
I have raised this with AWS who acknowledged it was an issue and raised it with their internal team but as of the time of writing this blog post there is no date for this being fixed.
UPDATE ** 25-10-23 The AWS team got back to me that the the issue is now fixed in v 15.4 where the setting log_checkpoints is ‘OFF’ by default. Good stuff 🙂
So there you go folks, hope you found this post useful.
Until next time, Slán!
Disclaimer: Never just believe everything you read in a post, test this out for yourself, we all can make mistakes! Happy coding.
The Front-line Database Blog 