Hi,
My colleague encountered the problem that WAL replay took a long time
in the standby with large shared_buffers when he dropped the database
using many tablespaces. As far as I read the code, this happens because
DROP DATABASE generates as many XLOG_DBASE_DROP WAL records as
the number of tablespaces that the database to drop uses,
and then WAL replay of one XLOG_DBASE_DROP record causes full scan of
shared_buffers. That is, DROP DATABASE causes the scans of shared_buffers
as many times as the number of the tablespaces during recovery.
Since the first scan caused by the first XLOG_DBASE_DROP record invalidates
all the pages related to the database to drop, in shared_buffers,
the subsequent scans by the subsequent records seem basically useless.
So I'd like to change the code so that we can avoid such subsequent
unnecessary scans, to reduce the recovery time of DROP DATABASE.
Generally the recovery performance of DROP DATABASE is not critical
for many users. But unfortunately my colleague's project might need to
sometimes drop the database using multiple tablespaces, for some reasons.
So, if the fix is not so complicated, I think that it's worth applying that.
The straight approach to avoid such unnecessary scans is to change
DROP DATABASE so that it generates only one XLOG_DBASE_DROP record,
and register the information of all the tablespace into it. Then, WAL replay
of XLOG_DBASE_DROP record scans shared_buffers once and deletes
all tablespaces. POC patch is attached.
Thought?
Regards,
--
Fujii Masao