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

On 10/27/2014 07:01 PM, Andres Freund wrote:
> On 2014-10-27 18:57:27 -0400, Andrew Dunstan wrote:
>> On 10/27/2014 05:58 PM, Tomas Vondra wrote:
>>> On 27.10.2014 17:24, Heikki Linnakangas wrote:
>>> I'm also thinking that for wal_level=archive and large databases, this
>>> won't really eliminate the checkpoint as it will likely generate enough
>>> WAL to hit checkpoint_segments and trigger a checkpoint anyway. No?
>>>
>>> That being said, our CREATE DATABASE docs currently say this
>>>
>>> Although it is possible to copy a database other than template1 by
>>> specifying its name as the template, this is not (yet) intended as
>>> a general-purpose "COPY DATABASE" facility. The principal
>>> limitation is that no other sessions can be connected to the
>>> template database while it is being copied. CREATE DATABASE will
>>> fail if any other connection exists when it starts; otherwise, new
>>> connections to the template database are locked out until CREATE
>>> DATABASE completes. See Section 21.3 for more information.
>>>
>>> I think that this limitation pretty much means no one should use CREATE
>>> DATABASE for cloning live databases in production environment (because
>>> of the locking).
>>>
>>> It also seems to me the "general-purpose COPY DATABASE" described in the
>>> docs is what we're describing in this thread.
>>>
>>
>> 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.
>

At the very least that needs to be benchmarked.

cheers

andrew