On Sat, Jun 7, 2025 at 9:17 PM David Rowley <dgrowle...@gmail.com> wrote: > On Sun, 8 Jun 2025 at 01:35, Robert Treat <r...@xzilla.net> wrote: > > On Fri, Jun 6, 2025 at 8:04 PM David Rowley <dgrowle...@gmail.com> wrote: > > > Can you list your proposed series of steps you'd recommend to a DBA > > > wishing to remove an index, assuming this feature exists in core as > > > you'd like it to? > > > > > > > Well, the series of steps differs depending on the nature of the > > system being managed. If you are running on a single node with normal > > traffic and resources, you just set the GUC to include the index you > > want to be invisible, wait for a few days (maybe no one runs monthly > > reports on this system?), take a quick look at your monitoring/stats > > to make sure things seem copacetic, and then you drop the index and > > reset the GUC. > > Thanks for explaining. > > What are your thoughts on cached plans? In this scenario, do you > assume that waiting a few days means that connections get reset and > prepared statements will have been replanned? Or do you think cached > plans don't matter in this scenario? >
Heh; I did say that the GUC model wasn't perfect, so good on you for getting right to one of the more wonky parts. In practice, I actually don't think it matters as much as one might think; IME there is a sort of inverse relationship were the more sensitive you are to production changes and/or running at high scale, the more likely you are going to want to slow deploy / ramp up these changes, and doing things like adding the GUC at the session level will likely require a connection recycle anyway. Also keeping invisible indexes in place for days or weeks is likely to be a common scenario, and again we don't normally expect connections, or cached plans, to stay alive for weeks at a time. Of course you can't dismiss this; you'd definitely have to document that if they are worried about queries with cached plans the best solution would be to recycle any connections that might have existed before setting the guc in place. That may not sound ideal, but I think in practice it is no worse than the practical effects of thinking that ANALYZE will help keep your queries fast; sure it keeps your statistics up to date, but if you are running cached plans for indefinite periods of time, you wouldn't actually pick those up those statistics changes*, which means cached plans are already susceptible to degrading over time, and we are expecting people to recycle connections regularly even if we don't say it very loud. * As an aside, I once looked into implementing some kind of pg_invalidate_cached_plans() function that would send a signal to all backend to dump their plans; kind of like a global DISCARD ALL, but it always seemed scarier than just recycling connections, so I gave up on it pretty quick; maybe some would find that useful though? Robert Treat https://xzilla.net
