Just a quick footnote: If autovac truncations are frequently causing replica lag, and if this is a problem for you, IIUC one way you can stop autovac from doing the truncations even on older versions is setting old_snapshot_threshold to any value at all besides zero. (On 12+ you can directly control the truncation behavior.)
-Jeremy Sent from my TI-83 > On May 10, 2019, at 12:46, Erik Jones <mag...@gmail.com> wrote: > >> On Fri, May 10, 2019 at 12:41 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > >> Andres Freund <and...@anarazel.de> writes: >> > On 2019-05-09 13:03:50 -0700, Erik Jones wrote: >> >> The question then is: Why would these user queries be waiting on an >> >> AccessShare lock on pg_attribute? >> >> > Queries that access a table for the *first* time after DDL happened >> > (including truncating the relation), need an AccessShareLock on >> > pg_attribute (and pg_class, pg_index, ...) for a short time. >> >> Also, it seems likely that what's really triggering the issue is >> autovacuum on pg_attribute trying to truncate off empty pages >> in pg_attribute (after a bunch of dead rows were generated there >> by DDL activity). That requires exclusive lock on pg_attribute, >> which would propagate down to the standby. >> >> regards, tom lane > > Right, that part I understood after checking out pg_attribute's insert/delete > counts in pg_stat_sys_tables before and after some REFRESH MATERIALIZED VIEW > runs on an otherwise idle server. With them running 2k+ refreshes per day > autovac is regularly working on their catalog tables. > > Thanks! > -- > Erik Jones > mag...@gmail.com
