Re: Raising the checkpoint_timeout limit

On 2/1/16 6:13 PM, 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.

The only reason I can see for the 1 hour limit is to try and prevent
footguns. I think that's a valid goal, but there should be a way to
over-ride it. And if we don't want that kind of protection then I'd say
just yank the upper limit.

> 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, ...).

It would be interesting if someone had a large-ish 9.4 or 9.5 install
that they could test recovery timing on. My suspicion is that as long as
FPWs are on that you'd generally end up limited by how fast you could
read WAL unless you exceeded the FS cache. (I'm assuming a BBU and that
the FS and controller will do a nice job of ordering writes optimally so
that you'll get performance similar to reads when it's time to fsync.)
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com