CREATE INDEX CONCURRENTLY — cơ chế, cái giá phải trả

Trên bảng lớn đang chạy production, lệnh CREATE INDEX thường sẽ khoá ghi (INSERT/UPDATE/DELETE) trong nhiều phút tới hàng giờ. CREATE INDEX CONCURRENTLY (viết tắt CIC — tạo index không chặn ghi) tránh việc đó, nhưng đổi lại build lâu hơn, tốn tài nguyên hơn, và có vài cách hỏng riêng mà lệnh thường không có.

Tài liệu này bổ sung cho Migration PostgreSQL — zero downtime: phần đó chỉ nói "thêm index thì dùng CONCURRENTLY", phần này nói nó chạy thế nào bên dưới và khi nào KHÔNG dùng.

Kiểm chứng bằng Context7 trên tài liệu PostgreSQL 17 (sql-createindex.html, sql-reindex.html, progress-reporting.html). Cơ chế này giống nhau từ PostgreSQL 8.2 tới bản mới nhất; PG 12 thêm REINDEX CONCURRENTLY.

1. Nó là gì / chạy thế nào

1.1. Lệnh thường khoá cái gì

CREATE INDEX (không CONCURRENTLY) lấy SHARE lock (khoá chia sẻ) trên bảng và giữ suốt quá trình build:

SELECT vẫn chạy được (SHARE không đụng ACCESS SHARE).
INSERT/UPDATE/DELETE bị chặn hết — chúng cần ROW EXCLUSIVE lock, xung đột với SHARE.
Build một index trên bảng vài chục triệu dòng mất từ vài phút tới hàng giờ. Suốt thời gian đó bảng "đọc được, ghi không được" — với hệ thống có ghi liên tục thì coi như downtime.

1.2. CIC đổi khoá, và tách build thành nhiều transaction

CIC chỉ lấy SHARE UPDATE EXCLUSIVE lock (viết tắt SUE — khoá cho phép đọc và ghi bình thường, chỉ chặn DDL khác). Với khoá này:

SELECT và INSERT/UPDATE/DELETE chạy bình thường suốt lúc build.
Cái bị chặn: một CIC khác trên cùng bảng, ALTER TABLE, VACUUM thủ công trên đúng bảng đó, và autovacuum sẽ nhường (bỏ lượt) cho tới khi CIC xong.

Đánh đổi để có khoá nhẹ đó: CIC không build trong một transaction. Nó chia thành nhiều transaction ngắn nối tiếp, và quét bảng 2 lần. Giữa các bước, nó phải đợi các transaction khác kết thúc.

sequenceDiagram
  participant W as Writer khác (app đang chạy)
  participant PG as PostgreSQL (tiến trình CIC)
  participant CAT as Catalog (pg_index)

  Note over PG: TX1 — đăng ký index
  PG->>CAT: thêm dòng index, cờ isready=false, isvalid=false
  PG->>PG: lấy SHARE UPDATE EXCLUSIVE lock
  PG-->>PG: commit TX1
  Note over W: Từ giờ mọi writer THẤY index này
và tự ghi entry mới vào nó

  Note over PG: Đợi 1 — chờ transaction đang mở (có thể chưa thấy index) chạy xong

  Note over PG: TX2 — quét lần 1 (build)
  PG->>PG: chụp snapshot, quét toàn bảng theo snapshot đó
  PG->>PG: sort và ghi ra cây index
  W->>CAT: trong lúc này writer vẫn ghi entry mới song song
  PG->>CAT: đánh dấu index isready=true (vẫn isvalid=false)
  PG-->>PG: commit TX2

  Note over PG: Đợi 2 — chờ transaction bắt đầu trước khi index ready chạy xong

  Note over PG: TX3 — quét lần 2 (validate)
  PG->>PG: chụp snapshot mới, quét lại bảng
  PG->>PG: thêm những dòng thay đổi hoặc mới mà lần 1 chưa có
  Note over PG: UNIQUE — bắt đầu ép ràng buộc trùng từ lần quét này

  Note over PG: Đợi 3 — chờ transaction còn giữ snapshot cũ hơn lần quét 2

  PG->>CAT: đánh dấu index isvalid=true
  PG-->>PG: commit TX3 — index dùng được

1.3. Vì sao phải quét 2 lần và đợi 3 lần

Mấu chốt: trong lúc quét lần 1, dữ liệu vẫn đang bị ghi. Cơ chế để không mất dòng nào:

