Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Nov 21 '12 at 16:49

This question came from our site for professional and enthusiast programmers.

    
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 '12 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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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