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

Re: Query times change by orders of magnitude as DB ages

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: Richard Neill <rn214(at)cam(dot)ac(dot)uk>
Cc: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Query times change by orders of magnitude as DB ages
Date: 2009-11-25 12:18:53
Message-ID: alpine.DEB.2.00.0911251214530.684@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, 25 Nov 2009, Richard Neill wrote:
>> On Sun, 22 Nov 2009, Richard Neill wrote:
>>> Worse still, doing a cluster of most of the tables and vacuum full analyze
>> 
>> Why are you doing a vacuum full? That command is not meant to be used 
>> except in the most unusual of circumstances, as it causes bloat to indexes.
>
> We'd left it too long, and the DB was reaching 90% of disk space. I
> didn't realise that vacuum full was ever actively bad, only sometimes
> unneeded. I do now - thanks for the tip.

The problem is that vacuum full does a full compact of the table, but it 
has to update all the indexes as it goes. This makes it slow, and causes 
bloat to the indexes. There has been some discussion of removing the 
command or at least putting a big warning next to it.

> So, having managed to bloat the indexes in this way, what can I do to
> fix it? Will a regular vacuum do the job?

In fact, cluster is exactly the command you are looking for. It will drop 
the indexes, do a complete table rewrite (in the correct order), and then 
recreate all the indexes again.

In normal operation, a regular vacuum will keep the table under control, 
but if you actually want to shrink the database files in exceptional 
circumstances, then cluster is the tool for the job.

Matthew

-- 
 Matthew: That's one of things about Cambridge - all the roads keep changing
           names as you walk along them, like Hills Road in particular.
 Sagar:   Yes, Sidney Street is a bit like that too.
 Matthew: Sidney Street *is* Hills Road.

In response to

Responses

pgsql-performance by date

Next:From: Richard NeillDate: 2009-11-25 12:22:40
Subject: Re: Query times change by orders of magnitude as DB ages
Previous:From: Richard NeillDate: 2009-11-25 12:11:09
Subject: Re: Query times change by orders of magnitude as DB ages

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