Date: Wed, 5 Jun 2024 12:07:58 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Ranier Vilela <ranier.vf@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: use CREATE DATABASE STRATEGY = FILE_COPY in pg_upgrade
Message-ID: <ZmCbbi8v8J1H1HfK@nathan>
References: <Zl9ta3FtgdjizkJ5@nathan>
 <CAEudQAptbRCeh08gaQKFdazOXPBqZh7UrjRcZ8fixyqkwrkUrA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAEudQAptbRCeh08gaQKFdazOXPBqZh7UrjRcZ8fixyqkwrkUrA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/ZmCbbi8v8J1H1HfK%40nathan>
Precedence: bulk

On Wed, Jun 05, 2024 at 01:47:09PM -0300, Ranier Vilela wrote:
> Why not use it too, if not binary_upgrade?
> 
> else
> {
> appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0
> STRATEGY = FILE_COPY",
>  qdatname);
> }
> 
> It seems to me that it also improves in any use.

Well, if that is true, and I'm not sure it is, then we should probably
consider changing the default STRATEGY in the server instead.  I haven't
looked too deeply, but my assumption is that when fsync is disabled (as it
is when restoring schemas during pg_upgrade), both checkpointing and
copying the template database are sufficiently fast enough to beat writing
out all the data to WAL.  Furthermore, in my test, all the databases were
basically empty, so I suspect that some CREATE DATABASE commands could
piggy-back on checkpoints initiated by others.  This might not be the case
when there are many objects in each database, and that is a scenario I have
yet to test.

-- 
nathan