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

On Thu, Jul 9, 2020 at 10:00 PM Nikolay Samokhvalov
<samokhvalov(at)gmail(dot)com> wrote:
> 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.

Yes, the current way of calculation of dead tuples is lossy, because
we only rely on the oldest xid. However, if we would keep the oldest
snapshot instead of oldest xmin, long-running transactions wouldn't be
such a disaster. I don't think this is feasible with the current
snapshot model, because keeping full snapshots instead of just xmins
would bloat shared-memory structs and complicate computations. But
CSN can certainly support this optimization.

------
Regards,
Alexander Korotkov