Re: DROP INVALID INDEXES command

Hello,

On 2026-Jun-30, Roman Khapov wrote:

> Recently Kirill and I have faced several problems with
> pg_repack, that resulted in list of invalid indexes on our installations,
> and that required us to remove them ourselves.

I agree that this is unpleasant behavior.

> We thought that it might be useful to have a command in PG, that
> removes invalid indexes on specified table automatically, to reduce
> the chances of any human-related mistakes.

I think what we should really do, is implement a mechanism to drop those
invalid indexes or unlink any unreferenced files, whenever a command
fails or the system crashes while running it. We had a POC patch in
that rough direction (see [1]) -- though that was never^W^W has not yet
been completed. My point is that I'm not sure we want to introduce new
DDL syntax for something that would become obsolete in the medium term,
if that other patch ever gets completed.

[1] https://www.postgr.es/m/CAEepm%3D0ULqYgM2aFeOnrx6YrtBg3xUdxALoyCG%2BXpssKqmezug%40mail.gmail.com

I'm not saying you should go and finish that patch, because I've been
told that that project is insanely complicated. But I'm not saying you
shouldn't, either.

On the other hand, I wouldn't be opposed to having the feature you want,
using a straight function as user interface (SELECT
pg_drop_invalid_indexes()).

Are you sure this works reasonably when invoked concurrently with CREATE
INDEX CONCURRENTLY or other concurrent DDL commands? I think the patch
should include some tests. (Probably the behavior we want is that the
code simply ignores any indexes it cannot obtain an AEL on.)

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Debido a que la velocidad de la luz es mucho mayor que la del sonido,
algunas personas nos parecen brillantes un minuto antes
de escuchar las pelotudeces que dicen." (Roberto Fontanarrosa)