This command helps VACUUM FULL ANALYZE VERBOSE; but in 2-3 hours it starts working slowly again Any ideas?
VACUUM FULL requires a table lock, is it getting blocked behind other locks?
Here is a sample query to view outstanding locks.
SELECT w.locktype AS waiting_locktype,w.relation::regclass AS waiting_table,w.transactionid, substr(w_stm.query,1,20) AS waiting_query,w.mode AS waiting_mode,w.pid AS waiting_pid, other.locktype AS other_locktype,other.relation::regclass AS other_table, other_stm.query AS other_query,other.mode AS other_mode,other.pid AS other_pid, other.granted AS other_granted FROM pg_catalog.pg_locks AS w JOIN pg_catalog.pg_stat_activity AS w_stm ON (w_stm.pid = w.pid) JOIN pg_catalog.pg_locks AS other ON ((w."database" = other."database" AND w.relation = other.relation) OR w.transactionid = other.transactionid) JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.pid = other.pid) WHERE NOT w.granted AND w.pid <> other.pid
