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

Biscuit

1 truncate will erase all rows, 2 analyze will gather statistics, you can't compare those – Express

autovacuum should release table, alowing you to lock it. add some output to question – Express

@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. – Biscuit

well, then truncating will be faster of course – Express

@VaoTsun If I run TRUNCATE, nothing happens for about 30-60 minutes. I think there is a problem with my query(most probably it waits for some lock to free). But how can I release the lock to run my TRUNCATE query ? – Biscuit

well, run truncate in one session nad leave it working, then open new connection and kill all other sessions – Express

Vao Tsun that worked! Great, thanks a lot!!! – Biscuit

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

Express

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.

Limewater