Thread: Vacuum not deleting tuples when lockless

Martín Fernández <fmartin91@gmail.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.consulting@comcast.net
p: 312.241.7800

=?UTF-8?q?Mart=C3=ADn_Fern=C3=A1ndez?= <fmartin91@gmail.com> writes:
> We are experiencing some `vacuum` issues with a given table (potentially more). When a manual vacuum runs on the
giventable 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.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> =?UTF-8?q?Mart=C3=ADn_Fern=C3=A1ndez?= <fmartin91@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.consulting@comcast.net
p: 312.241.7800