TX1 công bố index trước. Sau khi TX1 commit, mọi writer đọc catalog đều thấy index và có nghĩa vụ tự thêm entry vào nó mỗi lần INSERT/UPDATE. Nên mọi dòng ghi sau thời điểm này đã tự vào index.
Đợi 1: các transaction đang mở trước TX1 có thể chưa thấy index → đợi chúng xong để chắc chắn "từ đây trở đi, writer nào cũng đang maintain index".
Quét lần 1 xử lý toàn bộ dòng cũ (đã có trước khi index được công bố).
Quét lần 2 là vòng "quét vét": bắt những dòng thay đổi giữa 2 snapshot mà lần 1 nhìn thấy ở trạng thái cũ. Sau lần 2, index đã khớp 100% với bảng.
Đợi 3 trước khi bật isvalid=true: nếu có transaction cũ còn chạy với snapshot cũ hơn lần quét 2, query của nó có thể dùng index mà thiếu vài dòng cũ → đợi hết rồi mới cho planner dùng index.

Xem tiến độ real-time trong lúc build:

SELECT pid, phase, blocks_done, blocks_total,
       tuples_done, tuples_total, current_locker_pid
FROM pg_stat_progress_create_index;
-- phase chạy qua: "building index",
-- "waiting for writers before validation",
-- "index validation: scan table",
-- "waiting for old snapshots"...

1.4. Trong Laravel

Hai điều bắt buộc, nếu không migration sẽ nổ:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    // 1. Bắt buộc: CIC không chạy được trong transaction block.
    //    Laravel mặc định bọc mỗi migration trong 1 transaction trên Postgres.
    public $withinTransaction = false;

    public function up(): void
    {
        // 2. Schema builder ($table->index()) không có option concurrently → phải raw.
        DB::statement('CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id)');
    }

    public function down(): void
    {
        DB::statement('DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_id');
    }
};

Vì migration này không có transaction, nếu nó fail giữa chừng thì không có rollback tự động. Bảng migrations sẽ không ghi nhận migration này, và trong database còn lại một index INVALID (xem mục 3). Phải dọn tay rồi chạy lại.

2. Lợi ích

Lợi ích	Cụ thể
Không khoá ghi	App vẫn INSERT/UPDATE/DELETE suốt lúc build. Đây là lý do duy nhất, nhưng đủ lớn: cho phép thêm index trên bảng nóng vào giữa giờ làm việc.
Khoá giữ ở mức bảng rất nhẹ	SHARE UPDATE EXCLUSIVE không xếp sau các `SELECT` dài trong lock queue (hàng đợi khoá) như ACCESS EXCLUSIVE của `ALTER TABLE` — nên không gây "1ms mà sập site".
An toàn để bỏ index thử nghiệm	`DROP INDEX CONCURRENTLY` cũng không khoá ghi → thử thêm/bỏ index để đo query plan mà không sợ ảnh hưởng traffic.
Hợp với quy trình expand–contract	Thêm index là bước "expand" thuần, backward-compatible: code cũ không biết index tồn tại vẫn chạy đúng, chỉ chậm hơn.

3. Tác hại / hạn chế

3.1. Build lâu hơn và tốn tài nguyên hơn

Quét bảng 2 lần thay vì 1 → CPU và I/O gần gấp đôi lệnh thường.
Cộng thêm thời gian đợi các transaction khác (3 lần đợi). Nếu hệ thống có transaction dài (report, batch job, pg_dump), CIC đứng chờ chúng — build có thể kéo dài gấp nhiều lần.
Chỉ lần quét đầu chạy song song (parallel workers). Hai pha sau đơn luồng.

3.2. Nếu fail thì để lại index INVALID

