Thread: Vacuum analyze in 7.1.1

David Wall writes:

> Does a 'vacuum analyze' do the same functions as a plain 'vacuum' PLUS the stats updates?  Just wondering if I need
torun both commands from time to time, or if I can just do 'vacuum analyze'?  I'm running 7.1.1 now. 

yes / no / yes

However, depending on your application you might want to run 'vacuum' more
often than 'vacuum analyze'.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

"David Wall" <d.wall@computer.org> writes:
> Does a 'vacuum analyze' do the same functions as a plain 'vacuum' PLUS the
> stats updates?

Yes, exactly.

AFAIK that's always been true, but some of the documentation has been
confused about it :-(.  If you can find anything in the 7.1 docs that
sounds like VACUUM ANALYZE doesn't include VACUUM, please point it out.

            regards, tom lane

> AFAIK that's always been true, but some of the documentation has been
> confused about it :-(.  If you can find anything in the 7.1 docs that
> sounds like VACUUM ANALYZE doesn't include VACUUM, please point it out.

Thanks.  The documentation doesn't say that VACUUM ANALYZE doesn't do a
regular VACUUM as well, but it does say:

"VACUUM serves two purposes in Postgres as both a means to reclaim storage
and also a means to collect information for the optimizer."  So, I know it
does two things.  It then says:

"VACUUM ANALYZE collects statistics representing the dispersion of the data
in each column. This information is valuable when several query execution
paths are possible."  This is also clear to me, but it left open the
possibility that this is ALL that ANALYZE does, and does not clean out
records and update the stats as VACUUM does.  The docs say that VACUUM:

"VACUUM opens every table in the database, cleans out records from rolled
back transactions, and updates statistics in the system catalogs. The
statistics maintained include the number of tuples and number of pages
stored in all tables."

Isn't also true that VACUUM cleans out deleted and updated rows for
committed transactions?  And for those who use large objects, I understand
that vacuum now cleans those up when they are deleted as well (no need for
vacuumlo anymore).  Perhaps it would be clear to just add words to that
affect, and to mention that VACUUM ANALYZE does a VACUUM plus it collects
statistics...


David

"David Wall" <d.wall@computer.org> writes:
> The docs say that VACUUM:

> "VACUUM opens every table in the database, cleans out records from rolled
> back transactions, and updates statistics in the system catalogs. The
> statistics maintained include the number of tuples and number of pages
> stored in all tables."

> Isn't also true that VACUUM cleans out deleted and updated rows for
> committed transactions?

Okay.  As far as the VACUUM reference page goes, that's already changed
for 7.2 --- you might like to look at

http://www.ca.postgresql.org/devel-corner/docs/postgres/sql-vacuum.html

The current description there is:

    VACUUM reclaims storage occupied by deleted tuples. In normal Postgres
    operation, tuples that are DELETEd or obsoleted by UPDATE are not
    physically removed from their table; they remain present until a VACUUM
    is done.  Therefore it's necessary to do VACUUM periodically, especially
    on frequently-updated tables.

    With no parameter, VACUUM processes every table in the current
    database. With a parameter, VACUUM processes only that table.

    VACUUM ANALYZE performs a VACUUM and then an ANALYZE for each selected
    table. This is a handy combination form for routine maintenance
    scripts. See ANALYZE for more details about its processing.

(The last para refers to the fact that ANALYZE is also available as a
separate command in 7.2; that wasn't true before.)

I was mainly wondering if you'd come across any misstatements other than
on the command reference page...

> And for those who use large objects, I understand that vacuum now
> cleans those up when they are deleted as well (no need for vacuumlo
> anymore).

Mmm, not really.  As of 7.1, VACUUM will clean up space occupied in
pg_largeobject by deleted or modified LOs.  However, it doesn't try to
detect whether an LO still has any references or not, so you still need
vacuumlo if your application is not careful to issue lo_unlink at all
the right times.  Not sure where this should be documented.  The docs
on large objects are pretty bad anyway :-(

            regards, tom lane

The new vacuum comments are much more clear.  Bravo.

As for the large objects, I'll have to see what happens when I delete a row
that contains a large object.  Sounds like they are not being deleted for me
as I had thought.  That's too bad since I know it must be a common problem
for those who use them.  I use the JDBC library, so I don't really even
create the large objects except through standard JDBC calls like
PreparedStatement.setBytes().  I'll have to see if JDBC does an unlink
anywhere.

David

> I'll have to see if JDBC does an unlink anywhere.

Whoa!  Looks like JDBC (as makes sense if you think about the libraries)
doesn't unlink those large objects.  There is a call in the
postgresql-specific for doing the lo_unlink, but it requires an OID, and
when deleting a row, most of the time I only know the primary key, not the
OIDs inside.  So, I guess I'll have to run vacuumlo from contrib before I do
the regular vacuum.

Does anybody know if vacuumlo is truly safe to use?  I just ran it on my
test system and it removed a bunch of orphans, but I just hope they were
really orphans!

David