From: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
---|---|
To: | Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in> |
Cc: | Postgresql General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: 7.4? |
Date: | 2003-02-27 16:22:57 |
Message-ID: | Pine.LNX.4.33.0302270921370.18487-100000@css120.ihs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-02-27 16:28:13 | Re: SETOF (was: Function example returning more then 1 |
Previous Message | Stephan Szabo | 2003-02-27 16:21:46 | Re: index usage (and foreign keys/triggers) |