Should indexes be removed before changing their type?

Question

PostgreSQL

Should indexes be removed before changing their type? Do the old ones stay? Or do I need to delete first, then create new ones?
I'm just working through Valentina Studio and it's not entirely clear what's going on under the hood.

Answer 1

The index does not have the ability to change the type. Neither the index type itself (btree, hash, gin, gist) nor the field types.
You must first create a new index, then delete the old one. In this case, the names of the indexes will have to differ. First delete, then build a new one - it is fraught with entertainment in production, when the old index is no longer there at the time of building a new index, and therefore previously fast queries eat 100% cpu and disk, turning the task of building a new index into something near impossible. On a dev machine, of course, it doesn't matter.

I'm just working through Valentina Studio and it's not entirely clear what's going on under the hood.

Make log_statement = all (or ddl) - and you will see in the database log what the application is doing. It can be useful not only with all kinds of gui, but also with overly "smart" orm.