Re: Resolving Index Bloat

Samuel --

>________________________________
> From: Samuel Stearns <SStearns(at)internode(dot)com(dot)au>
>To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
>Sent: Monday, November 19, 2012 1:59 PM
>Subject: [ADMIN] Resolving Index Bloat
>
>
><...>
>
>Any ideas on how to resolve?
> 

I have custody of one database that is fairly small but subjected to lots of updated, inserts and deletes, and that database shows bloat that we have to knock down hourly or performance tanks.

This is on postgres 9.1 so I am not sure how much translates to earlier versions.

We ran ""REINDEX" hourly for a while; we currently use a hand rolled script that reindexes each regular index and does a dance to reindex primary keys and constraints. I could email it to you but as I said, not sure how much would work.

The straight REINDEX did work 99% of the time, with very occasional reports of failures, never repeated. The hand rolled version is a bit safer and a little less intrusive.

HTH,

Greg Williamson