Logical Replication hung: logical_decoding_work_mem over utilised

From: Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com>
To: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Logical Replication hung: logical_decoding_work_mem over utilised
Date: 2023-08-30 14:14:05
Message-ID: CAFpL5VwaQ0fNaBV1z76pDX726bkruccxaKsWY2Wccs5tQnsCnA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi Team,

I faced a scenario where after a heavy update query (touching ~100k
records) the logical replication was hung.

After enabling debug logging, we found it was spilling to disk and
increased logical_decoding_work_mem from 64MB to 4GB. Replication started
working after that

I am trying to understand why the replication was just hung ? Also, How do
we decide for an optimal configuration of logical_decoding_work_mem.?

Even if it reaches 64MB, it should be able to spill to disk and continue,
may be not at the same speed but slowly

Note: There were no subtransactions involved, it was a simple update query
touching a lot of records.

Thanks,
Nikhil

Browse pgsql-admin by date

  From Date Subject
Next Message richard coleman 2023-08-30 20:38:53 Why isn't there a IF NOT EXISTS clause on constraint creation?
Previous Message Rajesh Kumar 2023-08-28 16:34:17 Re: Autovacuum not running properly