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

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2014-10-27 18:57:27 -0400, Andrew Dunstan wrote:
>> Notwithstanding what the docs say, I have seen CREATE DATABASE used plenty
>> of times, and quite effectively, to clone databases. I don't think making it
>> do twice the IO in the general case is going to go down well.

> I think they're actually more likely to be happy that we wouldn't need
> do a immediate checkpoint anymore. The performance penalty from that
> likely to be much more severe than the actual IO.

Note that currently, CREATE DATABASE requires fsync'ing each file written
into the new database. With the proposed new implementation, we'd write
out that data to the kernel *but not have to fsync it*. Instead, we'd
fsync just the WAL. At least on spinning rust, that could be a
considerable win, for exactly the same reasons that we don't fsync
anything but WAL for ordinary transaction commits (ie, way fewer seeks).
Maybe not by enough to counteract doubling the write volume, but I think
we'd need some benchmarks before concluding that it's completely horrid.

regards, tom lane