From: Gregory Stark <stark@enterprisedb.com>
To: Bill Moran <wmoran@collaborativefusion.com>
Cc: Fernando Hevia <fhevia@ip-tel.com.ar>
Cc: pgsql-performance@postgresql.org
Subject: Re: Big delete on big table... now what?
Date: Sat, 23 Aug 2008 01:39:56 +0100
Message-ID: <87r68gpns3.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <20080822184407.37155367.wmoran@collaborativefusion.com>
References: <042c01c90496$ca8725f0$8f01010a@iptel.com.ar>
<20080822184407.37155367.wmoran@collaborativefusion.com>
"Bill Moran" <wmoran@collaborativefusion.com> writes:
> "Fernando Hevia" <fhevia@ip-tel.com.ar> wrote:
>>
>> Hi list.
>>
>> I have a table with over 30 million rows. Performance was dropping steadily
>> 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 vacuum
>> full necessary?
>> ¿Would 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.
Note that CLUSTER requires enough space to store the new and the old copies of
the table simultaneously. That's the main reason for VACUUM FULL to still
exist.
There is also the option of doing something like (assuming id is already an
integer -- ie this doesn't actually change the data):
ALTER TABLE x ALTER id TYPE integer USING id;
which will rewrite the whole table. This is effectively the same as CLUSTER
except it doesn't order the table according to an index. It will still require
enough space to hold two copies of the table but it will be significantly
faster.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stark@enterprisedb.com, wmoran@collaborativefusion.com, fhevia@ip-tel.com.ar
Subject: Re: Big delete on big table... now what?
In-Reply-To: <87r68gpns3.fsf@oxford.xeocode.com>
* 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