Autovacuum of a large table takes too long

Question

I upgraded my 9.4 postgresql cluster to 9.6. (via pg_upgrade, so my db stats were not moved to a new cluster) I have a large table(about 450M records). This table is much used in my code(many selects and less upserts). When I start my postgres service, Postgres starts autovacuum automatically, to it takes a lock on my table. So I can't do anything: neither truncate the table nor analyze it manually. I tried to set autovacuum=off in my config file, but it didn't help(why?! I restarted the server of course)

UPDATE: My aim is to start using the table as soon as possible. Truncate will help(as the table will be empty), ANALYZE should help(Postgres will start using proper indexes)

What is the fastest way to: 1) Trunate the table OR 2) Analyze the table ? Any help would be much appreciated.

UPDATE: Here is the output of the query to watch the locks:

SELECT psa.pid,granted,query FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid where locktype='relation';


  pid  | granted | query                                                                                                                          
-------+---------+---------------------------

 11831 | t       | autovacuum: VACUUM ANALYZE public.ig_tasks_users (to prevent wraparound)
 11831 | t       | autovacuum: VACUUM ANALYZE public.ig_tasks_users (to prevent wraparound)
 11831 | t       | autovacuum: VACUUM ANALYZE public.ig_tasks_users (to prevent wraparound)
 11831 | t       | autovacuum: VACUUM ANALYZE public.ig_tasks_users (to prevent wraparound)
 11831 | t       | autovacuum: VACUUM ANALYZE public.ig_tasks_users (to prevent wraparound)
 11831 | t       | autovacuum: VACUUM ANALYZE public.ig_tasks_users (to prevent wraparound)
 11831 | t       | autovacuum: VACUUM ANALYZE public.ig_tasks_users (to prevent wraparound)
 11831 | t       | autovacuum: VACUUM ANALYZE public.ig_tasks_users (to prevent wraparound)
 11831 | t       | autovacuum: VACUUM ANALYZE public.ig_tasks_users (to prevent wraparound)

When I analyze the table from my psql console and see data from pg_stat_activity:

query                  |         backend_start         |          xact_start           |          query_start          |         state_change          | state  | wait_event_type | wait_event
analyze ig_tasks_users;| 2017-01-19 10:03:30.287791+01 | 2017-01-19 10:07:11.683817+01 | 2017-01-19 10:07:11.683817+01 | 2017-01-19 10:07:11.683822+01 | active | Lock            | relation

If I properly understand, my VACUUM ANALYZE locks the table and my manual ANALYZE tasks is locked. (Is that right?)

Answer 1

run truncate in one session and leave it working, then open new connection and kill all other sessions with pg_terminate_backend(pid). Keep in mind

• truncate will erase all your data from table
• autovacuum is better to be on

Answer 2

If auto vacuum is taking too long, it might actually be because you switched it off for a while.

The usual goal of routine vacuuming is to do standard VACUUMs often enough to avoid needing VACUUM FULL. The autovacuum daemon attempts to work this way, and in fact will never issue VACUUM FULL.

The solution to slow vacuum is to vacuum more often! Also note that settnig autovaccum to off may not switch it off completely.