Re: automatic vacuum on pg_statistic pg_toast area blocks all queries in hot standby

> Secondly, where do you run "select txid_current() into res1" and "select
txid_current() into res2" ?
It does not matter where. Important to count the number of transactions that
takes place during max_standby_streaming_delay. Example res2-res1 = 25000,
my value vacuum_defer_cleanup_age = 40000

> Even if you set MASTER vacuum_defer_cleanup_age to certain value,
> autovacuum still can kick in anytime and may conflict query on the target
> table. So setting vacuum_defer_cleanup_age to a value can really reduce
> the chance of such situation?
Due to this parameter, at the time when Autovacuum clean the dead rows they
will be dead on the slave, and thus no queries will not use them. Therefore,
the conflict will not be.

> Moreover, in my post, the vacuum is on pg_statistic toast area, why did it
> block queries on SLAVE?
Autovacuum serves all tables, including a pg_catalog. This problem occurs on
any table. For example pg_class or my_table. Autovacuum remove rows in
moment AccessShareLock, this is row and unlock AccessShare receive to slave
with WAL. WAL not recorded on slave due to a conflict recovery. At the same
time queries are waiting for a lock on the slave.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/automatic-vacuum-on-pg-statistic-pg-toast-area-blocks-all-queries-in-hot-standby-tp5807143p5820183.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.