Horton

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

Dilip Kumar <dilipbalaut@gmail.com>

View thread View on postgr.es
From: Dilip Kumar <dilipbalaut@gmail.com>
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2021-06-15T12:41:23Z
Lists: pgsql-hackers 
On Tue, Jun 15, 2021 at 5:34 PM Heikki Linnakangas <hlinnaka@iki.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

Commits

  1. When using the WAL-logged CREATE DATABASE strategy, bulk extend.

  2. Avoid using a fake relcache entry to own an SmgrRelation.

  3. Fix data-corruption hazard in WAL-logged CREATE DATABASE.

  4. initdb: When running CREATE DATABASE, use STRATEGY = WAL_COPY.

  5. Simplify a needlessly-complicated regular expression.

  6. In 020_createdb.pl, change order of command-line arguments.

  7. Add new block-by-block strategy for CREATE DATABASE.

  8. Fix replay of create database records on standby

  9. Refactor code for reading and writing relation map files.

  10. Replace RelationOpenSmgr() with RelationGetSmgr().

  11. Refactor the fsync queue for wider use.