VACUUM VERBOSE outputs, nonremovable "dead row versions cannot be removed yet"?

Question

I have a Postgres 9.2 DB where a certain table has lots of nonremovable dead rows:

# SELECT * FROM public.pgstattuple('mytable');
 table_len  | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
------------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 2850512896 |      283439 | 100900882 |          3.54 |          2537195 |     2666909495 |              93.56 |   50480156 |         1.77
(1 row)

Normal vacuuming also shows lots of nonremovable dead rows:

# VACUUM VERBOSE mytable;
[...]
INFO:  "mytable": found 0 removable, 2404332 nonremovable row versions in 309938 out of 316307 pages
DETAIL:  2298005 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 1.90s/2.05u sec elapsed 16.79 sec.
[...]

The table only has around 300.000 actual data rows, but 2.3 million dead rows (and this appears to make certain queries very slow).

According to SELECT * FROM pg_stat_activity where xact_start is not null and datname = 'mydb' order by xact_start; there is no old transaction accessing the database. The oldest transactions are some minutes old and haven't modified anything on the table yet.

I've also checked select * from pg_prepared_xacts (to check for prepared transactions) and select * from pg_stat_replication (to check for pending replications), both of which are empty.

There are lots of inserts, updates and deletes performed on that table, so I can understand that lots of dead rows are being created. But why aren't they removed by the VACUUM command?

Answer 1

The oldest transactions are some minutes old and haven't modified anything on the table yet.

That's not sufficient. I think what is required to mark these rows as dead is that, when these transactions were started, there was no other transaction that had touched these rows (doing an UPDATE or DELETE on them).

Updating or deleting a row will keep the previous version of the row physically where it was, and set its xmax field to the TXID of the current transaction. From the point of view of other transactions, this old version of the row is still visible if it is part of their snapshot. Each snapshot has an xmin and xmax to which the xmin and xmax of the row versions can be compared. The point is that VACUUM must compare row versions against the combined visibility of all live snapshots, as opposed to simply checking if a row change is definitely committed. The latter is necessary but not sufficient to recycle the space used by the old version.

For example, here's a sequence of events such that VACUUM can't clean up dead rows even though the transaction that modified them has finished:

• t0: Long running transaction TX1 starts
• t0+30mn: TX2 starts and sets itself in REPEATABLE READ mode.
• t0+35mn: TX1 finishes.
• t0+40mn: pg_stat_activity shows only the 10-mn old TX2
• t0+45mn: VACUUM runs but won't eliminate the old versions of the rows modified by TX1 (because TX2 might need them).

Answer 2

I was able to recreate this. Essentially, when inside of a transaction,

• In READ COMMITTED the default transaction level:
  • SELECT gets an AccessShareLock
  • VACUUM can clean up dead rows versions
  • pg_stat_activity.backend_xmin IS NULL for the transaction
• In SERIALIZABLE or REPEATABLE READ transaction levels:
  • SELECT gets an AccessShareLock
  • VACUUM can not clean up dead rows versions
  • pg_stat_activity.backend_xmin IS NOT NULL for the transaction
  • VERBOSE reports these rows as "nonremovable row versions" and "dead row versions"

Sample Data

CREATE TABLE bar AS
SELECT x::int FROM generate_series(1,10) AS t(x);

As a side note, if you delete anything from bar after you create the table, those rows become removable, and on VACUUM you'll see.

INFO:  "bar": removed # row versions in # pages

Transaction Sequence

Now, here is txn table to re-create scenario.

txn1       - BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
txn1       - SELECT * FROM bar;
      txn2 - DELETE FROM bar;      -- We delete after the select
      txn2 - VACUUM VERBOSE bar;   -- Can't remove the "dead row versions"

VACUUM can't remove those row versions because a subsequent SELECT * FROM bar; under REPEATABLE READ will still see them! The VACUUM above produces,

# VACUUM VERBOSE bar;
INFO:  vacuuming "public.bar"
INFO:  "bar": found 0 removable, 10 nonremovable row versions in 1 out of 1 pages
DETAIL:  10 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

Which is exactly what you're seeing.

Debugging the problem

To find out what query is preventing VACUUM from cleaning up the dead rows, run this..

SELECT query, state,locktype,mode
FROM pg_locks
JOIN pg_stat_activity
  USING (pid)
WHERE relation::regclass = 'bar'::regclass
  AND granted IS TRUE
  AND backend_xmin IS NOT NULL;

This will return something like this..

       query        │        state        │ locktype │      mode       
────────────────────┼─────────────────────┼──────────┼─────────────────
 SELECT * FROM bar; │ idle in transaction │ relation │ AccessShareLock

Solution

So let's return to our TXNs.. We need to kill/commit/rollback txn1, and rerun VACUUM

txn1       - COMMIT;
      txn2 - VACUUM VERBOSE bar;

And now we see,

# VACUUM VERBOSE bar;
INFO:  vacuuming "public.bar"
INFO:  "bar": removed 10 row versions in 1 pages
INFO:  "bar": found 10 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "bar": truncated 1 to 0 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.01 sec.

Special notes

1. It doesn't matter what rows were deleted, and what rows you selected for. The select gets the ACCESS SHARE lock on the table. And, then VACUUM can not remove the dead rows so they're marked as "nonremovable".

2. I think this is pretty poor behavior for VACUUM VERBOSE. I would have liked to see..

   DETAIL:  10 dead row versions cannot be removed yet
            could not aquire SHARE UPDATE EXCLUSIVE lock on %TABLE
   

Further reading

• This is a really great post by Erwin on, "What are the consequences of not ending a database transaction?". He touches on VACUUM there.

• This comment by @Craig Ringer which sums this up

  SERIALIZABLE or REPEATABLE READ xacts block vacuum from removing dead rows though. It's only a READ COMMITTED xact with no active snapshot that lets vacuum proceed. Some other things, like WITH HOLD cursors, prepared xacts, etc do too.

• For what may turn out to be a trip down the rabbit hole my question for more information on backend_xmin inspired by my work with this answer

Also, thanks to Daniel Vérité for making me look into the system catalog and the behavior of VACUUM on this one.

Answer 3

One more thing you could check (if you have it enabled) is replication delay. If you have hot_standby_feedback enabled, then some tuples on the primary might be still marked as active only because they are being still used on the replicated instance. To check the option: show hot_standby_feedback;

And then look at backend_xmin from select * from pg_stat_replication;

Answer 4

I was facing this issue even after verifying that my database didn't have any active transaction or active lock on a certain "foo" table.

The following method successfully removed all those non removable dead rows from "foo":

CREATE TEMP TABLE temp_foo AS SELECT * FROM "foo";
TRUNCATE TABLE "foo";
INSERT INTO "foo" SELECT * FROM temp_foo;
DROP table temp_foo;

Just keep in mind that if you have a large table with too many rows this may not be a viable solution, since all table rows are transfered to a temporary table and then transfered back to the original table.