Thread

Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX

Robert Treat <rob@xzilla.net> — 2025-06-08T18:53:20Z

On Sat, Jun 7, 2025 at 9:17 PM David Rowley <dgrowleyml@gmail.com> wrote:
> On Sun, 8 Jun 2025 at 01:35, Robert Treat <rob@xzilla.net> wrote:
> > On Fri, Jun 6, 2025 at 8:04 PM David Rowley <dgrowleyml@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