Thread: replacements for vacuum?
Greetings, Are there any alternatives to vacuum (and, i'm aware of autovacuum)? thanks, Lonni -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand http://netllama.linux-sxs.org
On Fri, Dec 17, 2004 at 12:50:42 -0800, Lonni J Friedman <netllama@gmail.com> wrote: > Greetings, > Are there any alternatives to vacuum (and, i'm aware of autovacuum)? What problem are you trying to solve?
Lonni J Friedman <netllama@gmail.com> writes:
> Are there any alternatives to vacuum (and, i'm aware of autovacuum)?
CLUSTER is frequently a competitive alternative to VACUUM FULL.
In 8.0, there are some flavors of ALTER TABLE that rewrite the whole
table; this would work too, and should be faster than CLUSTER if you
don't care about the resulting table order.
Neither of these are a good substitute for plain VACUUM, but when you
have a table that's sparse enough to need a VACUUM FULL, consider them.
regards, tom lane
On Fri, 17 Dec 2004 15:28:30 -0600, Bruno Wolff III <bruno@wolff.to> wrote: > On Fri, Dec 17, 2004 at 12:50:42 -0800, > Lonni J Friedman <netllama@gmail.com> wrote: > > Greetings, > > Are there any alternatives to vacuum (and, i'm aware of autovacuum)? > > What problem are you trying to solve? I'd like to be able to run vacuum in a 'test' or read-only mode where i'd see what it would do before actually running it. I don't see any mention of any options to accomplish this in the vacuum man page. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand http://netllama.linux-sxs.org
On Fri, Dec 17, 2004 at 18:53:42 -0800, Lonni J Friedman <netllama@gmail.com> wrote: > On Fri, 17 Dec 2004 15:28:30 -0600, Bruno Wolff III <bruno@wolff.to> wrote: > > On Fri, Dec 17, 2004 at 12:50:42 -0800, > > Lonni J Friedman <netllama@gmail.com> wrote: > > > Greetings, > > > Are there any alternatives to vacuum (and, i'm aware of autovacuum)? > > > > What problem are you trying to solve? > > I'd like to be able to run vacuum in a 'test' or read-only mode where > i'd see what it would do before actually running it. I don't see any > mention of any options to accomplish this in the vacuum man page. That is because there isn't much point in doing all of that disk IO and not actually freeing up the deleted tuples. Unless you only want this out of curiosity, I don't think you have told us what problem you are really trying to solve.
Lonni J Friedman <netllama@gmail.com> writes:
> I'd like to be able to run vacuum in a 'test' or read-only mode where
> i'd see what it would do before actually running it.
Er ... what possible value would that have? ISTM it would expend 80% of
the effort to achieve 0% of the result.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Lonni J Friedman <netllama@gmail.com> writes: > > > I'd like to be able to run vacuum in a 'test' or read-only mode where > > i'd see what it would do before actually running it. > > Er ... what possible value would that have? ISTM it would expend 80% of > the effort to achieve 0% of the result. Just a guess, maybe you mean "analyze" when you say "vacuum"? People often conflate them since they often run both together with "vacuum analyze". But there wouldn't be much point in running a test vacuum, they're might be some point in running a test analyze. If so, one little known feature: you can run analyze inside a transaction. The new statistics are only used by that session until you commit. I started a script to explain a set of queries, run analyze, then re-explain the queries and compare the plans before either committing or rolling back. I think it would be a useful DBA tool for a high availability production system, but I haven't finished it. -- greg