Re: 7.4?

On Thu, 27 Feb 2003, Shridhar Daithankar wrote:

> On 24 Feb 2003 at 15:53, Ericson Smith wrote:
> > Currently our database is at about 15gigs. Over the period of a month,
> > it grows to about 25gigs (10Gb wasted space). Every month we have a
> > cleanup routine which involves copying the most actively updated tables
> > to text, and importing the data again. We vacuum every night and analyze
> > 4 times per day, but we're loath to do a VACUUM FULL because of the
> > table locking issues (locking some of the tables would break the
> > operation of some of our 24/7 systems), hence we prefer to stop the db
> > about once per month, eat the downtime as scheduled (about 1.5 hours),
> > and get back to business for the next 30 days again.
> 
> We ahd a discussion on this few days back and the solution might work as well 
> for you(apart from suggestions you have already received).
> 
> Instead of vacuum full on a table, backup the table to a dump file, drop it and 
> recreate it. It takes more efforts than simple vacuum full but may run much 
> faster if you have large amount of space to recover.

Another option is to do something like:

begin;
select * into temp_table from bigtable;
delete from big_table;
insert into big_table (select * from temp_table);
commit;

This way your table is online while you're doing this, and can still be 
selected by various clients without interruption.