I've got a table that grows under Postgres 9.1. It's a high-throughput table with lots of inserts and deletes, but it should generally remain between 1-2k in terms of number or records. Auto-vacuum is enabled, and we've run manual vacuums. We've checked for locks and there don't appear be any. Running "vacuum verbose;" says that while there are millions of dead records, but "found 0 removable".

What might I have not thought about?

  • 1
    OK. It looks like the cause was a long running function. The function runs inside a select that creates a implicit transaction, which is why the problem occurs.
    – alex.collins
    Nov 21, 2012 at 10:49

2 Answers 2


Run this query:

SELECT * FROM pg_stat_activity ORDER BY xact_start NULLS LAST LIMIT 1;

If you get a row back where xact_start is not null and it's anything more than a few seconds old, that's your culprit. VACUUM cannot remove rows that have transaction_ids greater than or equal to the oldest open transaction in the system (even if it's in a completely different database). If you terminate that transaction and re-run the VACUUM, you should see more than 0 rows removable.


Stale prepared transactions?

SELECT * FROM pg_prepared_xacts;

Purely for diagnostic purposes and only if you can afford the lock duration, try seeing if VACUUM FULL reduces the table size. Does it actually get the lock and run? I would recommend setting a FILLFACTOR of 50 or less before running it, otherwise the table will immediately grow rapidly to return to steady-state size. See storage parameters. Do the same to the indexes, since otherwise they'll have to be grown again too.

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.