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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
