Migration "thêm một cột" được coi là vô hại, và phần lớn thời gian nó đúng là vậy: một dòng trong catalog, một mili giây. Rồi một ngày cùng câu lệnh đó làm toàn bộ dịch vụ đứng năm giây, hoặc năm phút, và không ai hiểu vì sao — câu lệnh đâu có đổi. Thứ đổi là hoàn cảnh: một giao dịch ai đó quên đóng, một default không phải hằng số, một bảng đã lớn gấp trăm lần. Phần này đo từng kiểu ALTER TABLE bằng timing phía máy chủ, dựng lại hàng đợi khoá kinh điển với pgbench chạy nền để thấy tps rơi về 0, và đo các lối đi vòng — lock_timeout, CONCURRENTLY, NOT VALID — bằng cùng một thước: một UPDATE một dòng chen vào bị chặn bao lâu.

Khoá, hàng đợi khoá, và lối đi vòng

Ảnh chụp đoạn mã nền tối đổi schema không downtime khoá hàng đợi khoá và lối đi vòng với PostgreSQL 16 bảng docs 1 triệu dòng pgbench P 1 đọc ghi ngẫu nhiên chạy nền để thấy ai bị chặn bao lâu, ALTER TABLE lấy ACCESS EXCLUSIVE chặn cả SELECT câu hỏi là giữ bao lâu gồm ADD COLUMN c text chỉ sửa catalog khoá vài ms, ADD COLUMN c text NOT NULL DEFAULT x default hằng catalog PG11 cộng vài ms không viết lại bảng, ADD COLUMN c timestamptz DEFAULT now default volatile viết lại cả bảng khoá suốt lúc viết lại, ALTER COLUMN c SET NOT NULL quét cả bảng kiểm NULL khoá suốt lúc quét, ADD CHECK FK trực tiếp quét cả bảng kiểm khoá suốt lúc quét, CREATE INDEX SHARE chặn ghi suốt lúc dựng, hàng đợi khoá ALTER vài ms vẫn làm sập dịch vụ gồm phiên 1 BEGIN SELECT FROM docs để đó giữ ACCESS SHARE, phiên 2 ALTER TABLE docs ADD COLUMN xin ACCESS EXCLUSIVE chờ phiên 1, phiên 3 tới n SELECT FROM docs xếp hàng sau phiên 2 dù chỉ cần ACCESS SHARE, một giao dịch quên COMMIT cộng một ALTER vô hại bằng mọi truy vấn vào bảng đứng im, lock_timeout ALTER bỏ cuộc sau N giây thay vì giữ cả hàng đợi làm con tin retry sau, lối đi vòng tách khoá ngắn khỏi quét dài gồm CREATE INDEX CONCURRENTLY hai lần quét không chặn ghi lâu hơn thất bại để lại index INVALID, ADD CONSTRAINT NOT VALID rồi VALIDATE CONSTRAINT khoá ngắn cộng quét dưới SHARE UPDATE EXCLUSIVE ghi được, SET NOT NULL qua CHECK c IS NOT NULL NOT VALID rồi VALIDATE rồi SET NOT NULL PG12 cộng bỏ quét lần hai, đo gì gồm A thời gian khoá của 5 kiểu ALTER trên 1M dòng cộng độ trễ đọc từng giây trong lúc đó, B hàng đợi khoá 1 giao dịch mở cộng ALTER tps đọc rơi về 0 bao lâu lock_timeout 2s thì sao, C CREATE INDEX vs CONCURRENTLY thời gian tps ghi trong lúc dựng, D CHECK trực tiếp vs NOT VALID cộng VALIDATE SET NOT NULL trực tiếp vs ba bước

Hình 1: Mọi ALTER TABLE lấy ACCESS EXCLUSIVE; câu hỏi là giữ bao lâu. Hàng đợi khoá khiến ALTER một mili giây vẫn chặn mọi SELECT. Lối đi vòng tách khoá ngắn khỏi quét dài.

