Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX - Mailing list pgsql-hackers

On Sun, Jun 8, 2025 at 9:37 PM David Rowley <dgrowleyml@gmail.com> wrote:
> On Mon, 9 Jun 2025 at 06:53, Robert Treat <rob@xzilla.net> wrote:
> > On Sat, Jun 7, 2025 at 9:17 PM David Rowley <dgrowleyml@gmail.com> wrote:
> > > 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.
>
> I agree that it doesn't seem ideal. I feel like if we're adding a
> feature that we have to list a bunch of caveats in the documentation,
> then we're doing something wrong. BTW, the ALTER INDEX will correctly
> invalidate cached plans and does not suffer from the same issue.
>

While the ALTER INDEX provides a simple way to do cache invalidation,
for practical application you still have most of the same issues and
need to jump through many of the same guc hoops with
force_invisible_index, which is a large part of why this is such a red
herring.

> My thoughts on this are that extensions are a better place to keep
> solutions that work most of the time. Once you start committing quirky
> things to Postgres, you sentence yourself to answering the same
> question for possibly a few decades in the -bugs or -general mailing
> list. I do my best to avoid that and feel we have enough of that
> already, so I'm -1 on the GUC solution for this. I know there are a
> few other people that are for it, so feel free to listen to them
> instead.
>

I hear you wrt explaining quirky things to users; you wouldn't believe
the level of confusion I got when I started explaining
"plan_cache_mode" to users when v12 rolled out. I'd guess the vast
majority of users have still never heard of this guc and have no idea
that Postgres behaves like this, which is another reason why I'd
rather not optimize for a very small segment of the user base at the
expense of a much larger set of users.

And to be clear, this isn't a case of a GUC solution vs an ALTER
solution. There is a reason that the proposed ALTER solution contains
a GUC as well, and why Oracle had to make use of a session flag in
their implementation. You are going to have a guc either way, which
means you are going to have to explain a bunch of these different
caveats in BOTH solutions. It's just that in one of the solutions, you
are further entangling the usage with DDL changes (and the additional
caveats that come with that).


Robert Treat
https://xzilla.net