7

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?

  • 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) – ypercubeᵀᴹ Jan 13 '17 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? – Evan Carroll Jan 13 '17 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 Jan 13 '17 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) – a_horse_with_no_name Jan 13 '17 at 14:00
  • @oliver looking forward to getting input on my answer. – Evan Carroll Jan 14 '17 at 0:52
7

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)? – Evan Carroll Jan 13 '17 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. – Daniel Vérité Jan 13 '17 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. – Daniel Vérité Jan 13 '17 at 22:34
  • 3
    Insightful! The principal even works without any long running transactions. Ceaseless high load might achieve the same. – Erwin Brandstetter Jan 14 '17 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 Jan 14 '17 at 11:05
6

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

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

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

  • 1
    Excellent post. Looks like Daniel nailed it, though. And this is the deluxe version of his answer with demo, background, links and more explanation. – Erwin Brandstetter Jan 14 '17 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 Jan 14 '17 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. – Daniel Vérité Jan 14 '17 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 =) – Evan Carroll Jan 14 '17 at 17:27
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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