ALTER TABLE lấy khoá ACCESS EXCLUSIVE — xung đột với mọi thứ, kể cả SELECT. Điều đó không tự nó nguy hiểm; nguy hiểm là giữ bao lâu. Thêm cột không default, hay có default là hằng số (PostgreSQL 11+), chỉ sửa catalog: mili giây. Default là biểu thức volatile (random(), clock_timestamp()) thì phải tính giá trị riêng cho từng dòng — viết lại cả bảng dưới khoá. SET NOT NULL, ADD CHECK, ADD FOREIGN KEY trực tiếp phải quét cả bảng để kiểm — cũng dưới khoá. CREATE INDEX lấy khoá SHARE: cho đọc, chặn ghi, suốt lúc dựng.

Hàng đợi khoá là cơ chế biến "vài mili giây" thành sự cố. Phiên 1 mở giao dịch, SELECT một lần rồi để đó — giữ ACCESS SHARE cho tới khi COMMIT. Phiên 2 chạy ALTER TABLE: xin ACCESS EXCLUSIVE, phải chờ phiên 1. Phiên 3, 4, 5… chỉ cần ACCESS SHARE để SELECT, nhưng PostgreSQL xếp khoá theo thứ tự xin: chúng đứng sau phiên 2 trong hàng đợi, dù khoá chúng cần không hề xung đột với phiên 1. Kết quả: một giao dịch quên đóng cộng một ALTER vô hại là mọi truy vấn vào bảng đứng im. lock_timeout là lối ra: ALTER bỏ cuộc sau N giây thay vì giữ cả hàng đợi làm con tin.

Các lối đi vòng đều cùng một ý: tách phần cần khoá độc quyền (ngắn) khỏi phần quét dữ liệu (dài), và cho phần dài chạy dưới khoá nhẹ hơn. CREATE INDEX CONCURRENTLY quét hai lần dưới SHARE UPDATE EXCLUSIVE, cho ghi tiếp. ADD CONSTRAINT … NOT VALID chỉ sửa catalog, rồi VALIDATE CONSTRAINT quét dưới SHARE UPDATE EXCLUSIVE. SET NOT NULL từ PostgreSQL 12 bỏ qua quét nếu đã có CHECK IS NOT NULL hợp lệ — nên ba bước NOT VALID → VALIDATE → SET NOT NULL không có bước nào giữ khoá độc quyền lâu.

Môi trường đo: docs một triệu dòng và pgbench_accounts ba triệu dòng trên pg-prim; thời gian lấy bằng \timing của psql (phía máy chủ, không tính khởi động client); pgbench -P 1 chạy nền để thấy tps từng giây.

Đo thật: mili giây, giây, và ai bị chặn

