Re: Vacuum not deleting tuples when lockless

Martín Fernández <fmartin91(at)gmail(dot)com> 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(dot)consulting(at)comcast(dot)net
p: 312.241.7800