Postgres alter index vs drop index and create index

Question

I have to write a migration command to remove a column from the index. Currently let us say I have table1 that has index on col1 and col2

I want to remove col1 from the index. I am looking at https://www.postgresql.org/docs/9.4/static/sql-alterindex.html but it does not seem I can actually just remove a column?

If yes, will it be better to remove the column and how VS

• Create new Index
• Drop the old index

Also, I want to do the reverse if I need to do downgrade. So just wondering how to achieve this

Answer 1

The ability to alter an index doesn't exist because in order to do so you would have to destroy and recreate the index with the new columns. By default, Postgres uses B-Trees to create indices and removing a column causes that B-Tree to become invalid. As a result the B-Tree needs to be built from scratch.

If you want some more details on how indices work under the hood, this is a good article: Postgres Indices Under the Hood

Answer 2

You’re right, you’ll have to create a new index with a single column and then drop an old index with two columns.