Ảnh chụp output thật nền tối ALTER 1 ms vẫn làm đọc về 0 trong 5 giây với pg-prim postgres 16 docs 1M dòng pgbench_accounts 3M dòng thời gian đo bằng timing phía máy chủ, A năm kiểu ALTER trên docs 1M dòng pgbench đọc c 4 nền khoảng 80k tps gồm ADD COLUMN c1 text 7,6 ms catalog, ADD COLUMN c2 text NOT NULL DEFAULT x 1,1 ms default hằng catalog không viết lại, ADD COLUMN c3 timestamptz DEFAULT now 1,0 ms now là STABLE không volatile cũng không viết lại, ADD COLUMN c5 float DEFAULT random 739 ms VOLATILE viết lại bảng docs 145 MB sang 339 MB, ALTER COLUMN c1 SET NOT NULL trực tiếp 241 ms quét 1M dòng dưới ACCESS EXCLUSIVE, tps đọc từng giây 78k 80k 80k 80k 78k 19k 51k 47k 43k 36k 21k 77k 79k 80k hai lệnh cuối làm tụt 5 giây, B hàng đợi khoá 1 giao dịch mở 8 giây ACCESS SHARE cộng ALTER ADD COLUMN vô hại gồm ALTER xong sau 5531 ms chờ giao dịch kia log_lock_waits 1 ALTER chờ AccessExclusiveLock 4 SELECT chờ AccessShareLock, tps đọc từng giây 80k 80k 0 0 0 0 0 44k 81k 83k 81k 5 giây không một SELECT nào chạy, với lock_timeout 2s ERROR canceling statement due to lock timeout sau 2077 ms tps đọc 83k 82k 0 0 80k 82k 82k 79k tổn thất đúng 2 giây ALTER thử lại sau, C khoá DDL giữ và UPDATE 1 dòng chen vào bị chặn bao lâu không tải nền gồm CREATE INDEX accounts 3M 512 ms ShareLock UPDATE 112,5 ms chờ tới khi index xong, CREATE INDEX CONCURRENTLY 888 ms ShareUpdateExclusiveLock UPDATE 1,6 ms, ADD CHECK md5 body docs 1M 481 ms AccessExclusiveLock UPDATE 37,5 ms, ADD CHECK NOT VALID rồi VALIDATE 1 ms rồi 453 ms ShareUpdateExclusiveLock UPDATE 0,8 ms, ADD COLUMN DEFAULT random docs 622 ms AccessExclusiveLock UPDATE 173,5 ms, nền không DDL 0,8 tới 2,0 ms, D dưới pgbench GHI c 4 vào pgbench_accounts gồm CREATE INDEX 2889 ms tps ghi 4k 3k 2k 0 0 0 4k chặn ghi suốt lúc dựng, ADD CHECK trực tiếp 4978 ms tps ghi 6k 4k 4k 0 0 0 0 0 4k 115 ms quét cộng khoảng 4,8 giây chờ lấy khoá sau các writer, NOT VALID cộng VALIDATE 1 ms cộng 302 ms tps ghi 9k 13k 13k 6k 14k 9k 12k không giây nào về 0, SET NOT NULL filler trực tiếp 333 ms so với CHECK NOT VALID cộng VALIDATE 322 ms ghi được rồi SET NOT NULL 26 ms, nói thẳng bảng 1 tới 3M dòng nằm gọn trong cache nên quét cả bảng chỉ 115 tới 480 ms ở 300 GB cùng câu lệnh là hàng chục phút giữ khoá tps ghi nền ở D dao động vì I O trên máy ảo cột C không tải mới là số sạch để so khoá

Hình 2: Catalog-only là 1–8 ms, viết lại bảng 739 ms, quét 241 ms. Hàng đợi khoá: 5 giây tps đọc bằng 0, lock_timeout cắt còn 2. CONCURRENTLY và VALIDATE giữ khoá nhẹ, UPDATE qua trong 1–2 ms.

phép đo kết quả
ADD COLUMN text / NOT NULL DEFAULT 'x' / DEFAULT now() 7,6 / 1,1 / 1,0 ms
ADD COLUMN DEFAULT random() (volatile, 1M dòng) 739 ms, bảng 145 → 339 MB
SET NOT NULL trực tiếp (quét 1M dòng) 241 ms
1 giao dịch mở 8 s + ALTER vô hại ALTER chờ 5.531 ms; tps đọc 0 trong 5 giây
cùng kịch bản, lock_timeout = 2s ALTER huỷ sau 2.077 ms; tps đọc 0 trong 2 giây
CREATE INDEX / CONCURRENTLY: khoá giữ, UPDATE 1 dòng chen vào ShareLock, 112,5 ms / ShareUpdateExclusive, 1,6 ms
ADD CHECK trực tiếp / NOT VALID + VALIDATE: khoá, UPDATE chen vào AccessExclusive, 37,5 ms / ShareUpdateExclusive, 0,8 ms
ADD CHECK dưới tải ghi: trực tiếp / NOT VALID + VALIDATE 4.978 ms, ghi 0 trong 5 s / 1 + 302 ms, không giây nào 0
SET NOT NULL: trực tiếp / sau CHECK đã VALID 333 ms / 26 ms

