PostgreSQL Master-Slave Replication Performance Optimization: Solving Drop Table Bottleneck

This article discusses a critical performance issue in Tencent Cloud Database PostgreSQL when handling massive DROP operations in master-slave replication scenarios.

Background: For business scenarios requiring high data security, data modifications are performed in editable branch databases and only merged to the main database after review. This creates numerous CREATE/DROP table operations, leading to significant replication lag.

Root Cause Analysis: When PostgreSQL replica processes a DROP TABLE statement, it must: 1) Recover system tables (pg_class, pg_attribute, pg_type), 2) Close table files, 3) Iterate through all shared_buffers to mark cached pages as invalid via DropRelFileNodesAllBuffers, 4) Send async invalidation messages, 5) Delete physical files. The bottleneck is in step 3 - with default 8KB page size and 16GB shared_buffers, each DROP requires iterating 2 million+ pages. Since the replica runs as a single-process recovery, this causes severe replication lag.

Solution: Extract the buffer invalidation step into a separate subprocess to accelerate log consumption. To handle edge cases (file operations completing before buffer invalidation), implement a shared hash table to track tables being processed. When creating new files, check if同名 files are pending invalidation and wait if needed.

Results: After optimization, replication lag reduced from 400+ GB to just over 10 MB - a performance improvement of over 30,000 times. This solution addresses a long-standing issue in the PostgreSQL community.