What is ROW EXCLUSIVE in PostgreSQL exactly?

12
votes

I understand that PostgreSQL inserts use ROW EXCLUSIVE locks, does that mean that inserts can be in parallel, and that one insert won't lock up the entire table?

Table in question has a primary key generated outside of DB and no additional indexes (but I'm curious what would happen if that wasn't the case).

Edit 1:

Per documentation, ROW EXCLUSIVE conflicts with SHARE which is acquired by CREATE INDEX.

Does this mean that if the table has index, insert will lock up the entire table?

Or will the table be locked only when creating the index first time?

Also, as I understand now, primary key is also an index, right?

1
postgresqlinsertlocking
The main thing that ROW EXCLUSIVE is is badly named, as the docs explain. As for the lock taken for CREATE INDEX ... that's for the CREATE INDEX command, not for the work done to update an index as part of an INSERT or UPDATE command. You can have as many non-unique indexes as you like and still get concurrent inserts - though each added index requires more work to be done for each insert and will slow every insert down a bit. - Craig Ringer
So, according to conflict table in the docs, ROW EXCLUSIVE does not conflict with ROW EXCLUSIVE, which means the inserts should be concurrent, regardless of how many indexes you have (provided you're not inserting unique conflicting keys). Did I understood correctly? - omittones
Yes, you understood correctly. - Craig Ringer

1 Answers

14
votes

Concurrent inserts should not block each other, unless they are inserting conflicting keys into a unique index, in which case the second insert will wait for the transaction containing the first to be committed or rolled back, and then either abort or proceed. A primary key is implemented as a unique index.

Non-unique indexes should not cause additional lock conflicts. Creating an index will block inserts and updates to the table, although you can just add concurrently to the command to avoid this, for some speed penalty.