[ZODB-Dev] RelStorage and PostgreSQL's VACUUM

[Maurits van Rees]

Hi all,

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
> <noe at 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
> issues.

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 
autovacuumed.

> 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 
content):

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 
work.


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 
root reports.

Cheers,

-- 
Maurits van Rees
Web App Programmer at Zest Software: http://zestsoftware.nl
Personal website: http://maurits.vanrees.org/