From: Scott Marlowe <scott.marlowe@gmail.com>
To: Brian Modra <epailty@googlemail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Vacuum taking an age
Date: Thu, 3 Jan 2008 20:31:13 -0600
Message-ID: <dcc563d10801031831q36a81040l8a6d57615a441cd6@mail.gmail.com> (raw)
In-Reply-To: <5a9699850801030448k126a5028m85ddb2153e95184f@mail.gmail.com>
References: <5a9699850801030448k126a5028m85ddb2153e95184f@mail.gmail.com>
On Jan 3, 2008 6:48 AM, Brian Modra <epailty@googlemail.com> wrote:
> Hi,
>
>
> I have a pretty "live" table: rows being inserted and updated more
> than once 1 per second, though far, far more inserts than updates.
>
> There are currently over 3 million rows.
>
> It has not been vacuumed for months.
How many rows per second? 1? all of them? Kinda makes a difference.
If it was 1 a second updated for 3 months that's about 7million dead
rows. If it was all 3million, then that's 7million * 3million dead
rows, also know as a whole bunch of rows.
Either way, you probably have a table so terribly bloated that a
regular vacuum will not help you in terms of speeding it up. Regular
vacuums are like brushing your teeth three times a day. If you've
forgotten for three months, brushing them once isn't likely to fix all
the cavities you've got. Same thing here. You'll either need a
vacuum full or a cluster. Cluster is often faster. Or you can try
selecting everything into a temp table, truncating the real table, and
inserting the data back in. Truncation will remove all rows, dead or
otherwise. The advantage is that it's often faster to truncate /
reload than it is to vacuum full. If you have indexes, you might want
to drop them while re-inserting and then recreated them.
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-admin@postgresql.org
Cc: scott.marlowe@gmail.com, epailty@googlemail.com
Subject: Re: Vacuum taking an age
In-Reply-To: <dcc563d10801031831q36a81040l8a6d57615a441cd6@mail.gmail.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