Re: autovacuum for large periodic deletes

Looks like partitioning may seem the way to go. I have been hesitant
about using partitioning as the feature is very new. How do I manage the
free space map if I do not use partitioning?

Thanks for your inputs

Sriram

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Chris Browne
Sent: Wednesday, May 17, 2006 2:48 PM
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] autovacuum for large periodic deletes

sdandapani(at)counterpane(dot)com ("Sriram Dandapani") writes:
> About 5-10 million rows stay after deletion. There are a few other
> tables where the daily deletion totals about 3-6 million.

It would appear there is something fairly not-sane about the process,
then. You delete about 90% of the day's data from the table each day?
That's a *lot*, and you are quite likely to have trouble with this
table blowing out the Free Space Map as a result.

> Would a vacuum full/cluster affect other operations. These tables
> have a 24x7 high data insertion rate.

Yes, VACUUM FULL and CLUSTER would block other operations while they
run.

The real Right Answer probably involves having data flow into some
sort of "queue" table, created fresh each day, for that day's
activities, where, at the end of the day, all of the data either gets
purged or moved to the "final destination" table, so that a new table
can be created, the next day.
--
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/unix.html
CBS News report on Fort Worth tornado damage:
"Eight major downtown buildings were severely damaged and 1,000 homes
were damaged, with 95 uninhabitable. Gov. George W. Bush declared
Tarrant County a disaster area. Federal Emergency Management Agency
workers are expected to arrive sometime next week after required
paperwork is completed."

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend