Now, with the proper foresight, this situation can be avoided, but it requires that a developer be mindful of the type of lock any given query will cause, and for how long. As an example, let's create a similar situation. Let's say that we have the following table called people:
create table people ( id serial primary key, first_name text, last_name text );And we want to create a guid column, because in the future we want to start moving away from using a sequence for our ids and instead move to using guids. To do this, it would seem to be straightforward to write this migration by building a simple alter table query, like this:
alter table people add column if not exists guid varchar(50) default uuid_generate_v4() not null;It seems simple enough. What could go wrong? A lot, as it turns out. And the more data you have in your people table, and the more that data is used, the worse the problems with this query become. To better understand this problem, we need to dive in to the Postgres documentation a bit. In particular we need to understand Table-Level Lock Modes. We can find documentation on this at https://www.postgresql.org/docs/12/explicit-locking.html.
According to the documentation, there are eight different table-level locks, moving from least restrictive to most restrictive in this order: ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE. An alter table query can have one of three different locks: SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, or ACCESS EXCLUSIVE. Now can potentially have one of multiple different locks, it's always best to assume it's the most restrictive lock unless you've explicitly looked at the documentation and know that it is otherwise, and that rule would hold up in the case of the query above, because it does acquire an ACCESS EXCLUSIVE lock.
Now the important thing to understand about locks is what other lock types they conflict with. This in turn tells you what kind of queries will be locked out from accessing the table while the given query is running. In the case of ACCESS EXCLUSIVE, it conflicts with all other locks. Now this in and of itself isn't bad. There are a lot of important things that you wouldn't be able to reliably do for your database without the use of this kind of lock. Where it becomes bad is when a query that needs an ACCESS EXCLUSIVE lock is also a long running query, with the biggest issue caused by this being that it will block all of your standard CRUD operations, which includes all select, insert, update, or delete queries that need access to the table that has been locked.
So how does this apply to the example above? Well, let's find out. In order to test this, we'll want to have a large amount of data in our table. So after creating our table by running this:
create table people ( id serial primary key, first_name text, last_name text );We can put a little bit of data into the table with the following query:
insert into people (first_name, last_name) values
('John', 'Doe'),
('Jane', 'Doe'),
('Bob', 'Smith'),
('Jill', 'Hill'),
('Jack', 'Hill');
And then we can turn that into a lot of data by running this query a number of times:insert into people (first_name, last_name) select first_name, last_name from people;Which essentially takes all the data in the table, copies it, and reinserts into the table, effectively doubling the size of the table each time it is run. By running it 20 times, your table will have over 5 million users, which should be enough to give us a clear idea of what's going on. At this point if I run the alter table query from above, it'll take 1 m 6 s 455 ms to complete (Note that your times will likely vary). So that is over a minute where the table is locked with an ACCESS EXCLUSIVE lock, meaning that no other queries can be run against it. This is a problem. Ideally any query that needs an ACCESS EXCLUSIVE lock should be running in the millisecond range, or at worst upwards of a couple of seconds. That's definitely not the case here. So let's figure out how to fix it. Let's start by reverting our table to its pre migration state by running the following query:
alter table people drop column guid;Now, the part of the query that is taking so long to run is the part that is trying to generate a new guid for all of the 5 million plus rows that already exist in our database. There isn't really a reason why we need to generate all of those guids as part of the alter table query, and that part of the migration could ultimately be moved out into an update query, which according to the documentation only needs a ROW EXCLUSIVE lock, which is much more permissive and will allow other select, insert, update, and delete queries to run while it is runnning.
So let's start out with modifying our alter table query to remove the default value for the time being. Note that by removing the default value we are also required to remove the not null constraint.
alter table people add column if not exists guid varchar(50);This pared down query runs in 14 ms and will give you a resulting table that looks something like this:
| id | first_name | last_name | guid |
|---|---|---|---|
| 1 | 'John' | 'Doe' | NULL |
| 2 | 'Jane' | 'Doe' | NULL |
| 3 | 'Bob' | 'Smith' | NULL |
| 4 | 'Jill' | 'Hill' | NULL |
| 5 | 'Jack' | 'Hill' | NULL |
| ... | ... | ... | ... |
At this point, we can add on our default value.
alter table people alter column guid set default uuid_generate_v4();This query runs in 4 ms, which is good, because this query also requires an ACCESS EXCLUSIVE lock. At this point our total ACCESS EXCLUSIVE lock time is 18 ms. The reason why this query is so quick is because it doesn't set the guid value for the 5 million plus existing rows, but instead ensures that newly inserted rows will have a guid. So for instance if at this point I ran the query:
insert into people (first_name, last_name) values
('Susie', 'Queue');
I'd end up with a table that looks something like this:| id | first_name | last_name | guid |
|---|---|---|---|
| 1 | 'John' | 'Doe' | NULL |
| 2 | 'Jane' | 'Doe' | NULL |
| 3 | 'Bob' | 'Smith' | NULL |
| 4 | 'Jill' | 'Hill' | NULL |
| 5 | 'Jack' | 'Hill' | NULL |
| ... | ... | ... | ... |
| 5242881 | 'Susie' | 'Queue' | '60f029c8-3818-43ba-bb0a-9a483a4c5529' |
Now we can run an update query to add guids to all existing rows:
update people set guid = uuid_generate_v4() where guid is null;Note the conditional 'where guid is null'. This prevents us from overriding the guid values of any newly inserted rows, such as the Susie Queue row in the example above. Running this query takes 1 m 46 s 636 ms. Note that this takes longer than our original alter table query, but that's ok, because this is 1 m 46 s 636 ms of Row EXCLUSIVE lock time, which is a much more permissive lock that will still allow for select, insert, update, and delete queries to run. So at this point we have 18 ms of ACCESS EXCLUSIVE lock time, and 1 m 46 s 636 ms of ROW EXCLUSIVE lock time, with a table that looks like this:
| id | first_name | last_name | guid |
|---|---|---|---|
| 1 | 'John' | 'Doe' | 'cf8cd62a-5849-491a-a0b6-fd96dddb9562' |
| 2 | 'Jane' | 'Doe' | 'ea9722ae-f4af-4d52-bea9-fd38691d37a7' |
| 3 | 'Bob' | 'Smith' | 'a75d722c-217d-4a02-bb94-f4246fcf1a5c' |
| 4 | 'Jill' | 'Hill' | 'ab09e9ca-75b9-4bf1-a3cf-b34cc8fe6965' |
| 5 | 'Jack' | 'Hill' | '14c31156-c57e-4250-93d1-110ba78d2b5d' |
| ... | ... | ... | ... |
| 5242881 | 'Susie' | 'Queue' | '60f029c8-3818-43ba-bb0a-9a483a4c5529' |
But we're still not finished yet. We still need to add on our not null constraint. We can do this with the following query:
alter table people alter column guid set not null;This query took 6 s 290 ms to run, and requires an ACCESS EXCLUSIVE lock. This would bring our total ACCESS EXCLUSIVE lock time up to 6 s 308 ms. In many situations this is probably acceptable, and you could stop here. But with that being said, 6 seconds is still a long time to lock down all access to a table, especially if that table is critical to your application. So can we improve this and get the time down further? We can, but it requires diving deeper into the alter table documentation, which can be found at https://www.postgresql.org/docs/12/sql-altertable.html.
In the documentation for set not null, it says: "SET NOT NULL may only be applied to a column providing none of the records in the table contain a NULL value for the column. Ordinarily this is checked during the ALTER TABLE by scanning the entire table". This gives us the explanation of why the query takes so long. After it locks the table, it has to go through every single row and verify that there are no nulls before it can apply the constraint. Why does it need to do this? Simple, it's because while at this point you and I know that there are no nulls in any of the rows, Postgres has no way of knowing this. This is because at this current point it would still be completely valid to run a query such as:
insert into people (first_name, last_name, guid) values
('Joe', 'Johnson', null);
Or like:update people set guid = null where id = 1While you and I know that no such query has been run, Postgres has no way of knowing that for certain, hence the need to check every single row to verify that there are no nulls. But in the very next line of the documentation it gives us a way in which this very expensive scan can be skipped. It says: "however, if a valid CHECK constraint is found which proves no NULL can exist, then the table scan is skipped." So at this point we'll want to take a closer look at the documentation for the check constraint.
As we look over the documentation for the check constraint, we know that it will also require an ACCESS EXCLUSIVE lock, because there is no explicit documentation stating otherwise, and at the beginning of the alter table documentation it states that "[a]n ACCESS EXCLUSIVE lock is held unless explicitly noted." So with that in mind, let's take a closer look at what the documentation has to say about how the check constraint works. It states: "Normally, this form will cause a scan of the table to verify that all existing rows in the table satisfy the new constraint. But if the NOT VALID option is used, this potentially-lengthy scan is skipped." Furthermore, it states that: "The constraint will still be enforced against subsequent inserts or updates (that is [...] they'll fail unless the new row matches the specified check condition). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option."
From this we can assume that adding a check constraint will result in the same lengthy ACCESS EXCLUSIVE lock time as the not null constraint from before, unless we use the NOT VALID option. By doing so, the constraint will only be enforced for new changes to the data, and not any of the current data. This constraint can then be validated for the rest of the existing data later via the use of the VALIDATE CONSTRAINT option. So with this in mind, let's go take a closer look at the VALIDATE CONSTRAINT documentation.
The VALIDATE CONSTRAINT section points us to the notes section, and the notes section tells us the following: "[A] VALIDATE CONSTRAINT command can be issued to verify that existing rows satisfy the constraint. The validation step does not need to lock out concurrent updates, since it knows that other transactions will be enforcing the constraint for rows that they insert or update; only pre-existing rows need to be checked. Hence, validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered."
Going back over to the documentation on locks, we can that the SHARE UPDATE EXCLUSIVE lock only conflicts with locks at the SHARE UPDATE EXCLUSIVE level or above. This means that it won't prevent our select, update, insert, or delete queries. As such, we should be able to add a check constraint with the NOT VALID option, then validate that constraint, followed by adding the not null constraint, and then finally dropping the check constraint. Let's do that.
alter table people add constraint temp_null_check check ( guid is not null ) not valid;This query took 9 ms to run, and required an ACCESS EXCLUSIVE lock.
alter table people validate constraint temp_null_check;This query took 6 s 748 ms to run, and required a SHARE UPDATE EXCLUSIVE lock.
alter table people alter column guid set not null;This query took 4 ms to run, and required an ACCESS EXCLUSIVE lock.
alter table people drop constraint temp_null_check;And this final query took 4 ms to run, and required an ACCESS EXCLUSIVE lock.
At this point our entire migration from start to finish requires 35 ms of ACCESS EXCLUSIVE lock time, 6 s 748 ms of SHARE UPDATE EXCLUSIVE lock time, and 1 m 46 s 636 ms of ROW EXCLUSIVE lock time, resulting in a total migration time of 1 m 53 s 419 ms, of which time, only 35 ms caused CRUD queries to be blocked.
As an additional note, as part of a migration such as this, it's very likely that you'd also want to create an index on the newly created guid column, with the original migration looking something like this:
alter table people
add column if not exists guid varchar(50) default uuid_generate_v4() not null;
create index if not exists people_guid_index
on people using btree(guid);
This create index call is also problematic, in that it takes 25 s 751 ms to run on our example problem, and it requires a SHARE lock. A SHARE lock conflicts with a ROW EXCLUSIVE lock, so while this would allow select queries to be made, it would block insert, update, and delete queries. Luckily, this is easily fixed. Simply add the keyword concurrently, like so:
create index concurrently if not exists people_guid_index
on people using btree(guid);
When the keyword concurrently is added, it only requires a SHARE UPDATE EXCLUSIVE lock, which will in turn allow for insert, update, and delete queries to run. Note that adding concurrently will make the query take longer to run, with this taking 34 s 702 ms to run on our example, but again, this is preferred, because it doesn't block anything. So with that, that would bring our final migration to look something like this:
alter table people
add column if not exists guid varchar(50);
alter table people
alter column guid set default uuid_generate_v4();
update people set guid = uuid_generate_v4()
where guid is null;
alter table people
add constraint temp_null_check check ( guid is not null ) not valid;
alter table people
validate constraint temp_null_check;
alter table people
alter column guid set not null;
alter table people
drop constraint temp_null_check;
create index concurrently if not exists people_guid_index
on people using btree(guid);
With it requiring 35 ms of ACCESS EXCLUSIVE lock time, 41 s 450 ms of SHARE UPDATE EXCLUSIVE lock time, and 1 m 46 s 636 ms of ROW EXCLUSIVE lock time, with a total time of 2 m 28 s 121 ms, with only 35 ms of that blocking CRUD operations. And that is how you fix a migration to minimize lock times and keep everything running smoothly. The important lesson here is to always be aware of your lock times when writing migrations around heavily used and sensitive tables.
