Re: Avoiding adjacent checkpoint records

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.

Your suggestion of just checkpointing CLOG seems like it would
mitigate the worst of the damage, but I think I agree with Tom that
just reverting the whole patch would be a better solution, if we can't
figure out anything cleaner. It's better to have a few unnecessary
checkpoints than to risk losing somebody's data, especially since the
unnecessary checkpoints only happen with wal_level=hot_standby, but
the data loss risk exists for everyone.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company