Re: [Proposal] Fully WAL logged CREATE DATABASE - No Checkpoints

On Tue, Jun 15, 2021 at 5:34 PM Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
>
> On 15/06/2021 14:20, Dilip Kumar wrote:
> > Design Idea:
. Then
> > we can get the relfilenode of every file we need to copy, and prepare
> > a list of all such relfilenode.
>
> I guess that would work, but you could also walk the database directory
> like copydir() does. How you find the relations to copy is orthogonal to
> whether you WAL-log them or use checkpoints. And whether you use the
> buffer cache is also orthogonal to the rest of the proposal; you could
> issue FlushDatabaseBuffers() instead of a checkpoint.

Yeah, that would also work, but I thought since we are already
avoiding the checkpoint so let's avoid FlushDatabaseBuffers() also and
directly use the lower level buffer manager API which doesn't need
recache. And I am using pg_class to identify the useful relfilenode
so that we can avoid processing some unwanted relfilenode but yeah I
agree that this is orthogonal to whether we use checkpoint or not.

> Yeah, WAL-logging the contents of the source database would certainly be
> less weird than the current system. As Julien also pointed out, the
> question is, are there people using on "CREATE DATABASE foo TEMPLATE
> bar" to copy a large source database, on the premise that it's fast
> because it skips WAL-logging?
>
> In principle, we could have both mechanisms, and use the new WAL-logged
> system if the database is small, and the old system with checkpoints if
> it's large. But I don't like idea of having to maintain both.

Yeah, I agree in some cases, where we don't have many dirty buffers,
checkpointing can be faster. I think code wise maintaining two
approaches will not be a very difficult job because the old approach
just calls copydir(), but I am thinking about how can we decide which
approach is better in which scenario. I don't think we can take calls
just based on the database size? It would also depend upon many other
factors e.g. how busy your system is, how many total dirty buffers are
there in the cluster right? because checkpoint will affect the
performance of the operation going on in other databases in the
cluster.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com