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

On 10/27/2014 01:06 PM, Atri Sharma wrote:
>>
>>
>>>
>> To solve #1, we could redesign CREATE DATABASE so that replaying the
>> DBASE_CREATE record doesn't zap the old directory, and also doesn't copy
>> any files. We could instead just assume that if the transaction commits,
>> all the files have been copied and fsync'd already, like we assume that if
>> a CREATE INDEX commits in wal_level=minimal, the underlying file was
>> fsync'd before the commit.
>>
>
> Do you mean that during a recovery, we just let the database directory be
> and assume that it is in good shape since the transaction committed
> originally?

Right.

>> I wonder if we should bite the bullet and start WAL-logging all the files
>> that are copied from the template database to the new database. When the
>> template database is small (template0 is 6.4MB currently), that wouldn't
>> generate too much WAL. We could perhaps do that only if the template
>> database is small, and do the checkpoints otherwise, although I wouldn't
>> like to have subtly different behavior depending on database size like that.
>
> For the sort of workload Tomas described above (creating a lot of databases
> on the fly), we may end up with a lot of WAL eventually if we do this.

I think writing 6 MB for each CREATE DATABASE would be OK. For
comparison, a pg_switch_xlog() call will waste on average half a
segment, i.e. 8MB. It's a lot cheaper than a checkpoint on a busy
system, for sure. But of course, if the template database is larger,
then you will generate more WAL.

- Heikki