flowchart TD
  B["Đang build
(CREATE INDEX CONCURRENTLY)"]
  B -->|"chạy xong cả 3 transaction"| V["Index VALID
planner dùng được"]
  B -->|"deadlock / trùng UNIQUE /
bị cancel / mất kết nối"| I["Index INVALID
mỗi INSERT/UPDATE vẫn phải ghi vào nó
(tốn tài nguyên) nhưng planner bỏ qua"]
  I -->|"DROP INDEX CONCURRENTLY"| D["Đã xoá — làm lại từ đầu"]
  I -->|"REINDEX INDEX CONCURRENTLY"| V

Index INVALID là trạng thái xấu nhất: nó có cái giá (mỗi lần ghi vào bảng đều phải cập nhật nó) nhưng không có lợi ích (planner không dùng). Query để tìm:

SELECT c.relname AS index_name, t.relname AS table_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
WHERE i.indisvalid = false;

3.3. Chặn VACUUM dọn dead tuple toàn database

CIC giữ một snapshot "sống" suốt lúc build. Snapshot đó ghim xmin horizon (mốc tuổi transaction nhỏ nhất còn cần nhìn thấy) → VACUUM ở mọi bảng khác trong cùng database cũng không xoá được dead tuple mới hơn mốc đó cho tới khi CIC xong. Trên bảng lớn build vài giờ, bloat (phình file do dead tuple không dọn được) có thể tăng đáng kể ở khắp nơi.

3.4. Các giới hạn cứng

Không chạy trong transaction block (BEGIN ... COMMIT). Cũng không chạy được trong function/DO block.
Mỗi bảng chỉ một CIC tại một thời điểm. Chạy 2 cái song song → một cái đợi, hoặc deadlock.
Bảng partition: không CIC thẳng trên bảng cha. Phải CIC từng partition con, rồi CREATE INDEX ON ONLY parent + ALTER INDEX ... ATTACH PARTITION.
UNIQUE index: nếu dữ liệu có sẵn dòng trùng, CIC quét tới lần 2 mới phát hiện rồi mới fail — mất công build gần hết mới lỗi, và để lại index INVALID.

4. Trade-off

flowchart TD
  Q{"Bảng có đang nhận ghi
trong lúc bạn build không?"}
  Q -->|"Không (maintenance window,
bảng mới, môi trường staging)"| PLAIN["CREATE INDEX thường
nhanh hơn, 1 transaction,
fail thì rollback sạch"]
  Q -->|"Có"| SIZE{"Bảng lớn cỡ nào?"}
  SIZE -->|"Nhỏ (build dưới 1s)"| PLAIN2["CREATE INDEX thường
khoá ghi dưới 1s, chấp nhận được"]
  SIZE -->|"Lớn"| CIC["CREATE INDEX CONCURRENTLY
chịu build lâu + rủi ro INVALID
để đổi lấy không downtime"]

Tiêu chí	CREATE INDEX thường	CONCURRENTLY
Khoá ghi	Có, suốt build	Không
Số lần quét bảng	1	2 + 3 lần đợi
Thời gian	Ngắn hơn	Dài hơn, phụ thuộc transaction khác
Chạy trong transaction / migration có transaction	Được	Không
Fail giữa chừng	Rollback sạch, không để lại gì	Để lại index INVALID, phải dọn tay
Ảnh hưởng VACUUM toàn DB	Ít	Ghim xmin horizon suốt build

Phương án thứ ba khi bảng cực lớn và không được sai: tạo index trên một replica trước, đo, rồi mới promote / hoặc dùng logical replication để chuyển sang bảng mới đã có sẵn index. Phức tạp hơn nhiều, chỉ làm khi CIC không đủ an toàn.

5. Khó khăn khi vận hành thực tế

CIC đứng im, không rõ vì sao. Gần như luôn là đang ở pha "waiting for..." vì một transaction khác chưa đóng. Phải soi pg_stat_activity tìm transaction dài (report chạy 40 phút, một session idle in transaction bị quên) và xử lý nó. CIC không tự bỏ cuộc — nó đợi mãi.
Không đặt lock_timeout vẫn có rủi ro. Bước TX1 cần lấy SUE lock; nếu có ALTER TABLE khác đang đợi ACCESS EXCLUSIVE, TX1 xếp hàng sau nó và block luôn cả reader. Vẫn nên SET lock_timeout trước CIC và retry.
Deploy pipeline bị timeout. CI/CD hay đặt giới hạn 10–15 phút cho job migration. CIC trên bảng lớn vượt xa. Phải tách CIC ra chạy thủ công / job riêng không giới hạn thời gian, hoặc chạy ngoài giờ.
Job bị kill giữa chừng (pod restart, mạng rớt, ai đó Ctrl-C) → index INVALID nằm lại. Cần một bước kiểm tra indisvalid = false trong health-check hoặc runbook, không thì vài tuần sau mới có người phát hiện query vẫn chậm.
Monitoring: theo dõi pg_stat_progress_create_index để biết còn bao lâu; theo dõi dead tuple / bloat ở các bảng khác trong lúc build dài.

6. Case thực tế và cách xử lý

Case 1 — CIC chạy 2 tiếng không nhúc nhích

Triệu chứng: pg_stat_progress_create_index kẹt ở phase waiting for writers before validation, lockers_done không tăng.

Tìm thủ phạm:

SELECT pid, state, now() - xact_start AS age, left(query, 80)
FROM pg_stat_activity
WHERE state <> 'idle' AND xact_start < now() - interval '5 min'
ORDER BY age DESC;

Thấy một session idle in transaction tuổi 2 tiếng (client quên commit). CIC đang đợi đúng nó.
Báo chủ session đó commit/rollback. Nếu không liên hệ được và nó vô hại: SELECT pg_terminate_backend(<pid>).
Ngay khi transaction kia đóng, CIC tự chạy tiếp qua pha validate. Không cần restart CIC.
Phòng sau này: đặt idle_in_transaction_session_timeout ở mức DB để loại này tự chết sau vài phút.

Case 2 — CIC fail vì trùng khi tạo UNIQUE index

Triệu chứng: migration lỗi could not create unique index ... Key (email)=(a@x.com) is duplicated. Trong DB còn lại index INVALID.

Dọn index hỏng trước:

DROP INDEX CONCURRENTLY IF EXISTS idx_users_email_unique;

Tìm và xử lý dữ liệu trùng (do bug lúc trước, chưa có ràng buộc nên lọt):
```
SELECT email, count(*), array_agg(id)
FROM users GROUP BY email HAVING count(*) > 1;
```
Gộp/merge hoặc xoá bản trùng theo luật nghiệp vụ. Đây là bước tốn thời gian nhất, làm cẩn thận.
Chạy lại CIC. Lần này qua.
Rút kinh nghiệm: với UNIQUE index, nên tạo trước dưới dạng CREATE UNIQUE INDEX CONCURRENTLY ở môi trường có bản sao dữ liệu production để phát hiện trùng sớm, đừng để nổ trên production.

7. Câu hỏi phụ interviewer hay hỏi tiếp

"CIC không khoá ghi, vậy nó dùng khoá gì?"

SHARE UPDATE EXCLUSIVE trên bảng, giữ suốt build. Khoá này cho đọc và ghi bình thường, chỉ xung đột với chính nó (CIC khác), với ALTER TABLE, và VACUUM thủ công trên cùng bảng.

"Vì sao nó không chạy được trong transaction block?"

Vì cơ chế của nó là nhiều transaction nối tiếp, có commit ở giữa để các writer khác nhìn thấy index và các pha "đợi" hoạt động đúng. Bọc trong một transaction lớn thì các commit đó không xảy ra, cơ chế sập. Trong Laravel phải đặt public $withinTransaction = false;.

"Nếu CIC bị kill giữa chừng thì sao?"

Để lại một index có cờ indisvalid = false. Planner bỏ qua nó nhưng mỗi lần ghi vào bảng vẫn phải cập nhật nó. Phải DROP INDEX CONCURRENTLY rồi tạo lại, hoặc REINDEX INDEX CONCURRENTLY để "chữa" tại chỗ.

"Tại sao build index lại làm VACUUM ở bảng khác chậm đi?"

CIC giữ một snapshot lâu. Snapshot đó ghim xmin horizon, nên VACUUM ở mọi bảng trong cùng database không xoá được dead tuple mới hơn mốc đó cho tới khi CIC xong. Bảng build càng lâu, bloat tích ở các bảng khác càng nhiều.

"Có cần lock_timeout khi đã dùng CONCURRENTLY không?"

Có. Bản thân build không khoá ghi, nhưng bước đầu vẫn phải lấy SUE lock. Nếu có ALTER TABLE khác đang xếp hàng chờ ACCESS EXCLUSIVE, CIC xếp sau nó và chặn cả reader trong lúc chờ. Đặt lock_timeout ngắn + retry vẫn là thói quen đúng.

"REINDEX CONCURRENTLY khác gì?"

Cùng ý tưởng (nhiều transaction, quét 2 lần, khoá SUE) nhưng để build lại một index đã tồn tại — dùng khi index bị bloat hoặc corrupt. Nó tạo một index tạm, đổ dữ liệu sang, rồi tráo tên và bỏ index cũ. Cũng không chạy trong transaction block.