Hello, there are currently no transactions whatsoever, the app is paused. I can even restart the database if needed. I ran vacuum full, because as I mentioned above it seemed to me that manual vacuum did not change the relminmxid of a table. Unfortunately, an upgrade is not an option :(
name | setting -------------------------------------+----------- autovacuum | on autovacuum_analyze_scale_factor | 0.1 autovacuum_analyze_threshold | 50 autovacuum_freeze_max_age | 200000000 autovacuum_max_workers | 3 autovacuum_multixact_freeze_max_age | 400000000 autovacuum_naptime | 60 autovacuum_vacuum_cost_delay | 10 autovacuum_vacuum_cost_limit | -1 autovacuum_vacuum_scale_factor | 0.2 autovacuum_vacuum_threshold | 50 autovacuum_work_mem | -1 log_autovacuum_min_duration | -1 vacuum_cost_delay | 0 vacuum_cost_limit | 2000 vacuum_cost_page_dirty | 20 vacuum_cost_page_hit | 1 vacuum_cost_page_miss | 10 vacuum_defer_cleanup_age | 0 vacuum_freeze_min_age | 50000000 vacuum_freeze_table_age | 150000000 vacuum_multixact_freeze_min_age | 3000000 vacuum_multixact_freeze_table_age | 140000000 The default vacuum_multixact_freeze_min_age is 5M according to the doc, but according to the documentation, it's limited silently to half of autovacuum_multixact_freeze_max_age. So I guess I have to get those 400M to much lower number? Thank you very much Peter On Wed, Aug 9, 2017 at 10:39 PM Andres Freund <and...@anarazel.de> wrote: > Hi, > > On 2017-08-09 16:30:03 -0400, Alvaro Herrera wrote: > > > > One particular table before vacuum full: > > > > > > relname | relminmxid | table_size > > > ------------------------------+------------+------------ > > > delayed_jobs | 1554151198 | 21 GB > > > > > > And after vacuum full: > > > > > > relname | relminmxid | table_size > > > --------------+------------+------------ > > > delayed_jobs | 1554155465 | 6899 MB > > > > > > Shouldn't be the relminmxid changed after vacuum full, or am I not > > > understanding something? > > FWIW, VACUUM FULL isn't a good tool to use here. It's commonly way more > expensive than a normal vacuum (it rewrites the entire table, and > rebuilds indexes). > > > > But it did change ... the problem is that it didn't change enough (only > > 4000 multixacts). Maybe your multixact freeze min age is too high? > > Getting rid of 15 GB of bloat is a good side effect, though, I'm sure. > > I wonder if there's longrunning transactions preventing cleanup. I > suggest checking pg_stat_activity, pg_prepared_xacts, > pg_replication_slot for older stuff. > > Greetings, > > Andres Freund >