Watch a Migration Stop the Service

Goal

Migration incidents do not happen because the operation takes long. They happen because everything behind it lines up in a queue while it waits for a lock.

In this lab you create that queue yourself. It reproduces in a few seconds.

Preparation

create table big(id bigserial primary key, name text, n int);
insert into big(name,n) select 'row'||i, i from generate_series(1,300000) i;

Measuring time

psql -h 127.0.0.1 -U lab -d labdb
\timing on

Creating two sessions

psql -h 127.0.0.1 -U lab -d labdb \
  -c "begin; select count(*) from big; select pg_sleep(8);" &
sleep 1
# 여기서 두 번째 세션
wait

Steps

1. Time for adding a column → 01-addcolumn.txt
2. Lock mode → 02-lockmode.txt
3. The queue forms → 03-queue.txt
4. lock_timeout → 04-locktimeout.txt
5. The index blocks writes → 05-index.txt
6. CONCURRENTLY → 06-concurrently.txt
7. Find unusable indexes → 07-invalid.md
8. Wrap-up → 08-notes.md

Notes

Step 3 is the whole point of this lab. The rest are ways to avoid it.

Is adding a column with a default slow

Create a table with 300,000 rows, add a column with a default, and record the time it took in 01-addcolumn.txt.

create table big(id bigserial primary key, name text, n int);
insert into big(name,n) select 'row'||i, i from generate_series(1,300000) i;

You can get the time by turning on \timing on in psql. alter table big add column status text not null default 'new';

You should see milliseconds. Since PostgreSQL 11 the default is written only to the metadata — the advice that "it rewrites the table" is out of date.

But which lock does it take

Check the lock mode that ALTER TABLE takes and record it in 02-lockmode.txt.

Run the alter inside a transaction and look at pg_locks before committing.

begin;
alter table big add column tmp1 int;
select mode from pg_locks where relation='big'::regclass;
rollback;

You should see AccessExclusiveLock. It is the strongest lock, so it blocks even reads.

What comes behind it lines up in a queue

Run a long query → ALTER TABLE → a plain SELECT, in that order, and record in 03-queue.txt that even the last SELECT is blocked.

This is the core of the lab.

1. Run begin; select count(*) from big; select pg_sleep(8); in the background
2. After 1 second, run alter table big add column q1 int; in the background
3. After another second, run set lock_timeout='3s'; select count(*) from big;

Step 3 is a read that has nothing to do with the ALTER, yet it is blocked. This is because lock requests are handled in order.

It looks as if the entire service has stopped, but the DB metrics look fine — that is why finding the cause takes so long.

Block it with lock_timeout

In the same situation, set lock_timeout on the ALTER and record in 04-locktimeout.txt that only the ALTER fails and the queries behind it are not blocked.

set lock_timeout='2s'; alter table .... After 2 seconds the ALTER gives up and the queue clears.

A failed migration can simply be run again, but the 5 minutes you made everyone queue cannot be taken back.

Do not confuse it with statement_timeout — that limits execution time, and what you need here is a limit on waiting time.

Creating an index blocks writes

Try create index while a write transaction is left open, and record in 05-index.txt that it is blocked.

On one side, start begin; update big set n=n where id=1; select pg_sleep(6);, and on the other side, try set lock_timeout='2s'; create index idx_n on big(n);.

CREATE INDEX takes a SHARE lock, so reads work and writes are blocked. On a large table, that can last for minutes.

Create it with CONCURRENTLY

In the same situation, show that create index concurrently succeeds, and also record in 06-concurrently.txt that it does not work inside a transaction.

It goes through even with a write left open. And if you try begin; create index concurrently ...;, this is what you get.

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

If your migration tool automatically wraps statements in a transaction, it breaks here. Record both.

A failed index stays behind

Write and run a query that finds unusable indexes in pg_index, and record it in 07-invalid.md together with the result. Also write why you need to check this.

select indexrelid::regclass, indisvalid from pg_index where not indisvalid;

If CONCURRENTLY fails midway, an index with indisvalid = false is left behind. You have to drop it and create it again.

If you do not know this, you can spend a long time puzzling over "I created the index but it is not being used." The result may be empty for now — the goal is to get the query into your fingers.

Sum up three things

In 08-notes.md, at least three lines: the real reason migration incidents happen, why lock_timeout is needed, and why you split deployments when dropping a column.

The text must include 대기, lock_timeout, and 배포 (the Korean words for "waiting" and "deployment").