Matthew T. O'Connor wrote:
But we track tuples because we can compare against the count given by the stats system. I don't know of a way (other than looking at the FSM, or contrib/pgstattuple ) to see how many dead pages exist.
I think making pg_autovacuum dependent of pgstattuple is very good idea.
Only if pgstattuple can become much cheaper than it is now. Based on the testing I did when I wrote pg_autovacuum, pgstattuple cost nearly the same amount as a regular vacuum. Given that, what have we gained from that work? Wouldn't it just be better to run a vacuum and actually reclaim space rather than running pgstattuple, and just look and see if there is free space to be reclaimed?
Perhaps we could use pgstattuple ocasionally to see if we are going a good job of keeping the amount of dead space to a reasonable level, but I'm still not really sure about this.
Probably it might be a good idea to extend pgstattuple to return pages that are excessively contaminated and clean them ASAP. Step by step getting closer to daemonized vacuum.
I don't know of anyway to clean a particular set of pages. This is something that has been talked about (partial vacuums and such), but I think Tom has raised several issues with it, I don't remember the details. Right now the only tool we have to reclaim space is vacuum, a whole table at a time.
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster