Alvaro Herrera wrote:
Csaba Nagy wrote:
Now when the queue tables get 1000 times dead space compared to their
normal size, I get performance problems. So tweaking vacuum cost delay
doesn't buy me anything, as not vacuum per se is the performance
problem, it's long run time for big tables is.
So for you it would certainly help a lot to be able to vacuum the first
X pages of the big table, stop, release locks, create new transaction,
continue with the next X pages, lather, rinse, repeat.
I got the impression that Csaba is looking more for "multiple
simultaneous vacuum" more than the partial vacuum. Not sure the best
way to set this up, but perhaps a flag in the pg_autovacuum table that
says "vacuum this table even if there is another vacuum running" that
way you can control things and not have autovacuum firing off lots of
vacuums at the same time. Sounds to me that these frequently updated
queue tables need to be monitored closely and not ignored for a long
period of time because we are vacuuming another table. Has anyone
looked more closely at the multiple vacuum patch that was submitted to
the patches list a while ago?
Matt
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
