Re: should we enable log_checkpoints out of the box?

On Tue, Nov 2, 2021 at 8:02 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I'm still of the position that the default ought to be that a
> normally-functioning server generates no ongoing log output.
> Only people who have got Nagios watching their logs, or some
> such setup, are going to want anything different. And that is
> a minority use-case. There are going to be way more people
> bitching because their postmaster log overflowed their disk
> than there will be people who are happier because you made
> such output the default. (Don't forget that our default
> logging setup does not rotate the logs.)

I mean, you still seem to be postulating a setup where none of the
user activity ever causes any logging messages to get generated, which
is just not the way real systems behave. And as to log volume, if you
log a checkpoint every 5 minutes, generating roughly 1/4 kB of log
output each time, that's ~3kB/hour, or ~27MB/year.

Just for fun, I went and had a look at the least-used PostgreSQL
instances that I know about, which is the one running on my Raspberry
Pi. It powers a little web site that I wrote and people sometimes use
that web site but not that often. It's running PostgreSQL 9.4.5
because it's not an important enough system that I can be bothered to
upgrade it. The OS that came preinstalled is some Debian derivative
that arranges to rotate the logs automatically, so I only have logs
going back to May. For the most part, it generates pretty much nothing
in the logs, although back end of May/early June it kicked out about
56MB of logs. I guess I must have temporarily enabled
log_statement=all to debug something or other.

Anyway, there are three points I'd like to make about this machine.
The first is that the root partition, where pretty much all the files
are located on this tiny box, is 6GB. It currently has 825MB of free
space. If you enabled log_checkpoints=on on this machine, first of all
I suppose most of the time it wouldn't log anything because there's
usually no activity and thus the checkpoint could just be skipped.
Second, even if it did checkpoint every 5 minutes and generate
27MB/year of logs, that would take roughly 30 years to free up the
825MB of free space I have on the root partition. I doubt this machine
will be around in 30 years; it's uncertain that *I'll* be around in 30
years. Third, this machine came with PostgreSQL log compression and
rotation set up by the operating system, which I think is pretty good
evidence that configurations including those features are common. The
compression ratio on the logs this machine is generating seems to be
about 98.5%. After compression, the overhead of the log_checkpoints
output for the three months of logs that it keeps - assuming it had
enough activity to checkpoint every 5 minutes - would be perhaps
100kB, which is not only little enough not to be a problem on this
machine, but also little enough not to be a problem on the first IBM
PC that I ever used, back in the 1980s.

If log_checkpoints=on wouldn't fill up the disk on my 5+-year old
Raspberry Pi in less time that it takes to raise multiple children to
adulthood even after disabling the OS-provided log rotation and
compression, then it seems more than fair to say that for the vast
majority of users, this isn't a real problem. And for those few for
whom it *is* a real problem, they can still shut off log_checkpoints.
It's not like anybody is proposing to remove the option.

--
Robert Haas
EDB: http://www.enterprisedb.com