Skip site navigation (1) Skip section navigation (2)

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 (view raw, whole thread or download thread mbox)
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


pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group