Date: Fri, 22 Aug 2008 18:44:07 -0400
From: Bill Moran <wmoran@collaborativefusion.com>
To: "Fernando Hevia" <fhevia@ip-tel.com.ar>
Cc: pgsql-performance@postgresql.org
Subject: Re: Big delete on big table... now what?
Message-Id: <20080822184407.37155367.wmoran@collaborativefusion.com>
In-Reply-To: <042c01c90496$ca8725f0$8f01010a@iptel.com.ar>
References: <042c01c90496$ca8725f0$8f01010a@iptel.com.ar>
Organization: Collaborative Fusion Inc.
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

"Fernando Hevia" <fhevia@ip-tel.com.ar> wrote:
>
> Hi list.
> =20
> I have a table with over 30 million rows. Performance was dropping steadi=
ly
> so I moved old data not needed online to an historic table. Now the table
> has about 14 million rows. I don't need the disk space returned to the OS
> but I do need to improve performance. Will a plain vacuum do or is a vacu=
um
> full necessary?
> =BFWould a vacuum full improve performance at all?

If you can afford the downtime on that table, cluster would be best.

If not, do the normal vacuum and analyze.  This is unlikely to improve
the performance much (although it may shrink the table _some_) but
regular vacuum will keep performance from getting any worse.

You can also reindex pretty safely.  Any queries that run during the
reindex will just have to do so without the indexes.

Longer term, if you remove smaller groups of rows more frequently, you'll
probably be able to maintain performance and table bloat at a reasonable
level with normal vacuum.

--=20
Bill Moran
Collaborative Fusion Inc.

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023