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$8f01010a@iptel.com.ar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
Regards,
Fernando.
From | Date | Subject | |
---|---|---|---|
Next Message | Jonah H. Harris | 2008-08-25 14:20:37 | Re: Identifying the nature of blocking I/O |
Previous Message | RW | 2008-08-25 08:32:24 | Re: Identifying the nature of blocking I/O |