Martín Fernández <fmartin91@xxxxxxxxx> writes: > * > Hello, > > 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. > ``` > > I've been investigating the reasons for vacuum not being able to do > it's work and I found that generally the problem is caused by open > transactions referencing the dead tuples. I also found that locking > can be a problem as well. > > I did check that no long running transaction was happening and no > locking was happening before running `vacuum` on the given table. > > I used this query to check the locks: > > ``` > SELECT query, state,locktype,mode > FROM pg_locks > JOIN pg_stat_activity > USING (pid) > WHERE relation::regclass = 'my_table'::regclass > AND granted IS TRUE; > ``` > > I'm not sure where else to look for a potential issue that could be > causing this problem. A few days back we had to use re_pack on the Also check for... * Old open prepared xacts. * Inactive replication slots. * Standbys confi'd with large vacuum clean up delay and feedback enabled. HTH > given table to solve our performance issues. > > Important thing to clarify is that we are using postgresql 9.2.24 > > Thanks in advance! > > Best, > Martín > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@xxxxxxxxxxx p: 312.241.7800
