Re: Raising the checkpoint_timeout limit

On 2/2/16 11:10 PM, Robert Haas wrote:
> On Tue, Feb 2, 2016 at 10:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I've gotta go with the "paternalism" side of the argument here. Suppose
>> you configure your system to checkpoint once a year --- what is going to
>> happen when the year is up? Or when you try to shut it down? You *will*
>> regret such a setting.
>>
>> I don't think we should allow the checkpoint distances to be so large that
>> checkpoints don't happen in the normal course of events. I'd be okay with
>> the max being a day, perhaps.
>
> If smart people[1] want to set checkpoint_timeout to a value higher
> than 1 day[2], then I think we should let them.

Agreed - I have a specific instance where I would prefer the daily
backups or checkpoint segments to be the primary source of checkpoints
with checkpoint_timeout set to 36 hours and used only as a fallback.

A limit of 1 day would be lower than I like though still better than
what I have now, 1 hour.

For this case I would probably configure:

checkpoint_segments = 256
checkpoint_timeout = 36h

So the daily backups would generally trigger the checkpoint unless there
was unusually high volume in which case it would be checkpoint segments.
Finally, if the backups weren't working and volume was normal then
checkpoint_timeout would come along to save the day.

In this case its all about reducing full-page writes on a medium-sized
system with heavy churn in order to make a long backup/archive retention
more cost-effective. Recovery time is not as much of an issue for this
application.

--
-David
david(at)pgmasters(dot)net