Horton

RE: Introduce XID age and inactive timeout based replication slot invalidation

Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>

View thread
From: "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>
To: Amit Kapila <amit.kapila16@gmail.com>, Nathan Bossart <nathandbossart@gmail.com>
Cc: Álvaro Herrera <alvherre@alvh.no-ip.org>, Nisha Moond <nisha.moond412@gmail.com>, Peter Smith <smithpb2250@gmail.com>, vignesh C <vignesh21@gmail.com>, Shlok Kyal <shlok.kyal.oss@gmail.com>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, shveta malik <shveta.malik@gmail.com>, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>, Ajin Cherian <itsajin@gmail.com>, Bertrand Drouvot <bertranddrouvot.pg@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-02-12T07:46:22Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add a test for commit ac0e33136a using the injection point.

  2. Invalidate inactive replication slots.

  3. Fix incorrect slot type in BuildTupleHashTableExt

  4. Allow synced slots to have their inactive_since.

  5. Change last_inactive_time to inactive_since in pg_replication_slots.

  6. Track last_inactive_time in pg_replication_slots.

  7. Track invalidation_reason in pg_replication_slots.

  8. Add option force_initdb to PostgreSQL::Test::Cluster:init()

  9. Add a failover option to subscriptions.

  10. Allow setting failover property in the replication command.

  11. Allow to enable failover property for replication slots via SQL API.

  12. Track conflict_reason in pg_replication_slots.

  13. Log messages for replication slot acquisition and release.

  14. Remove vacuum_defer_cleanup_age

  15. Fix corruption due to vacuum_defer_cleanup_age underflowing 64bit xids

  16. meson: Add initial version of meson based build system

On Wednesday, February 12, 2025 11:56 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> 
> On Tue, Feb 11, 2025 at 9:39 PM Nathan Bossart
> <nathandbossart@gmail.com> wrote:
> >
> > On Tue, Feb 11, 2025 at 03:22:49PM +0100, Álvaro Herrera wrote:
> > > I find this proposed patch a bit strange and I feel it needs more
> > > explanation.
> > >
> > > When this thread started, Bharath justified his patches saying that
> > > a slot that's inactive for a very long time could be problematic
> > > because of XID wraparound.  Fine, that sounds a reasonable feature.
> > > If you wanted to invalidate slots whose xmins were too old, I would
> > > support that.  He submitted that as his 0004 patch then.
> > >
> > > However, he also chose to submit 0003 with invalidation based on a
> > > timeout.  This is far less convincing a feature to me.  The
> > > justification for the time out seems to be that ... it's difficult
> > > to have a one-size-fits-all value because size of disks vary. (???)
> > > Or something like that.  Really?  I mean -- yes, this will prevent
> > > problems in toy databases when run in developer's laptops.  It will
> > > not prevent any problems in production databases.  Do we really want
> > > a setting that is only useful for toy situations rather than production?
> > >
> > >
> ...
> > >
> > > I'm baffled.
> >
> > I agree, and I am also baffled because I think this discussion has
> > happened at least once already on this thread.
> >
> 
> Yes, we previously discussed this topic and Robert seems to prefer a
> time-based parameter for invalidating the slot (1)(2) as it is easier to reason in
> terms of time. The other points discussed previously were that there are tools
> that create a lot of slots and sometimes forget to clean up slots. Bharath has
> seen this in production and we now have the tool pg_createsubscriber that
> creates a slot-per-database, so if for some reason, such slots are not cleaned
> on the tool's exit, such a parameter could save the cluster. See (3)(4).
> 
> Also, we previously didn't have a good experience with XID-based threshold
> parameters like vacuum_defer_cleanup_age as mentioned by Robert (1).
> AFAICU from the previous discussion we need a time-based parameter and we
> didn't rule out xid_age based parameter as another parameter.

Yeah, I think the primary purpose of this time-based option is to invalidate dormant
replication slots that have been inactive for a long period, in which case the
slots are no longer useful.
 
Such slots can remain if a subscriber is down due to a system error or
inaccessible because of network issues. If this situation persists, it might be
more practical to recreate the subscriber rather than attempt to recover the
node and wait for it to catch up, which could be time-consuming.

Parameters like max_slot_wal_keep_size and max_slot_xid_id_age do not
differentiate between active and inactive replication slots. Some customers I
met are hesitant about using these settings, as they can sometimes invalidate
a slot unnecessarily and break the replication.


> (1) -
> https://www.postgresql.org/message-id/CA%2BTgmoZTbaaEjSZUG1FL0mzx
> AdN3qmXksO3O9_PZhEuXTkVnRQ%40mail.gmail.com
> (2) -
> https://www.postgresql.org/message-id/CA%2BTgmoaRECcnyqxAxUhP5dk2
> S4HX%3DpGh-p-PkA3uc%2BjG_9hiMw%40mail.gmail.com
> (3) -
> https://www.postgresql.org/message-id/CALj2ACVFV%3DyUa3DXXfJLOtJxU
> M8qzC_mEECMJ2iekDGPeQLkTw%40mail.gmail.com
> (4) -
> https://www.postgresql.org/message-id/CAA4eK1L3awyzWMuymLJUm8SoF
> EQe%3DDa9KUwCcAfC31RNJ1xdJA%40mail.gmail.com

Best Regards,
Hou zj