Re: Raising the checkpoint_timeout limit

On 2016-02-01 23:16:16 -0500, Noah Misch wrote:
> On Tue, Feb 02, 2016 at 01:13:20AM +0100, Andres Freund wrote:
> > I'm not sure what'd actually be a good upper limit. I'd be inclined to
> > even go to as high as a week or so. A lot of our settings have
> > upper/lower limits that aren't a good idea in general.
> 
> In general, I favor having limits reflect fundamental system limitations
> rather than paternalism.  Therefore, I would allow INT_MAX (68 years).

I generally incree with that attitude - I'm disinclined to go just that
high though. Going close to INT_MAX means having to care about overflow
in trivial computations, in a scenario we're unlikely to ever
test. Sure, we can use a debugger to adjust time or accellerate time
progress, but that's all unrealistic if we're honest.  So maybe go with
a year?

> > I'm also wondering if it'd not make sense to raise the default timeout
> > to 15min or so. The upper ceiling for that really is recovery time, and
> > that has really shrunk rather drastically due to faster cpus and
> > architectural improvements in postgres (bgwriter, separate
> > checkpointer/bgwriter, restartpoints, ...).
> 
> Have those recovery improvements outpaced the increases in max recovery time
> from higher core counts generating more WAL per minute?

Mostly yes, imo. I think the biggest problem with max recovery time is
in workloads that are considerably bigger than shared buffers: There the
single threaded, synchronously reading, startup process (without the
benefit of FPWs filling up pages), has to compete with a lot of
processes having higher IO throughput, because of multiple processes, at
the same time.  But even that has considerably improved due to SSDs.

Andres