Given a large table (say “users”) that receives many writes, which of the following SQL statements will not lock the table against writes for others [closed]

Question

Statements:

• 1. Vacuum analyze;
• 2. Vacuum full;
• 3. Create unique index concurrently on users ....
• 4. Create index on users .....
• 5. Insert into users....
• 6. Alter table users add constraint 'new_check_name' CHECK (....) not valid;
• 7. update users set column = $1;
• 8. update users set column_name = $2 where pk = $2;

Answer 1

Here are the answers for Postgres 13, with reference to the official documentation, I let you navigate to your own version of Postgres to see if there were changes:

• 1. will allow writes on the table (see "Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained." in the official documentation https://www.postgresql.org/docs/current/sql-vacuum.html)
• 2. won't allow writes on the table (see "VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an exclusive lock on each table while it is being processed." in the official documentation https://www.postgresql.org/docs/current/sql-vacuum.html)
• 3. will allow writes (see "CONCURRENTLY When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table" in the official documentation https://www.postgresql.org/docs/current/sql-createindex.html)
• 4. won't allow writes (see "Creating an index can interfere with regular operation of a database. Normally PostgreSQL locks the table to be indexed against writes and performs the entire index build with a single scan of the table." in the official documentation https://www.postgresql.org/docs/current/sql-createindex.html)
• 5. won't allow concurrent writes on the newly inserted rows until the transaction is over, but will allow other transactions to change other rows (see "UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress)." in the official documentation https://www.postgresql.org/docs/current/transaction-iso.html)
• 6. won't allow writes on the table (see "Although most forms of ADD table_constraint require an ACCESS EXCLUSIVE lock, ADD FOREIGN KEY requires only a SHARE ROW EXCLUSIVE lock." and the matrix that shows that ACCESS EXCLUSIVE lock is the highest level of lock in the official documentation https://www.postgresql.org/docs/9.6/sql-altertable.html and https://www.postgresql.org/docs/current/explicit-locking.html)
• 7. won't allow concurrent writes on the rows being updated until the end of the transaction (see "UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress)." in the official documentation https://www.postgresql.org/docs/current/transaction-iso.html)
• 8. see point 7