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

Re: Big delete on big table... now what?

From: "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar>
To: "'Gregory Stark'" <stark(at)enterprisedb(dot)com>,"'Bill Moran'" <wmoran(at)collaborativefusion(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Big delete on big table... now what?
Date: 2008-08-25 13:38:08
Message-ID: 04cd01c906b7$cfa0bf10$ (view raw or whole thread)
Lists: pgsql-performance
> Gregory Stark <start(at)enterprisedb(dot)com> writes:
> "Bill Moran" <wmoran(at)collaborativefusion(dot)com> writes:
> > "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)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.

Yes, I can afford a downtime on Sunday.
Actually the clustering option would help since most of our slow queries use
the same index.

Thanks Bill and Gregory for the advice.

In response to

pgsql-performance by date

Next:From: Jonah H. HarrisDate: 2008-08-25 14:20:37
Subject: Re: Identifying the nature of blocking I/O
Previous:From: RWDate: 2008-08-25 08:32:24
Subject: Re: Identifying the nature of blocking I/O

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