Tom & Jerry, Thanks a lot for information!
On Monday (weekends don't have the same load patterns compared to business days) I will take a look at ` pg_prepared_xacts` that seems to expose Jerry's suggestion on xacts. Replication slots don't apply to 9.2.X from what I could investigate so I will discard that suggestion. Feedback setting (hot_standby_feedback) is turned off in all our replicas, this shouldn't be an issue from what I understood. Delay setting (vacuum_defer_cleanup_age ) in our master is configured to 0, , this shouldn't be an issue from what I understood. Thanks a lot! Best, Martín On Fri, Sep 14th, 2018 at 11:29 PM, Jerry Sievers <gsiever...@comcast.net> wrote: > > > > Tom Lane < t...@sss.pgh.pa.us > writes: > > > =?UTF-8?q?Mart=C3=ADn_Fern=C3=A1ndez?= < fmarti...@gmail.com > writes: > > > >> We are experiencing some `vacuum` issues with a given table > >> (potentially more). When a manual vacuum runs on the given table it > >> seems that the `vacuum` process is not doing the expected cleanup. > > > >> DETAIL: 113257 dead row versions cannot be removed yet. > > > > Locks don't really have anything to do with that: what does matter is > > how old is the oldest open transaction, because that determines the > > "event horizon" that dead row versions have to fall below before they > > can be removed. That oldest transaction might not be holding any locks > > at the moment, but it doesn't matter, because in principle it could ask > > to read this table later --- and it should see the table's contents as > > of its snapshot. > > > > Serializable transactions are worse than repeatable-read transactions > > for this purpose, because the former will keep a snapshot as of their > > start time. > > > > As Jerry mentioned, replication slots can also act like open > transactions > > for this purpose, though I don't recall how much of that behavior is > > present in 9.2.x. > > Oops, didn't notice OP was on 9.2! Presume none, since I don't think we > got rep slots till 9.4 :-) > > > > > regards, tom lane > > > > > > -- > Jerry Sievers > Postgres DBA/Development Consulting > e: postgres.consult...@comcast.net > p: 312.241.7800 > > > >
