Re: Answering my own question - Mailing list pgsql-novice

Well, this is only my second day on the list, but I don't think "NOVICE"
could ever have a question to be embarrased about.

VACUUM does 3 things (at least in 7.2) :

-  VACUUM  (alone)  takes rows marked for deletion, and frees up the space
to be re-used by later INSERTs or UPDATEs.
      - I'm guessing, but it shure looks like DELETE doesn't physically
delete, it logically deletes.
      - similarly UPDATE doesn't seem to physically update in place, but
logically update by marking the old record as "deleteable" and INSERTing
(in effect) a new record with your changes.
      - all of which leaves a lot of Logically deleted, but physically
still present , records in your file.

- VACUUM FULL  apparently goes one step further than VACUUM, and actually
frees up the disk space for the operating system.
      - FULL is NOT an option to VACUUM in 7.1.3 so I'm guessing, based on
comments from other emails.
      - In our shop we're running Postgres 7.1.3 on a Solaris 2.(6?7?8)
E3500.  After a VACUUM, the unix files in pgsql/data/base are no smaller.
      - eventually these get so big, its worthwhile to pg_dump , DROP, and
reload a volatile table. At which point the unix files are smaller, and
response time is improved.
      - presumably in postgres 7.2+  , FULL was added as an option to
VACUUM to do this inherently. In IBM mainframe speak, it somewhat analogous
to doing a "freespace" and "de-frag".

- VACUUM ANALYZE, as I've learned so well these past two days, provides
stats on the remaining rows in your tables, allowing the QUERY PLAN
OPTIMIZER to best determine whether or not to use any indicies, and if so,
which ones.

- VACUUM VERBOSE - just dumps the output of the VACUUM to the calling
script or command line. A copy of said output is in your server.log file in
any case.

Interestingly enough VACUUM VERBOSE ANALYZE doesn't get you any stats from
the Analyzer, just the line "Analyzing...."  I was hoping for crytpic clues
for things like "commonly recurring key, index useless"  type messages. Or
"too few rows, index useless".


Gerry Smit,
Toronto, Canada.





              
                      "Balazs Wellisch"
              
                      <balazs@bwellisch.com>         To:      pgsql-novice@postgresql.org
              
                      Sent by:                       cc:
              
                      pgsql-novice-owner@pos         Fax to:
              
                      tgresql.org                    Subject: Re: [NOVICE] Answering my own question
              

              

              
                      15/05/2002 06:57 PM
              

              

              




Hi all,

I'm embarrassed to even ask this, but this being a novice list here it
goes.

What exactly is Vacuum? Is there an equivalent of this in MS SQL Server?

Thanks,
Balazs Wellisch


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster