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