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 to run 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

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