I have a postgres 9.3.4 database table which (intermittently but reliably) gets into a state where queries get blocked indefinitely (at least for many hours) behind an automatic vacuum. I was under the impression that vacuum should never take any blocking locks for any significant period of time, and so would like help resolving the issue.
The process blocking the query is: postgres 21985 11304 98 Feb13 ? 1-14:20:52 postgres: autovacuum worker process <db_name> which is running the query autovacuum: VACUUM public.<table_name>
If it doesn't include the "to prevent wraparound", then it should sacrifice itself as soon as it realizes it is blocking something else. If it is not doing that, something is wrong.
If it does say "(to prevent wraparound)", then see all the other comments on this thread.
Notes: - This database table is used for about 6 million row writes per day, all of which are then deleted at the end of the day.
How many transactions do those 6 million writes comprise?
- Our application kicks off a manual vacuum against this table each night which doesn't hit this problem, as far as we're aware.
If it were having problems, would you be aware of it? Do you see in the log files the completion of the vacuum? Or look in pg_stat_user_tables to see when last_vacuum was. If it runs every night and succeeds, it is hard to see why wraparound would ever kick in. Unless you are hitting 150,000,000 transactions in a day.