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?)

1 truncate will erase all rows, 2 analyze will gather statistics, you can't compare those — Vao Tsun
autovacuum should release table, alowing you to lock it. add some output to question — Vao Tsun
@VaoTsun I realize that TRUNCATE and ANALYZE do different things but any of them could help me(I updated my question). Also added console output. — Denis Kuznetsov
well, run truncate in one session nad leave it working, then open new connection and kill all other sessions — Vao Tsun

Vao Tsun Vao Tsun · Accepted Answer · 2017-01-19T09:49:06

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

Autovacuum of a large table takes too long

3 Answers