Default hằng số là catalog; default volatile là viết lại bảng. Thêm cột text mất 7,6 ms; thêm cột NOT NULL DEFAULT 'x' mất 1,1 ms — từ PostgreSQL 11 giá trị default hằng được ghi vào catalog và trả về khi đọc, không chạm trang nào. DEFAULT now() cũng 1,0 ms: now() là stable (một giá trị cho cả câu lệnh), không phải volatile, nên vẫn đi đường catalog — một chỗ nhiều người đoán sai, kể cả bài này trước khi đo. DEFAULT random() thì khác: 739 ms viết lại một triệu dòng dưới ACCESS EXCLUSIVE, và bảng từ 145 MB thành 339 MB vì bản cũ chưa được dọn. SET NOT NULL trực tiếp 241 ms — quét để chắc không có NULL. Hai lệnh cuối kéo tps đọc nền từ 80.000 xuống 19.000–51.000 trong năm giây; với bảng 300 GB, cùng hai câu lệnh là hàng chục phút không ai đọc được gì.

Một giao dịch mở cộng một ALTER một mili giây: năm giây không một SELECT nào chạy. Phiên 1 giữ ACCESS SHARE tám giây. ALTER TABLE docs ADD COLUMN c4 int — thứ bình thường xong trong 1 ms — chờ 5.531 ms. Trong lúc chờ, bốn client pgbench chỉ chạy SELECT … WHERE id = …: tps từ 80.000 rơi thẳng về 0 trong năm giây liền, rồi 44.000, rồi hồi. log_lock_waits ghi đúng bức tranh: một ALTER chờ AccessExclusiveLock, bốn SELECT chờ AccessShareLock — sau ALTER. Không có truy vấn nào chậm; chúng không chạy. Đây là sự cố "migration vô hại làm sập dịch vụ" ở dạng tinh khiết nhất, và thủ phạm không phải ALTER mà là giao dịch quên đóng ở một dịch vụ khác.

lock_timeout biến tổn thất không giới hạn thành tổn thất hai giây. Cùng kịch bản với SET lock_timeout = '2s' trước ALTER: canceling statement due to lock timeout sau 2.077 ms; tps đọc về 0 đúng hai giây rồi 80.000. ALTER thất bại — và đó là kết quả tốt: script migration thử lại sau vài giây, trong khi dịch vụ chỉ mất hai giây thay vì chờ tới khi ai đó tìm ra giao dịch treo. Mọi migration trên bảng đang có tải nên bắt đầu bằng SET lock_timeout ngắn và một vòng retry; đó là một dòng, và nó là khác biệt giữa "chậm hai giây" và "sự cố".

CREATE INDEX chặn ghi; CONCURRENTLY thì không — đo bằng một UPDATE chen vào. Không tải nền, CREATE INDEX trên ba triệu dòng mất 512 ms và giữ ShareLock; một UPDATE một dòng chạy giữa chừng chờ 112,5 ms — tới khi index xong. CREATE INDEX CONCURRENTLY mất 888 ms (hai lần quét), giữ ShareUpdateExclusiveLock, và UPDATE chen vào đi qua trong 1,6 ms — bằng lúc không có DDL. Dưới pgbench ghi, khác biệt thành tps: CREATE INDEX 2,9 giây với ba giây ghi bằng 0. Giá của CONCURRENTLY là chậm hơn 70%, và nếu thất bại giữa chừng nó để lại index INVALID phải DROP rồi làm lại — đổi lấy việc không ai nhận ra bạn đang dựng index.

