Re: [Proposal] Fully WAL logged CREATE DATABASE - No Checkpoints - Mailing list pgsql-hackers

On Mon, Dec 6, 2021 at 12:45 PM Robert Haas <robertmhaas@gmail.com> wrote:
> So for example, imagine tests with 1GB of shard_buffers, 8GB, and
> 64GB. And template databases with sizes of whatever the default is,
> 1GB, 10GB, 100GB. Repeatedly make 75% of the pages dirty and then
> create a new database from one of the templates. And then just measure
> the performance. Maybe for large databases this approach is just
> really the pits -- and if your max_wal_size is too small, it
> definitely will be. But, I don't know, maybe with reasonable settings
> it's not that bad. Writing everything to disk twice - once to WAL and
> once to the target directory - has to be more expensive than doing it
> once. But on the other hand, it's all sequential I/O and the data
> pages don't need to be fsync'd, so perhaps the overhead is relatively
> mild. I don't know.

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.

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. 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.

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.

-- 
Robert Haas
EDB: http://www.enterprisedb.com