Re: Deleting bytea, autovacuum, and 8.2/8.4 differences

Hi Dave,

thank you for your answers! Here some comments:

Dave Crooke:

> > * The table just has 5 unused int columns, a timestamp,
> > OIDs, and the bytea column, *no indices*; the bytea storage
> > type is 'extended', the 16 MB are compressed to approx. the
> > half.
> >
>
> Why no indices?

Simply because the test case had just < 50 rows (deleting
all rows older than 2 minues). Later on I would use indices.

> > while it is planned to have the interval set to 6 hours in
> > the final version (thus creating a FIFO buffer for the
> > latest 6 hours of inserted data; so the FIFO will keep
> > approx. 10.000 rows spanning 160-200 GB data).
> >
>
> That's not the way to keep a 6 hour rolling buffer ... what you need to do
> is run the delete frequently, with *interval '6 hours'* in the SQL acting
> as the cutoff.

In fact the delete was run frequently to cut everything
older than 6 hours *immediately*.

> If you really do want to drop the entire table contents before refilling it,
> do a *DROP TABLE* and recreate it.

No, I do not want to drop the whole table.

> > * This deletion SQL command was simply repeatedly executed
> > by pgAdmin while my app kept adding the 16 MB rows.
> >
>
> Are you sure you are timing the delete, and not pgAdmin re-populating some
> kind of buffer?

Quite sure, yes. Because I launched just the delete command
in pgAdmin while the rest was executed by my application
outside pgAdmin, of course.

> > * Autovacuum is on; I believe I need to keep it on,
> > otherwise I do not free the disk space, right? If I switch
> > it off, the deletion time reduces from the average 10s down
> > to 4s.
> >
>
> You may be running autovaccum too aggressively, it may be interfering with
> I/O to the tables.

Hm, so would should I change then? I wonder if it helps to
run autovacuum less aggressive if there will not be a
situation were the whole process is stopped for a while. But
I'd like to understand what to change here.

> 8.4 has a lot of performance improvements. It's definitely worth a shot. I'd
> also consider switching to another OS where you can use a 64-bit version of
> PG and a much bigger buffer cache.

O.k., I'll give it a try.

Thank You.
Felix