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@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Chris Browne Sent: Wednesday, May 17, 2006 2:48 PM To: pgsql-admin@xxxxxxxxxxxxxx Subject: Re: [ADMIN] autovacuum for large periodic deletes sdandapani@xxxxxxxxxxxxxxx ("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
