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

On 2014-10-30 14:51:54 -0400, Robert Haas wrote:
> On Wed, Oct 29, 2014 at 2:36 PM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
> >> 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.
> >
> > That however solves "only" the checkpoint, not the double amount of I/O
> > due to writing both the files and WAL, no? But maybe that's OK.
>
> I mean, it's not unimaginable that it's going to hurt somebody, but
> the current situation is pretty bad too. You don't have to be the
> world's foremost PostgreSQL performance expert to know that extra
> checkpoints are really bad for performance. Write volume is of course
> also a problem, but I bet there are a lot more people using small
> template databases (where the write volume isn't really an issue,
> because as Heikki points out the checkpoint wastes half a segment
> anyway, but the checkpoint may very well be a issue) than large ones
> (where either could be an issue).

Agreed. The current behaviour is a pretty ugly that just failed to fail
recently. I actually think we should *always* use the new code and not
add a separate wal_level=minimal branch. Maintaining this twice just
isn't worth the effort. minimal is used *far* less these days.

Greetings,

Andres Freund