![]() ![]() The next line in the logs reveals everything this is blocking: DETAIL: Processes holding the lock: 29386. As a result all the ACCESS SHARE locks from all select statements on the users table are blocked. As described above though locks are given in a first come, first served basis. You might not think this alone is cause for alarm perhaps the migration will just take a while. LOG: process 31025 still waiting for AccessExclusiveLock on relation 17966 of database The process waiting is the one running the migration, and relation 17966 corresponds to the users table. Looking through the database logs from the migration there are many entries of: Locks are handed out using a queue, so if processes A and B are trying to acquire locks on the same resource, and process A requests the lock first, it will grant the lock to A first even if process B is requesting a less restrictive lock. The problem comes in the way Postgres hands out locks. Since the new table we're adding is completely empty, the whole process is very quick. Commit the transaction with the foreign key relationship.Validate the integrating of all user_id references in the new table.Acquire ACCESS EXCLUSIVE lock on the users table.The steps Postgres follows to add this constraint are: The only lock that ACCESS SHARE conflicts with is the ACCESS EXCLUSIVE lock. SELECT statements happen to take out an ACCESS SHARE lock on the table, which is the least restrictive lock in Postgres. So it blocks everything including SELECT statements on the table. Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement. ![]() The ACCESS EXCLUSIVE lock in Postgres is the most restrictive type of lock you can acquire. By default in Postgres, adding a foreign key constraint on a table requires an ACCESS EXCLUSIVE lock on the table the key is referencing. The problem is adding the foreign key constraint on the new user reference. Here's a simple version of it: Rails Version: However, a simple migration we ran a couple weeks ago brought the site down for an entire minute. Don't create columns with default values in large tables.Don't create NOT NULL columns in one operation for large tables.For this to be successful and not negatively impact performance during peak usage hours, we follow some simple rules for running migrations in production. Postgres is our primary data store, and since many migration operations are very cheap with Postgres we usually bundle database migration alongside our deploys. Here at Handshake, we deploy our site multiple times a day. Editor’s note: This article was originally published in February 2017, and was re-posted in February 2021.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |