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

BShaps · Accepted Answer · 2018-07-27 22:22:16Z

9

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

answered Jul 27, 2018 at 22:22

BShaps

1,4248 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user9503053 Over a year ago

is this still the case? I see there is an ALTER INDEX: postgresql.org/docs/13/sql-alterindex.html

Linas Valiukas · Accepted Answer · 2018-07-27 19:44:36Z

3

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

answered Jul 27, 2018 at 19:44

Linas Valiukas

1,4061 gold badge15 silver badges23 bronze badges

Collectives™ on Stack Overflow

Postgres alter index vs drop index and create index

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related