1

How robust is Postgres when it comes to running multiple commands on a table at the same time?

Can I for instance, set a primary key and create a couple of indexes using multiple separate commands at the same time?

How about a query that will only affect/change data in one column? Is it ok to run other commands as long as they don't interfere with the column affected by the first command?

2 Answers 2

5

You can't run multiple DDL statements at the same time because each statement will request an exclusive lock. So if you add a primary or foreign key, the table is locked.

You can however create multiple indexes at the same time using the concurrently option.

Without the concurrently this will also block write access to the table while the index is created (read access is still possible in any case).

1

@a_horse_with_no_name gave you some explanation about creating index.

But what about modifying data in the table itself? You can modify your data concurrently by default. The main question is what result you'll get. The answer really depends on your transaction isolation level. You can read more about that here.

Also you can prevent concurrent writes by setting an appropriate lock on the table/row before writing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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