1

I have a table with 85% dead rows which is giving me trouble. I executed VACUUM (VERBOSE) MY_TABLE as per this SO response and according to the results, no active session is currently holding my xmin horizon back (so I don't see which session I could terminate to cause autovacuum to remove the dead rows).

The results of VACUUM (VERBOSE) MY_TABLE were like this:

...
CPU: user: 0.01 s, system: 0.01 s, elapsed: 0.04 s.
INFO:  "my_table": found 69454 removable, 364950 nonremovable row versions in 41903 out of 82105 pages
DETAIL:  165590 dead row versions cannot be removed yet, oldest xmin: 3914663626
There were 10532004 unused item pointers.
Skipped 3 pages due to buffer pins, 39715 frozen pages.
...

Then, running this query:

SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

Yielded:

  pid  | datname  |     usename     | state  | backend_xmin 
-------+----------+-----------------+--------+--------------
 16664 | my_table | ¤               | active |   3914835133
 30837 | my_table | my_user         | active |   3915292658
...

So I don't see any session that has an xmin as low as 3914663626.

I also tried the two other queries from that SO answer: the one for prepared transactions and for replication slots, and both yielded no rows.

What else could be holding vacuum from removing those dead rows?

(If you want the full output of the VACUUM (VERBOSE) MY_TABLE let me know, it's about 30 lines.)

Daniel
  • 21,933
  • 14
  • 72
  • 101
  • I don't think this question and the one it's marked a duplicate of have the same answer. In their case there was a living session they could call `pg_cancel_backend` on, but in my case there is not. I posted the results of the query showing all active sessions with their xmin, and in there no active session has the same xmin than the one the auto vacuum reports. – Daniel Jun 26 '19 at 16:47
  • Hi @Daniel I am also facing the same issue. Could you let me know how you were able to solve the issue? – arundevma Apr 10 '22 at 18:00
  • Hi, I ended up restarting the DB and doing the VACUUM manually while no other process was reading/writing to my tables. Then I changed my process to do fewer writes to avoid having the same issue in the future. Hope it helps. – Daniel Apr 12 '22 at 01:22

0 Answers0