PSQL: VACUUM ANALYZE is showing incorrect oldest xmin-CodePudding

When I run vacuum verbose on a table, the result is showing an oldest xmin value of 9696975, as shown below:

table_xxx: found 0 removable, 41472710 nonremovable row versions in 482550 out of 482550 pages
DETAIL:  41331110 dead row versions cannot be removed yet, oldest xmin: 9696975
There were 0 unused item identifiers.

But when I check in pg_stat_activity, there are no entries with the backend_xmin value that matches this oldest xmin value. Below is the response I get when I run the query:

SELECT backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

Response:

backend_xmin
------------
10134695
10134696
10134696
10134696
10134696

The issue I am facing is that the vacuum is not removing any dead tuples from the table. I tried methods mentioned in: this post. But it didn't help.

edit: The PostgreSQL version is 13.6 running in Aurora cluster.

CodePudding user response：

A row is only completely dead when no live transaction can see it anymore. I.e. no transaction that has been started before the row was updated / deleted is still running. That does not necessarily involve any locks at all. The mere existence of a long-running transaction can block VACUUM from cleaning up.

So the system view to consult is pg_stat_activity. Look for zombi-transactions that you can kill. Then VACUUM can proceed.

Old prepared transactions can also block for the same reason. You can check pg_prepared_xacts for those.

Of course, VACUUM only runs on the primary server, not on replicas (standby) instances - in case streaming replication has been set up.

CodePudding user response：

Apart from old transactions, there are some other things that can hold the “xmin horizon” back:

stale replication slots (see pg_replication_slots)
abandoned prepared transactions (see pg_prepared_xacts)