Re: Avoiding adjacent checkpoint records

On 7 June 2012 18:03, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Jun 7, 2012 at 12:52 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> Clearly, delaying checkpoint indefinitely would be a high risk choice.
>> But they won't be delayed indefinitely, since changes cause WAL
>> records to be written and that would soon cause another checkpoint.
>
> But that's exactly the problem - it might not be soon at all.  We have
> customers who process about one write transaction per day.  The
> current state of play in 9.2 is that we'll checkpoint when we get to
> the next WAL segment.  But at one write transaction per day, that
> could take weeks or months.  Someone invented a setting called
> 'checkpoint_timeout' for a very good reason - people don't want huge
> amounts of time to pass between checkpoints.  That setting is
> currently capped at one hour; the proposition that someone who sets it
> to 5 minutes on a low-write-volume system is OK with the effective
> value being 5 months does not seem likely to me.

ISTM that this low-volume system accepts approximately the same risk
of data loss as a high volume system that writes the same volume of
data, but in 5 minutes rather than 5 months. You might argue that the
scope of things that can go wrong in 5 months is far greater than the
scope of 5 minutes. I might counter that the mechanical wear attendant
to constant writes was a more important factor, or flash memory having
gone through one too many P/E cycles, becoming unreliable.

The bottom line is that once you've flushed WAL, you're by definition
home-free - if you're relying on a checkpoint to save you from data
loss, you're in big trouble anyway. Checkpoints are theoretically just
for putting some cap on recovery time. That's pretty much how they're
described in the literature.

Your customer's use-case seems very narrow, and your complaint seems
unusual to me, but couldn't you just get the customer to force
checkpoints in a cronjob or something? CheckPointStmt will force,
provided !RecoveryInProgress() .

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services