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