Re: using file cloning in create database / initdb

On Sat, Feb 12, 2022 at 07:37:30PM -0800, Andres Freund wrote:
> On 2022-02-12 20:17:46 -0600, Justin Pryzby wrote:
> > On Sat, Feb 12, 2022 at 06:00:44PM -0800, Andres Freund wrote:
> > > I bet using COW file copies would speed up our own regression tests noticeably
> > > - on slower systems we spend a fair bit of time and space creating template0
> > > and postgres, with the bulk of the data never changing.
> > >
> > > Template databases are also fairly commonly used by application developers to
> > > avoid the cost of rerunning all the setup DDL & initial data loading for
> > > different tests. Making that measurably cheaper would be a significant win.
> >
> > +1
> >
> > I ran into this last week and was still thinking about proposing it.
> >
> > Would this help CI
>
> It could theoretically help linux - but currently I think the filesystem for
> CI is ext4, which doesn't support FICLONE. I assume it'd help macos, but I
> don't know the performance characteristics of copyfile(). I don't think any of
> the other OSs have working reflink / file clone support.
>
> You could prototype it for CI on macos by using the "template initdb" patch
> and passing -c to cp.

Yes, copyfile() in CREATE DATABASE seems to help cirrus/darwin a bit.
https://cirrus-ci.com/task/5277139049119744

On xfs:
postgres=# CREATE DATABASE new3 TEMPLATE postgres STRATEGY FILE_COPY ;
2022-07-31 00:21:28.350 CDT [2347] LOG: checkpoint starting: immediate force wait flush-all
...
CREATE DATABASE
Time: 1296.243 ms (00:01.296)

postgres=# CREATE DATABASE new4 TEMPLATE postgres STRATEGY FILE_CLONE;
2022-07-31 00:21:38.697 CDT [2347] LOG: checkpoint starting: immediate force wait flush-all
...
CREATE DATABASE
Time: 167.152 ms

--
Justin