On Thu, Jul 17, 2025 at 9:49 PM David Rowley <dgrowle...@gmail.com> wrote: > > On Wed, 16 Jul 2025 at 05:59, Robert Treat <r...@xzilla.net> wrote: > > operational risk within the system. Similarly, the nature of the DDL > > change also requires that all sessions be impacted everywhere at once; > > there is no way to slowly roll the change to some segment of the > > database or some specific workload within the system. > > IMO, sounds like your method for safety here is to slowly walk your > bull into the china shop. Wouldn't it be much better to learn where or > confirm the index isn't being used before you go turning it off for > various queries? I'm stumped at why your method for removing the index > amounts to closing your eyes and doing your best to narrow the blast > radius of the trial and error method. > > > regards to roll out specifically, the ALTER method is no safer than > > drop index because both use DDL which means they are both open to > > blocking or being blocked by conflicting queries, which increase > > Aside from not having to recreate the index, I agree with this part. > It's a genuine concern. If some query switches to a Seq Scan and the > queries to that table start taking a week to execute, then it'll be a > long wait before you can get an AccessExclusiveLock on the table > again. I think our mental models for this differ, however. In my > imagination, I've checked that the index is unused before I disable > it. It seems like in your model, you're going to disable it and > measure the yield of the resulting explosion. >
The whole premise of this feature is that there is no way to have certainty that an index is truly unused. I can assure you that I've done just as much due diligence as you have (perhaps more) to determine that the index is unused, but if that were enough to ensure safety, we wouldn't need invisible indexes in the first place; we could just drop the index. Once we admit that neither of us have operational safety, the question becomes just how close can we get people to certainty, and how can we most limit the fallout from being wrong. In my world, I'd never have to worry about a plan flipping to sequential scan causing a query to hold locks for a week because the server will have certainly crashed within minutes from the overwhelming level of traffic said sequential scan brings, so if the solution requires heavy locks and global application (like alter would), it will still be extremely risky in heavy production workloads. In short, we're both walking a bull through the china shop, but it would seem mine is much more temperamental than yours. Robert Treat https://xzilla.net
