pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oleg Bartunov <oleg@sai.msu.su>
To: Lee Wu <Lwu@mxlogic.com>
Cc: Gaetano Mendola <mendola@bigfoot.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Vacuum full on a big table
Date: Fri, 18 Mar 2005 01:00:12 +0300 (MSK)
Message-ID: <Pine.GSO.4.62.0503180055480.5508@ra.sai.msu.su> (raw)
In-Reply-To: <ECAB83AA52BCC043A0E24BBC00001024D2E6A2@mxhq-exch.corp.mxlogic.com>
References: <ECAB83AA52BCC043A0E24BBC00001024D2E6A2@mxhq-exch.corp.mxlogic.com>

On Thu, 17 Mar 2005, Lee Wu wrote:

> I wish.
>
> I am having a table, size of more than 60G, with 2.04412e+08 rows.
> Vacuum full and reindex it just kill me.

In my case ( I have more than 500,000,000 rows) I had to 
'select * into new_big_table from big_table'
it was faster and didn't kill server.
As a bonus, you could 'CLUSTER' your big table if add
'order by somekey';

After that, dont' forget to recreate indices and then you could 
drop big_table and 'alter table new_big_table rename to big_table'.


Oleg


>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Gaetano Mendola
> Sent: Thursday, March 17, 2005 2:10 PM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] Vacuum full on a big table
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi all,
> is there a way to vacuum full a table but working only
> a part of the table ? I have a table with 6 milion rows
> and vacuum full it will send out of line for hours my
> server, so I'll like to vacuum that table multiple times
> in order to not block that table for a long period.
>
> It will work decresing the FSM settings ?
>
>
> Regards
> Gaetano Mendola
>
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.5 (MingW32)
> Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
>
> iD8DBQFCOfIs7UpzwH2SGd4RAsXaAKDIgcNZLqsYULjnNVNhTktXvWmJTgCg4zcK
> V/gFNRTCu0y99HLbTtGm610=
> =0SF3
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83



view thread (8+ messages)  latest in thread

Message-ID: <Pine.GSO.4.62.0503180055480.5508@ra.sai.msu.su>
Permalink:  ../Pine.GSO.4.62.0503180055480.5508@ra.sai.msu.su/
Also on:    postgresql.org/message-id/Pine.GSO.4.62.0503180055480.5508@ra.sai.msu.su

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: oleg@sai.msu.su, Lwu@mxlogic.com, mendola@bigfoot.com
  Subject: Re: Vacuum full on a big table
  In-Reply-To: <Pine.GSO.4.62.0503180055480.5508@ra.sai.msu.su>
  References: <ECAB83AA52BCC043A0E24BBC00001024D2E6A2@mxhq-exch.corp.mxlogic.com> <Pine.GSO.4.62.0503180055480.5508@ra.sai.msu.su>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox