Re: Postgres is not able to handle more than 4k tables!?

Great idea.

In addition to this, it would be good to consider another optimization for
the default transaction isolation level: making autovacuum to clean dead
tuples in relations that are not currently used in any transaction and when
there are no IN_PROGRESS transactions running at RR or S level (which is a
very common case because RC is the default level and this is what is
actively used in heavily loaded OLTP systems which often suffer from
long-running transactions). I don't know the details of how easy it would
be to implement, but it always wondered that autovacuum has the global XID
"horizon".

With such an optimization, the "hot_standby_feedback=on" mode could be
implemented also more gracefully, reporting "min(xid)" for ongoing
transactions on standbys separately for RC and RR/S levels.

Without this, we cannot have good performance for HTAP use cases for
Postgres – the presence of just a small number of long-running
transactions, indeed, is known to kill the performance of OLTP workloads
quickly. And leading to much faster bloat growth than it could be.

However, maybe I'm wrong in these considerations, or it's impossible / too
difficult to implement.

On Thu, Jul 9, 2020 at 9:38 AM Alexander Korotkov <aekorotkov(at)gmail(dot)com>
wrote:

> On Thu, Jul 9, 2020 at 6:57 PM Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
> > 2. Remember in relation info XID of oldest active transaction at the
> > moment of last autovacuum.
> > At next autovacuum iteration we first of all compare this stored XID
> > with current oldest active transaction XID
> > and bypass vacuuming this relation if XID is not changed.
>
>
> This option looks good for me independently of the use case under
> consideration. Long-running transactions are an old and well-known
> problem. If we can skip some useless work here, let's do this.
>
> ------
> Regards,
> Alexander Korotkov
>
>
>