NOT VALID rồi VALIDATE: cùng một quét, khoá khác hẳn. ADD CHECK md5(body) <> '' trực tiếp trên docs: 481 ms giữ AccessExclusiveLock, UPDATE chen vào bị chặn 37,5 ms (phần còn lại của quét). Tách làm hai: NOT VALID 1 ms, rồi VALIDATE CONSTRAINT 453 ms giữ ShareUpdateExclusiveLock — UPDATE chen vào 0,8 ms. Dưới tải ghi vào pgbench_accounts, chênh lệch lộ hết: ADD CHECK trực tiếp mất 4.978 ms với ghi bằng 0 trong năm giây, trong đó quét chỉ ~115 ms còn lại là chờ lấy khoá sau các writer đang chạy — rồi giữ khoá đó qua lượt quét. NOT VALID + VALIDATE: 1 ms + 302 ms, không giây nào tps về 0. SET NOT NULL đi cùng đường: trực tiếp 333 ms dưới khoá độc quyền; qua CHECK IS NOT NULL NOT VALID → VALIDATE (322 ms, ghi được) → SET NOT NULL chỉ còn 26 ms vì PostgreSQL 12+ tin CHECK đã hợp lệ và bỏ quét.

Đánh đổi cần cân nhắc

Thời gian giữ khoá không phải thời gian câu lệnh. ADD CHECK trực tiếp "mất 5 giây" nhưng quét chỉ 115 ms; 4,8 giây còn lại là xếp hàng sau writer — và trong lúc xếp hàng, mọi câu lệnh đến sau xếp hàng sau nó. Hai số đo khác nhau: thời gian DDL chạy, và thời gian người khác bị chặn. Phần này dùng UPDATE một dòng chen vào làm thước đo thứ hai; trong vận hành, đó là pg_stat_activity.wait_event_type = 'Lock' và log_lock_waits = on.

Lối đi vòng tốn nhiều bước hơn và có trạng thái dở dang. CONCURRENTLY để lại index INVALID khi hỏng; NOT VALID để lại ràng buộc chưa kiểm cho tới khi ai đó VALIDATE; ba bước SET NOT NULL là ba lần deploy nếu công cụ migration không hỗ trợ. Đổi lại là không còn khái niệm "cửa sổ bảo trì" cho thay đổi schema thông thường. Với bảng nhỏ (dưới vài chục MB) đường thẳng vẫn hợp lý — quét vài ms không đáng ba bước.

Phạm vi đo. Bảng 1–3 triệu dòng nằm gọn trong cache nên "quét cả bảng" là 115–480 ms; tỉ lệ giữa các cách mới là thứ mang sang bảng 300 GB, không phải số tuyệt đối. Tps ghi nền trên máy ảo dao động nên cột không-tải là số sạch để so khoá. Chưa chạm: đổi kiểu cột (luôn viết lại, cần cột mới + backfill + đổi tên), thêm FK NOT VALID với bảng tham chiếu đang bận, và pg_repack.

Ba ý mang về

  1. Thời gian khoá, không phải thời gian lệnh: ADD COLUMN 1 ms vẫn làm đọc về 0 trong 5 giây nếu một giao dịch khác đang mở; SET lock_timeout ngắn + retry là dòng đầu tiên của mọi migration trên bảng có tải.
  2. Biết lệnh nào quét hay viết lại: default hằng và now() là catalog (1 ms); random() là viết lại (739 ms); SET NOT NULL, CHECK, FK trực tiếp là quét dưới khoá độc quyền. Trên bảng lớn, ba cái sau là downtime.
  3. Tách khoá ngắn khỏi quét dài: CONCURRENTLY (UPDATE qua 1,6 ms thay vì chờ 113 ms), NOT VALID → VALIDATE (0,8 ms thay vì chặn), và SET NOT NULL sau CHECK VALID (26 ms thay vì 333) — cùng một kết quả, không giây nào về 0.

Nguồn

Phần sau đặt PgBouncer trước cụm: mỗi kết nối PostgreSQL tốn bao nhiêu, transaction pooling tăng thông lượng ra sao với hàng nghìn client, và những tính năng gãy khi đi qua pool — prepared statement, SET, advisory lock.