Hi hackers, I recently hit a disk bloat issue caused by logical decoding: (1) A cluster has two databases, db1 and db2. There is a single logical replication slot on db1 while db2 holds plenty of tables. (2) A long-running transaction sits open in db2, pinning the xmin of the historic snapshots during decoding. (3) db2's tables get ANALYZEd -- manual run over all tables. Each analyzed table commits separately and writes pg_statistic, pg_statistic_ext and pg_class row, so every such commit is a catalog-changing commit carrying invalidation messages. (4) Every one of these commits makes the decoding session of db1's slot rebuild its historic snapshot and distribute it to every in-progress transaction in its reorder buffer -- including db2's long transaction, whose changes the slot will never decode however. Since xmin is pinned, the distributed snapshot's xip array grows, the snapshot handed out by the k-th ANALYZE commit is O(k) in size, and with the total spill files grow O(k^2), even though the slot's own database is completely idle. Note that a decoding session is connected to a single database and can only reads that database's catalogs plus the shared catalogs, so changes on other databases' catalog doesn't need a snapshot rebuild and distribution. The attached patch exploits this: in DecodeCommit(), if the committing transaction's dbId differs from the slot's database and none of the commit record's invalidation messages touches a shared catalog, it skips both the snapshot rebuild and the distribution. Correctness relies on that, every write to a shared catalog produces invalidation messages, SHAREDINVALSNAPSHOT_ID or SHAREDINVALRELCACHE_ID. Repro/measurement (script attached): one test_decoding slot on db1; 3000 tables created in db2; a long transaction open in db2; then a single ANALYZE over all of db2's tables, which commits each table separately (logical_decoding_work_mem = 64kB); finally decode the WAL with the slot. spill_bytes from pg_stat_replication_slots for db1's slot: master ~19 MB patched 0 So the slot of an idle database is completely unaffected by another database's ANALYZE traffic or other operations that can generate invalidation messages, while decoding of its own database's catalog changes is unchanged. Any feedback is appreciated. Regards, Boyu Yang
0001-Skip-snapshot-distribution-for-catalog-commits-in-ot.patch
Description: Binary data
Description: Binary data
