Thread: Advance xmin aggressively on Read Commit isolation level
Since the impact of the long transaction is huge in postgresql, for example
a long transaction by incident, tables may become very huge and it can't
become small again even the transaction is completed unless a vacuum full
is used.
I have 2 ideas about this. One is in the Read Committed level, we can advance xmin
aggressively. suppose it started at t1, and complete a query at t2. the xmin should
be t1 currently. Can we advance the xmin to t2 since it is read committed level,
The data older than t2 will never be used? Another one is can we force to clean
up the old tuples which are older than xxx? If users want to access that,
we can just raise errors. Oracle uses this strategy and the error code is
ORA-01555.
--
Best Regards
Andy Fan
On Fri, Nov 6, 2020 at 9:48 PM Andy Fan <zhihui.fan1213@gmail.com> wrote: > I have 2 ideas about this. One is in the Read Committed level, we can advance xmin > aggressively. suppose it started at t1, and complete a query at t2. the xmin should > be t1 currently. Can we advance the xmin to t2 since it is read committed level, > The data older than t2 will never be used? Another one is can we force to clean > up the old tuples which are older than xxx? If users want to access that, > we can just raise errors. Oracle uses this strategy and the error code is > ORA-01555. Hi Andy, For the second idea, we have old_snapshot_threshold which does exactly that since 9.6. There have been some questions about whether it works correctly, though: see https://commitfest.postgresql.org/30/2682/ if you would like to help look into that :-)
On Fri, Nov 6, 2020 at 4:54 PM Thomas Munro <thomas.munro@gmail.com> wrote:
On Fri, Nov 6, 2020 at 9:48 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:
> I have 2 ideas about this. One is in the Read Committed level, we can advance xmin
> aggressively. suppose it started at t1, and complete a query at t2. the xmin should
> be t1 currently. Can we advance the xmin to t2 since it is read committed level,
> The data older than t2 will never be used? Another one is can we force to clean
> up the old tuples which are older than xxx? If users want to access that,
> we can just raise errors. Oracle uses this strategy and the error code is
> ORA-01555.
Hi Andy,
For the second idea, we have old_snapshot_threshold which does exactly
that since 9.6. There have been some questions about whether it works
correctly, though: see https://commitfest.postgresql.org/30/2682/ if
you would like to help look into that :-)
Hi Tomas:
This is exactly what I want and I have big interest with that. Thanks for
the information!
Best Regards
Andy Fan