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 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.

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

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 <gsievers19@comcast.net> wrote: