Re: Full vacuuming of BIG tables takes too long

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: "Eugene M(dot) Zheganin" <emz(at)norma(dot)perm(dot)ru>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Full vacuuming of BIG tables takes too long
Date: 2003-05-22 14:01:58
Message-ID: 1053612118.2206.323.camel@camel
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

On Thu, 2003-05-22 at 08:37, Eugene M. Zheganin wrote:

My first suggestion would be to check your free space map settings in
the postgresql.conf and make sure they are set high enough. Setting
them to low can cause Vacuum Full to take longer than necessary.

> Thursday, May 22, 2003, 5:45:26 PM, you wrote:
> TM> 2) In the alternative dump/recreate/restore, do you recreate the Foreign Key
> TM> ? This step takes long time (depending of your Database schema). I have try
> TM> this scenario :
> TM> Dump data / Drop Foreign Key / Truuncate Tables / restore / Recreate the
> TM> Foreign Key
> TM> The step Recreate FK takes 2 times the four first steps.
> I don't use foreign keys. Only primary keys. There is only 3 tables in
> that db. 99% of space is taken by one table.
>

Is it possible for you do Begin; Create Table t2 As Select * From t1;
Drop Table t1 ; Alter table t2 Rename To t1; Commit;

Note you might want to lock t1 from writers, but people could still
select from it while you making the switch.

Robert Treat

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Josh Goldberg 2003-05-22 14:04:46 Re: user defined function call problem after upgrade 7.2.3 -> 7.3.2
Previous Message boggss 2003-05-22 13:29:59 system variables