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

Hi Nantha,

Thank you for your valuable input. So it's really not a bug, but an
existing behavior you mean.

I do not know or understand what a "spill" is. 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 though.
If that can create such spills, we can look into them.

Can someone please advise on how to avoid such spills please?

Thanks and regards,
Thadeus Anand.

On Wed, 27 Aug, 2025, 8:09 am Nantha kumar.T., <nanthad(at)gmail(dot)com> wrote:

> Hi Thadeus Anand, Recently in our postgres 16.4 version system we have
> encountered similar kind of pg_repslot folder size growing abnormally. The
> files in this folders are just spill over Wal files due to long running or
> complex transactions. There are commands to check the slot's holding
> transactions count which related to this spill. If required can share the
> same. So suggesting to monitor the transactions/sub transactions that are
> executing. Well for our case, one of the past transaction invoked the
> issue.
>
> Not sure whether it's bug which have to be addressed in 17.5.
>
> On Tue, 26 Aug, 2025, 17:51 Thadeus Anand, <thadeus(at)rmkv(dot)com> wrote:
>
>> Hi,
>>
>> Here is the result of "select * from pg_replication_slots"
>>
>> slot_name plugin slot_type datoid database temporary active active_pid
>> xmin catalog_xmin restart_lsn confirmed_flush_lsn wal_status
>> safe_wal_size two_phase inactive_since conflicting invalidation_reason
>> failover synced
>> rmkv_crm_sub_twn pgoutput logical 5 postgres FALSE TRUE 240,409 [NULL]
>> 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL]
>> FALSE [NULL] FALSE FALSE
>> rmkv_main_sub_twn pgoutput logical 5 postgres FALSE TRUE 240,410 [NULL]
>> 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL]
>> FALSE [NULL] FALSE FALSE
>> standby_slot [NULL] physical [NULL] [NULL] FALSE TRUE 240,458 [NULL]
>> [NULL] 2EA/834D54B8 [NULL] reserved 21,486,545,736 FALSE [NULL] [NULL]
>> [NULL] FALSE FALSE
>> rmkv_crm_sub_cbe pgoutput logical 5 postgres FALSE TRUE 240,404 [NULL]
>> 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL]
>> FALSE [NULL] FALSE FALSE
>> rmkv_main_sub_cbe pgoutput logical 5 postgres FALSE TRUE 240,406 [NULL]
>> 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL]
>> FALSE [NULL] FALSE FALSE
>> rmkv_crm_sub_tvl pgoutput logical 5 postgres FALSE TRUE 240,408 [NULL]
>> 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL]
>> FALSE [NULL] FALSE FALSE
>> rmkv_main_sub_tvl pgoutput logical 5 postgres FALSE TRUE 240,411 [NULL]
>> 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL]
>> FALSE [NULL] FALSE FALSE
>> rmkv_crm_sub_blr pgoutput logical 5 postgres FALSE TRUE 240,405 [NULL]
>> 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL]
>> FALSE [NULL] FALSE FALSE
>> rmkv_main_sub_blr pgoutput logical 5 postgres FALSE TRUE 240,407 [NULL]
>> 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL]
>> FALSE [NULL] FALSE FALSE
>> rmkv_crm_sub_vch pgoutput logical 5 postgres FALSE TRUE 240,403 [NULL]
>> 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL]
>> FALSE [NULL] FALSE FALSE
>> rmkv_main_sub_vch pgoutput logical 5 postgres FALSE TRUE 240,401 [NULL]
>> 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL]
>> FALSE [NULL] FALSE FALSE
>> rmkv_crm_sub_vdp pgoutput logical 5 postgres FALSE TRUE 240,400 [NULL]
>> 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL]
>> FALSE [NULL] FALSE FALSE
>> rmkv_main_sub_vdp pgoutput logical 5 postgres FALSE TRUE 419,107 [NULL]
>> 10861325 2E9/296801D0 2EA/1E788780 reserved 15,681,629,000 FALSE [NULL]
>> FALSE [NULL] FALSE FALSE
>>
>> Thadeus Anand.
>>
>>
>>
>> On Tue, Aug 26, 2025 at 5:44 PM Thadeus Anand <thadeus(at)rmkv(dot)com> wrote:
>>
>>> Hi,
>>>
>>> Since this afternoon, the replication slot folders have started to grow
>>> in size again. I have attached a couple of screenshots showing the size and
>>> some contents of a folder. Hope you find them useful.
>>>
>>> I checked the database log at the publisher, and could find nothing
>>> suspicious around that time when the spill files started appearing.
>>>
>>> Thadeus Anand.
>>>
>>>
>>> On Tue, Aug 26, 2025 at 4:05 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
>>> wrote:
>>>
>>>> On Tue, Aug 26, 2025 at 1:45 PM Thadeus Anand <thadeus(at)rmkv(dot)com> wrote:
>>>> >
>>>> > We created the publications and subscriptions again last night after
>>>> restarting the publisher and all the subscribers.
>>>> >
>>>> > So far, everything seems to be working fine, and the replication slot
>>>> folders remain at 4 KB each.
>>>> >
>>>> > I did not change anything, and didn't do anything differently this
>>>> time. In any case, I will monitor this closely and let you know if any
>>>> malfunction.occurs.
>>>> >
>>>>
>>>> Thanks for the update.
>>>>
>>>> --
>>>> With Regards,
>>>> Amit Kapila.
>>>>
>>>