Re: Index degradation

This sounds like index bloat, and yes you can monitor for it. I've written
some blog posts both on detection and cleanup procedures as well as been
working on a tool to make monitoring for it easier.

https://www.keithf4.com/checking-for-postgresql-bloat/
https://www.keithf4.com/cleaning-up-postgresql-bloat/
https://github.com/keithf4/pg_bloat_check

Also, to help mitigate this problem in the future, you can try and tune
autovacuum to run on said tables so that space is marked as reusable on a
regular enough basis that new inserts/updates can make use of it more
efficiently. The following blog post talks about how to tune autovacuum for
wrap-around, but this is applicable to tuning it to avoid bloat as well.
Note this only really helps with gradual changes to tables. Sudden large
purging of a table with deletes, or mass updates, are going to leave more
empty space that autovacuum can really be tuned for. In such cases, you
should always follow up said operations with a manual vacuum and bloat
cleanup as needed.

https://www.keithf4.com/per-table-autovacuum-tuning/

Hope that helps!

On Thu, Nov 7, 2019 at 2:33 PM Alex Balashov <abalashov(at)evaristesys(dot)com>
wrote:

> Over a period of some years, particularly in tables whose contents
> periodically get swapped out entirely (i.e. lots of dead rows), indexes
> slowly degrade, pushing query latency up.
>
> Is there a way to detect this condition short of just monitoring
> statement latency, so as to know when to REINDEX / build new indexes?
>
> --
> Alex Balashov | Principal | Evariste Systems LLC
>
> Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free)
> Web: http://www.evaristesys.com/, http://www.csrpswitch.com/
>
>
>

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com