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:

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?

  • 1
    is this a production system. There are other options for VACUUM, like FULL, but warning: you probably want to do this when the database has low volume as it will lock the table. Related read: wiki.postgresql.org/wiki/VACUUM_FULL (and note the details about when and if) to do a VACUUM FULL and about FILLFACTOR and CLUSTER) Commented Jan 13, 2017 at 9:59
  • You've done your homework on trying to vacuum it. and checking for long standing transactions. 9.2 is a bit old? Is upgrading to the latest stable 9.6 out of the realm of possibility? Commented Jan 13, 2017 at 10:08
  • @EvanCarroll updating might be possible, but still quite difficult. I don't really want to do that as an experiment, without any indication that 9.3+ actually have fixes for this kind of problem.
    – oliver
    Commented Jan 13, 2017 at 10:41
  • 2
    Unremovable dead rows are typically caused by long running transactions. Make sure that you do not have have any connections with "idle in transaction" (for a long time)
    – user1822
    Commented Jan 13, 2017 at 14:00
  • @oliver looking forward to getting input on my answer. Commented Jan 14, 2017 at 0:52

4 Answers 4


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

  • In READ COMMITTED the default transaction level:
  • 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

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

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


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,

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

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

  • 3
    Excellent post. Looks like Daniel nailed it, though. And this is the deluxe version of his answer with demo, background, links and more explanation. Commented Jan 14, 2017 at 2:09
  • Amazing analysis! I don't have access to the software on weekends, but will check this on Monday. But I think the problem is not so much the dead rows created by pending transactions but rather the dead rows created by past transactions that have been finished long ago.
    – oliver
    Commented Jan 14, 2017 at 11:04
  • A SELECT having an ACCESS SHARE lock on a table does not prevent VACUUM to take a SHARE UPDATE EXCLUSIVE lock on that table. The "what's going on" section of the answer has it backwards, it seems to me. Also this other question: dba.stackexchange.com/questions/21068/… is a good read for how too-strong locking can prevent vacuum to work, but vanilla reads don't cause this problem. Commented Jan 14, 2017 at 15:23
  • Right, well, some of that is because this isn't implemented with the locking system. I'm looking into that and after I have a better idea of how it works I'll update it even further. Actually, the lock that vacuum should require is a function of the backend_xmin. I'll hide that section for in a comment on the site, because I agree. It's clearly not strictly right. In the mean time, help me answer this question on internals: dba.stackexchange.com/q/161050/2639 =) Commented Jan 14, 2017 at 17:27

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).
  • Under REPEATABLE READ, if that was the case after TX1 "finishes" (commits?) without a rollback wouldn't TX2 immediately get ERROR: could not serialize access due to concurrent update on the next DML (when txn2 fails to get a lock on rows modified after the txn2 snapshot)? Commented Jan 13, 2017 at 21:44
  • 1
    @EvanCarroll: It's much more simpler than that, TX2 does not even has to write anything and there is no lock or conflict involved, it's just a matter of visibility. The system cannot destroy rows that were visible when TX2 started until TX2 terminates, that's all. Commented Jan 13, 2017 at 22:05
  • 2
    @EvanCarroll: AFAIK visiting a row does not mark anything. For one thing, read performance would be destroyed if every row read would incur a write. Commented Jan 13, 2017 at 22:34
  • 3
    Insightful! The principal even works without any long running transactions. Ceaseless high load might achieve the same. Commented Jan 14, 2017 at 2:15
  • 2
    So this would mean that a "chain" of overlapping transactions (each performing inserts/updates) would prevent vacuuming of all dead rows created by one of the overlapping transactions? That would indeed explain the problems I'm seeing - my software performs exactly this usage pattern. Each of my transactions takes less than a minute (and creates maybe 1.000 dead rows); but the entire chain remains unbroken for days.
    – oliver
    Commented Jan 14, 2017 at 11:05

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;


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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.