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.
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.
Hope that helps!
On Thu, Nov 7, 2019 at 2:33 PM Alex Balashov <abalashov@xxxxxxxxxxxxxxx> 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/
