Re: proposal: CREATE DATABASE vs. (partial) CHECKPOINT

On Mon, Oct 27, 2014 at 8:01 PM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
> (3) write-heavy workloads / large template database
>
> Current approach wins, for two reasons: (a) for large databases the
> WAL-logging overhead may generate much more I/O than a checkpoint,
> and (b) it may generate so many WAL segments it eventually triggers
> a checkpoint anyway (even repeatedly).

I would tend not to worry too much about this case. I'm skeptical
that there are a lot of people using large template databases. But if
there are, or if some particular one of those people hits this
problem, then they can raise checkpoint_segments to avoid it. The
reverse problem, which you are encountering, cannot be fixed by
adjusting settings.

(This reminds me, yet again, that it would be really nice to something
smarter than checkpoint_segments. If there is little WAL activity
between one checkpoint and the next, we should reduce the number of
segments we're keeping around to free up disk space and ensure that
we're recycling a file new enough that it's likely to still be in
cache. Recycling files long-since evicted from cache is poor. But
then we should also let the number of WAL files ratchet back up if the
system again becomes busy. Isn't this more or less what Heikki's
soft-WAL-limit patch did? Why did we reject that, again?)

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