From: Greg Williamson <gwilliamson39@yahoo.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Resolving Index Bloat
Date: Mon, 19 Nov 2012 15:40:41 -0800 (PST)
Message-ID: <1353368441.76758.YahooMailNeo@web125903.mail.ne1.yahoo.com> (raw)
In-Reply-To: <CBAC86BE623FDB4E8B6225471691724291E1BC19@EXCHMBX-ADL6-01.staff.internode.com.au>
References: <CBAC86BE623FDB4E8B6225471691724291E1BC19@EXCHMBX-ADL6-01.staff.internode.com.au>
Samuel --
>________________________________
> From: Samuel Stearns <SStearns@internode.com.au>
>To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.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
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: gwilliamson39@yahoo.com
Subject: Re: Resolving Index Bloat
In-Reply-To: <1353368441.76758.YahooMailNeo@web125903.mail.ne1.yahoo.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