Re: vacuum

Mike Cianflone <mcianflone(at)littlefeet-inc(dot)com> writes:

> I know that vacuum has come up in the past, and even saw the
> discussion about putting a cron entry to have it run every once in a while,
> but I don't remember seeing anything about having it kick off via a trigger
> every so may inserts.
> Is there a relative consensus for how often to run vacuum? I have a
> table of about 8 columns that I fill with 100,000 items simply via a "\i
> alarms.sql". After 1,000 items or so it gets extremely slow to fill with
> data, and will take over a day to fill the entire thing unless I run vacuum
> once a minute.

Ummm... Are you wrapping the inserts in a transaction? If not, no
wonder it's slow--you're paying transaction overhead for each insert.

I don't think VACUUM has that much effect on insert performance, as
insert doesn't leave empty space in existing pages.

Try using COPY instead of INSERT, or at least wrap all your inserts in
a transaction. Also, drop indexes before running the insert, and
recreate them afterwards.

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time... --Dylan