Re: Do we need to handle orphaned prepared transactions in the server? - Mailing list pgsql-hackers

From Craig Ringer
Subject Re: Do we need to handle orphaned prepared transactions in the server?
Date
Msg-id CAMsr+YFk=FuyLV2sqcYx9Y8xfB-y6rMO70KcbnjuKria2_rAWQ@mail.gmail.com
Whole thread Raw
In response to Re: Do we need to handle orphaned prepared transactions in the server?  (Craig Ringer <craig@2ndquadrant.com>)
Responses Re: Do we need to handle orphaned prepared transactions in the server?  (Hamid Akhtar <hamid.akhtar@gmail.com>)
List pgsql-hackers
On Thu, 16 Apr 2020 at 13:23, Craig Ringer <craig@2ndquadrant.com> wrote:

Just discarding the prepared xacts is not the answer though.

... however, I have wondered a few times about making vacuum smarter about cases where the xmin is held down by prepared xacts or by replication slots. If we could record the oldest *and newest* xid needed by such resource retention markers we could potentially teach vacuum to remove intermediate dead rows. For high-churn workloads like like workqueue applications that could be a really big win.

We wouldn't need to track a fine-grained snapshot with an in-progress list (or inverted in-progress list like historic snapshots) for these. We'd just remember the needed xid range in [xmin,xmax] form. And we could even do the same for live backends' PGXACT - it might not be worth the price there, but if you have workloads that have batch xacts + high churn rate xacts it'd be pretty appealing.

It wouldn't help with xid wraparound concerns, but it could help a lot with bloat caused by old snapshots for some very common workloads.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 2ndQuadrant - PostgreSQL Solutions for the Enterprise

pgsql-hackers by date:

Previous
From: Craig Ringer
Date:
Subject: Re: Do we need to handle orphaned prepared transactions in the server?
Next
From: Amit Kapila
Date:
Subject: Re: Vacuum o/p with (full 1, parallel 0) option throwing an error