CREATE INDEX CONCURRENTLY: tạo index trên bảng đang chạy mà không chặn ghi

Bạn cần thêm một index trên bảng don_hang đang phục vụ hàng nghìn giao dịch mỗi phút. Gõ CREATE INDEX, và đột nhiên mọi INSERT, UPDATE, DELETE treo lại — toàn bộ cho tới khi index dựng xong, có thể là nhiều phút trên bảng lớn. Đơn hàng dồn ứ, ứng dụng timeout. Đây là một trong những cách tự gây sự cố phổ biến nhất trên PostgreSQL, và cách né rất đơn giản: một từ khóa. Bài này đo thật cơ chế khóa đứng sau.

Vì sao CREATE INDEX thường chặn ghi

Để dựng index, PostgreSQL cần một ảnh chụp nhất quán của bảng. CREATE INDEX thường đạt điều đó bằng cách lấy một ShareLock trên bảng. ShareLock không chặn đọc — SELECT vẫn chạy — nhưng nó xung khắc với mọi khóa ghi, nên INSERT/UPDATE/DELETE phải xếp hàng chờ tới khi build xong.

CREATE INDEX idx_dh_ma ON don_hang(ma);   -- lấy ShareLock, khóa ghi cả bảng

Đo thật: khởi động lệnh này trên bảng 3 triệu dòng, rồi từ một connection khác thử INSERT với lock_timeout = 600ms:

Hình 1: CREATE INDEX thường lấy ShareLock — INSERT song song bị treo rồi huỷ vì lock_timeout. CREATE INDEX CONCURRENTLY lấy ShareUpdateExclusiveLock — cùng lúc đó INSERT trả về INSERT 0 1, ghi thành công.

Dòng ERROR: canceling statement due to lock timeout chính là bằng chứng: nếu không đặt lock_timeout, INSERT đó sẽ treo im lặng cho tới khi index xong. Trên production, đó là ứng dụng đứng hình.

CONCURRENTLY: lock nhẹ hơn, ghi chạy song song

Thêm từ khóa CONCURRENTLY, PostgreSQL đổi chiến lược: nó chỉ lấy một ShareUpdateExclusiveLock — khóa này cho phép INSERT/UPDATE/DELETE chạy đồng thời.

CREATE INDEX CONCURRENTLY idx_dh_ma ON don_hang(ma);   -- ghi không bị chặn

Đo thật xác nhận: trong lúc lệnh này chạy, pg_locks cho thấy mode là ShareUpdateExclusiveLock, và INSERT song song trả về INSERT 0 1 ngay lập tức — không hề bị treo. Cách nó làm được: PostgreSQL quét bảng hai lượt. Lượt một dựng index từ dữ liệu hiện có; lượt hai bắt và hòa vào những dòng đã thay đổi trong lúc lượt một chạy. Vì thế nó không cần khóa ghi.

-- Cú pháp đầy đủ để dựng và soi lock từ session khác:
CREATE INDEX CONCURRENTLY idx_dh_ma ON don_hang(ma);

SELECT l.mode, l.granted FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation = 'don_hang'::regclass;   -- ShareUpdateExclusiveLock

Hình 2: CONCURRENTLY chỉ lấy ShareUpdateExclusiveLock nên ghi chạy song song, đổi lại quét bảng hai lượt và có ba ràng buộc vận hành cần biết.

Cái giá phải trả

CONCURRENTLY không miễn phí — có ba điều phải nhớ, nếu không sẽ tự bẫy mình:

Chậm hơn. Vì quét hai lượt, nó luôn lâu hơn build thường. Đo thật ở đây: 2.243 ms (thường) so với 2.443 ms (concurrently) — chênh nhỏ trên bảng đứng yên, nhưng khoảng cách nới rộng đáng kể khi bảng đang có nhiều ghi phải hòa lại. Bạn đổi thời gian build lấy việc không chặn ghi — một đánh đổi gần như luôn đáng trên production.

Không chạy trong khối giao dịch. CREATE INDEX CONCURRENTLY không thể nằm trong BEGIN ... COMMIT. Điều này khiến nhiều công cụ migration mặc định (bọc mỗi bước trong một transaction) báo lỗi — phải cấu hình riêng để chạy nó ngoài transaction.

Thất bại để lại index INVALID. Đây là cái bẫy nguy hiểm nhất. Nếu build thất bại giữa chừng — ví dụ CREATE UNIQUE INDEX CONCURRENTLY gặp dòng trùng — PostgreSQL để lại một index hỏng thay vì dọn sạch:

CREATE UNIQUE INDEX CONCURRENTLY idx_uniq_fail ON don_hang(ma);
-- ERROR:  could not create unique index "idx_uniq_fail"
-- DETAIL:  Key (ma)=(DH1) is duplicated.

Đo thật cho thấy index idx_uniq_fail vẫn tồn tại với indisvalid = f. Index INVALID này không được dùng để truy vấn (vô ích), nhưng vẫn phải cập nhật mỗi lần ghi (có hại). Bạn phải tự tìm và xóa nó:

-- Tìm mọi index INVALID còn sót
SELECT c.relname FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

DROP INDEX CONCURRENTLY IF EXISTS idx_uniq_fail;   -- DROP cũng nên CONCURRENTLY

Ba ý mang về

1. CREATE INDEX thường lấy ShareLock chặn mọi ghi (INSERT/UPDATE/DELETE) tới khi build xong — đo thật: INSERT song song bị huỷ vì lock timeout. Trên bảng production đông ghi, đây là sự cố ngừng dịch vụ.
2. CREATE INDEX CONCURRENTLY lấy ShareUpdateExclusiveLock cho ghi chạy song song bằng cách quét bảng hai lượt — luôn dùng nó khi thêm index trên hệ thống đang chạy.
3. Cái giá là build chậm hơn, không chạy trong transaction, và thất bại để lại index INVALID (indisvalid = f) vẫn ngốn chi phí ghi — luôn kiểm pg_index sau khi build hỏng và DROP INDEX CONCURRENTLY để dọn.

Phần sau ta đo chính cái chi phí ẩn vừa nhắc tới: Phần sau đo mỗi index làm chậm INSERT/UPDATE bao nhiêu, để bạn cân nhắc giữ index nào và bỏ index nào.