Re: Raising the checkpoint_timeout limit

On Tue, Feb 2, 2016 at 1:16 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> On Tue, Feb 02, 2016 at 01:13:20AM +0100, Andres Freund wrote:
>> is there any reason for the rather arbitrary and low checkpoint_timeout
>> limit?
>
> Not that I know, and it is inconvenient.
>
>> 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).

+1. This way users can play as they wish.

>> 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?

Perhaps having some numbers showing that the architecture improvements
of Postgres really matter at constant checkpoint_segments for pgbench
load would help to get into a better default value. I would tend to
agree that as things speed up it would make sense to increase this
value a bit though, even if Postgres is usually conservative enough in
default parameters aimed at low-spec machines.
--
Michael