Vacuum not deleting tuples when lockless

From: Martín Fernández <fmartin91(at)gmail(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Vacuum not deleting tuples when lockless
Date: 2018-09-14 23:15:43
Message-ID: 5b9c3f8aaa20125720000001@polymail.io
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2018-09-15 00:06:25 Re: column information from view
Previous Message Chris Williams 2018-09-14 23:07:51 Re: Slow shutdowns sometimes on RDS Postgres