Is there a way to add index to a postgres table without locking up the db?

Question

I have a postgres table with 3 columns that has 4 million rows and growing. When I created the table I intended to create a btree index on one of the columns, but I forgot to do so. I can use the CREATE INDEX command to create the index now, but I think that will lock up the db. Since I have data coming in every second, I would like to avoid that. I noticed that there is an option called CONCURRENTLY. Is that what I should use? Does it have any other effects that I should be aware of?

Thanks!

knitti · Accepted Answer · 2015-06-02 14:01:19Z

4

Yes you can use CREATE INDEX CONCURRENTLY idx_xyz ON sometable(key_a, key_b);

However caveats apply: you have to look yourself, whether it succeded, and possibly re-try.

answered Jun 2, 2015 at 14:01

knitti

7,03335 silver badges43 bronze badges

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

Comments

Samuel · Accepted Answer · 2015-06-02 14:01:56Z

3

Yes CONCURRENTLY will allow your table to remain in use while the index is building. The performance implications are difficult to predict and will vary based on the load on the table and how big the index is.

Whenever we had to add an index to an always-on production database we chose to schedule downtime with our clients instead of risking using the online indexing feature because it's less predictable and could cause an outage for a different reason.

answered Jun 2, 2015 at 14:01

Samuel

17.2k6 gold badges65 silver badges77 bronze badges

Collectives™ on Stack Overflow

Is there a way to add index to a postgres table without locking up the db?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related