Reviving a thread from October 2010 about vacuuming in postgres.
Op 20-10-10 17:35, David Blewett schreef:
> On Tue, Oct 19, 2010 at 1:05 AM, Noe Misael Nieto Arroyo
> <n...@iservices.com.mx> wrote:
>> And if yes, What kind of VACUUM is good? FULL, CLUSTER?
> VACUUM FULL is almost never a good idea; autovac should be sufficient
> for most needs, and can be tweaked to meet particular performance
On a test database of 524 MB I did a vacuum and this had no effect. A
vacuum full resulted in a significant drop to 423 MB. But this is just
a local test database on my laptop so it had probably never been
> I have had very good results doing a CLUSTER after an initial
> zodbconvert run to significantly reduce the size of the database. I
> highly recommend doing CLUSTER after any large data loads.
CLUSTER does not do anything unless you first tell it which index to use
for clustering a table. Using postgres 8.4 I have done this for the
four table for which I think this is useful (the other tables had no
cluster blob_chunk using blob_chunk_pkey;
cluster current_object using current_object_pkey;
cluster object_state using object_state_pkey ;
cluster transaction using transaction_pkey;
This did not have an effect on the above mentioned test database though.
But that may be because it had already been fully vacuumed.
Would these be the correct tables and indexes? Some of these tables
have other indexes as well, but these are the primary ones.
Would it make sense to add these lines to the postgres code in
relstorage that creates the tables? Then users would only need to
perform the 'CLUSTER;' command without any extra parameters and it would
I am asking about this because I have a database of about 22 GB on which
I have performed a migration to blob files. The size is now about 38 GB
and I am pretty sure the autovacuum has already been done.
BTW, I have no access to the filesystem on which this database is
stored; the size is simply what the Database Control Panel in the Zope
Maurits van Rees
Web App Programmer at Zest Software: http://zestsoftware.nl
Personal website: http://maurits.vanrees.org/
For more information about ZODB, see the ZODB Wiki:
ZODB-Dev mailing list - ZODB-Dev@zope.org