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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With