1

As I know CONCURRENTLY let DATABASE create an index without any lock though It could incur more CPU usage.

I am planning to create an index in production DATABASE with the option.

Before applying that I want to check for sure.

Is it safe enough to use in production DATABASE regardless of how many rows there and how many INSERT, UPDATE is executed while creating the index?

1
  • Yes, it's safe to be used in production. Otherwise the Postgres development team wouldn't have released it. Oct 25 '19 at 5:58
1

Yes, that is safe, meaning that it cannot damage your database and that the index will be correct once the statement succeeds.

Creating an index uses I/O and CPU resources, that is unavoidable.

If CREATE INDEX CONCURRENTLY fails, it will leave behind an invalid index that you should drop, since it uses space.

CREATE INDEX CONCURRENTLY will require an ACCESS EXCLUSIVE lock on the table, but differently from a normal CREATE INDEX which locks the table for the whole duration of the operation, it takes only a very short lock at the very end. This is normally not a problem, but it can be a problem if you have long running transactions that use the table: CREATE INDEX CONCURRENTLY will have to queue behind these transactions, and all later statements will have to queue behind it.

So make sure you have no long running transactions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.