Re: how to vacuum thru psycopg

Al Niessner wrote:
> I am using psycopg 2.0.8 and want to vacuum my postgresql database as I
> fill it. I found out that some of what I am seeing performance wise is
> the database needing to optimize for my queries and insertions. So, I
> was told to vacuum it occasionally.

"Occasionally" in this case usually means every day or couple of days,
not in mid-transaction.  Vacuuming removes stale data that accumulates
from deletes and updates.  If you are doing a whole series of inserts, I
would expect vacuuming to serve no purpose whatsoever.  Where did you
get the advice to vacuum so often?

> So, I tried to do it in my program
> as it grew. The error says it all:
>
>   File
> "/home/niessner/Projects/PictureLibrary/Source/PictureLibrary/Model/DB/PostgresqlWithPsycopg/actions.py", line 247, in transactionClose
>     if len (ids) == 10**int(log10 (len (ids))): self.__execute("vacuum
> full", close=False, query=False)
>   

Interesting way to do that.  I'm fighting the urge to optimize away the
expensive logarithm and exponentiation operations, because of course the
time those take is dwarfed by the many seconds required by a vacuum.

Are you literally storing image files in the database?  I know Postgres
has the ability to do that, but it has always seemed like "database
abuse" to me.  It has always seemed more sensible to use the file system
to hold the images, and store a file name or a URL in the database. 
Still, as you say, this should work.

-- 
Tim Roberts, [email protected]
Providenza & Boekelheide, Inc.