RE: Speedup of relation deletes during recovery

From: Fujii Masao [mailto:masao(dot)fujii(at)gmail(dot)com]
> When multiple relations are deleted at the same transaction, the files of
> those relations are deleted by one call to smgrdounlinkall(), which leads
> to scan whole shared_buffers only one time. OTOH, during recovery,
> smgrdounlink() (not smgrdounlinkall()) is called for each file to delete,
> which leads to scan shared_buffers multiple times.
> Obviously this can cause to increase the WAL replay time very much especially
> when shared_buffers is huge.
>
> To alleviate this situation, I'd like to propose to change the recovery
> so that it also calls smgrdounlinkall() only one time to delete multiple
> relation files. Patch attached. Thought?

Nice catch. As Horiguchi-san and Michael already commented, the patch looks good.

As a related improvement, the following proposal is effective for shortening WAL replay time of DROP TABLE (and possibly TRUNCATE as well.) How should we proceed with this?

https://www.postgresql.org/message-id/A1CF58A8CBA14341B3F3AC6A468F18454545E4F3@g01jpexmbyt23

Furthermore, TRUNCATE has a similar and worse issue. While DROP TABLE scans the shared buffers once for each table, TRUNCATE does that for each fork, resulting in three scans per table. How about changing the following functions

smgrtruncate(SMgrRelation reln, ForkNumber forknum, BlockNumber nblocks)
DropRelFileNodeBuffers(RelFileNodeBackend rnode, ForkNumber forkNum,
BlockNumber firstDelBlock)

to

smgrtruncate(SMgrRelation reln, ForkNumber *forknum, BlockNumber *nblocks, int nforks)
DropRelFileNodeBuffers(RelFileNodeBackend rnode, ForkNumber *forkNum,
BlockNumber *firstDelBlock, int nforks)

to perform the scan only once per table? If there doesn't seem to be a problem, I think I'll submit the patch next month. I'd welcome if anyone could do that.

Regards
Takayuki Tsunakawa