Re: Do we need to handle orphaned prepared transactions in the server?

On Thu, 16 Apr 2020 at 13:23, Craig Ringer <craig(at)2ndquadrant(dot)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