Re: Primary Key Index Bloat?

On Sun, Nov 17, 2013 at 10:48 PM, David Johnston <polobo@yahoo.com> wrote:
> I am preparing to REINDEX the unique index and DROP the non-unique one over
> the same field - probably Tuesday evening.  Does everything I am saying here
> sound kosher or would someone like me to provide additional information?

[...]

> I have mostly left VACUUMing to auto-vacuum though the occasional manual one
> has been performed.  Never performed VACUUM FULL.

You can try pgcompact from PgToolkit [1]

pgcompact  -d dbname -t tablename -r -v info

that will concurrently reindex those indexes that are bloated and will
remove bloat from the table without recreating it in the case if it is
bloated only. It might require pgstattuple extension [2] to be
installed [3] .

[1] https://github.com/grayhemp/pgtoolkit
[2] http://www.postgresql.org/docs/9.0/static/pgstattuple.html
[3] http://www.postgresql.org/docs/9.0/static/contrib.html

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com