Re: [CAUTION: SUSPECT SENDER] RE: [CAUTION: SUSPECT SENDER] RE: BUG #19029: Replication Slot size keeps increasing while logical subscription works fine

Hi Thadeus Anand, This spill over files are held by slots thinking that it
need for sequential apply of transaction. Manually deletion may have impact
on the logical streaming. I too had that thought but ours high critical
production system and so I didn't have courage to proceed further.

Rather, alter publication/subscription would work/help.

If any have technical insights on this manual intervention, kindly please
share which will help for our case too.

On Wed, 27 Aug, 2025, 11:01 Thadeus Anand, <thadeus(at)rmkv(dot)com> wrote:

> Hi,
>
> Thank you everyone for your wonderful guidance. Really appreciate it.
>
> I have a couple of doubts.
>
> Can these spill files in the pg_replslot folder be deleted safely, if all
> the subscribers have caught up? (I noticed last night when I restarted my
> publisher, the spill files were removed automatically)
>
> Can I use ALTER SUBSCRIPTION to set streaming on, with my existing
> subscriptions?
>
> Thadeus.
>
>
>
>
> On Wed, Aug 27, 2025 at 10:06 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wrote:
>
>> On Wed, Aug 27, 2025 at 9:09 AM Hayato Kuroda (Fujitsu)
>> <kuroda(dot)hayato(at)fujitsu(dot)com> wrote:
>> >
>> > Dear Thadeus,
>> >
>> > > I do not know or understand what a "spill" is.
>> >
>> > As I wrote in a previous mail, logical decoding (and logical
>> replication)
>> > sometimes writes changes into the disk to avoid using too-much memory,
>> and the
>> > ".spill" file is the fragment. This can happen when the total amount of
>> decoded
>> > changes exceeds logical_decoding_work_mem.
>> >
>> > > I will look it up. But the tables that are part of the
>> > > publication are not updated as part of any huge transaction. They may
>> be part of some other
>> > > long running procedures
>> >
>> > Actually, logical decoding decodes all changes even if tables are not
>> published,
>> > and suppress outputting changes at commit phase.
>> >
>>
>> One can avoid the generation of so many spill files for large
>> transactions by using streaming=on or streaming=parallel while
>> creating subscriptions.
>>
>> --
>> With Regards,
>> Amit Kapila.
>>
>