Jim C. Nasby wrote:
Would it be difficult to vacuum as part of a dump? The reasoning behind this is that you have to read the table to do the dump anyway, so it would be a good time to be able to piggy-back other operations that need to read the entire table on top. I know vacuuming of indexes complicates this, so it's probably not as simple as just firing off a vacuum and copy at the same time (although that idea is probably worth testing, since it might still be a win).
This would be a nightmare on a large database. Think of how long it takes to dump 20 gig, now add how long it is going to take to vacuum that size of DB, now think about a 500 gig database.
Actually this also probably would not gain you much in 8.1 as vacuum in theory is already dealing with itself.
When dropping a table or index, is it's space immediately released in the FSM?
I would have to double check but I believe you would have to vacuum to reclaim the space to the FSM because the relationship is still there just like when you delete (but not truncate).
Also, would it be possible to add some means to check the status of a running vacuum? Even with vacuum verbose, once it starts in on a large table you have no way to know how far along it is.
That is an interesting thought... Perhaps a quick scan of the table to see how many dead rows there are? Then check back every n/10 ? Hmmm... I am not a C guy so I don't know if that is technically feasible (although probably possible) but it is interesting from a DBA perspective. Although that could be an issue on a large table as well I think.
Finally, if vacuum_delay is enabled, does vacuum_cost_page_miss consider a miss as not in the database buffer, or not in the kernel buffer? I remember discussions about trying to track IO request times to try and determine if something came out of kernel buffers or not, but AFAIK that's all vaporware right now...
Good question, anyone else? Sincerely, Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq