On Mon, Dec 6, 2021 at 12:45 PM Robert Haas <robertmh...@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