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

On Tue, Feb 8, 2022 at 10:39 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> I have been tied up with other things for a bit now and have not had
> time to look at this thread; sorry about that. I have a little more
> time available now so I thought I would take a look at this again and
> see where things stand.

Thanks for looking into this.

>
> Sadly, it doesn't appear to me that anyone has done any performance
> testing of this patch, along the lines suggested above or otherwise,
> and I think it's a crucial question for the patch.

Yeah, actually some performance testing started as shared by Ahustosh
[1] and soon after that we got side tracked by another issue[2] which
we thought had to be fixed before we proceed with this feature.

My reading of this
> thread is that nobody really likes the idea of maintaining two methods
> for performing CREATE DATABASE, but nobody wants to hose people who
> are using it to clone large databases, either. To some extent those
> things are inexorably in conflict. If we postulate that the 10TB
> template database is on a local RAID array with 40 spindles, while
> pg_wal is on an iSCSI volume that we access via a 128kB ISDN link,
> then the new system is going to be infinitely worse. But real
> situations aren't likely to be that bad, and it would be useful in my
> opinion to have an idea how bad they actually are.

Yeah that makes sense, I will work on performance testing in this line
and also on previous ideas you suggested.

> I'm somewhat inclined to propose that we keep the existing method
> around along with the new method. Even though nobody really likes
> that, we don't necessarily have to maintain both methods forever. If,
> say, we use the new method by default in all cases, but add an option
> to get the old method back if you need it, we could leave it that way
> for a few years and then propose removing the old method (and the
> switch to activate it) and see if anyone complains. That way, if the
> new method turns out to suck in certain cases, users have a way out.
> However, I still think doing some performance testing would be a
> really good idea. It's not a great plan to make decisions about this
> kind of thing in an information vacuum.

Yeah that makes sense to me.

Now, one bigger question is can we proceed with this patch without
fixing [2], IMHO, if we are deciding to keep the old method also
intact then one option could be that for now only change CREATE
DATABASE to support both old and new way of creating database and for
time being leave the ALTER DATABASE SET TABLESPACE alone and let it
work only with the old method? And another option is that we first
fix the issue related to the tombstone file and then come back to
this?

IMHO, the first option could be better in a way that we have already
made better progress in this patch and this is in better shape than
the other patch we are trying to make for removing the tombstone
files.

[1]https://www.postgresql.org/message-id/CAE9k0Pkg20tHq8oiJ%2BxXa9%3Daf3QZCSYTw99aBaPthA1UMKhnTg%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CA%2BTgmobM5FN5x0u3tSpoNvk_TZPFCdbcHxsXCoY1ytn1dXROvg%40mail.gmail.com

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