CODEWITHSUNDEEP
postgresqlindexing
user1657624
user1657624

Reputation: 233

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

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!

Upvotes: 4

Views: 1580

Answers (2)

Samuel
Samuel

Reputation: 17171

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.

Upvotes: 3

knitti
knitti

Reputation: 7033

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.

Upvotes: 4

Related Questions