PostgreSQL Bài 28: Kỹ thuật tạo Index an toàn trên Production với CREATE INDEX CONCURRENTLY

Khép lại Phần 4: Indexing & Tối ưu hóa truy vấn chuyên sâu, chúng ta sẽ đối mặt với một vấn đề mang tính sống còn đối với các kỹ sư vận hành và phát triển hệ thống: Làm thế nào để đánh index trên một bảng có hàng chục triệu bản ghi đang chạy thực tế mà không gây gián đoạn dịch vụ (Zero-Downtime)?

Một kịch bản sự cố kinh điển: Bạn phát hiện một câu query bị chậm trên môi trường production, bạn mở migration tool hoặc terminal lên và chạy:

SQL

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Ngay lập tức, toàn bộ các API tạo đơn hàng, thanh toán và cập nhật giỏ hàng của ứng dụng đều bị treo cứng (hang/timeout). Số lượng kết nối (connection pool) chạm ngưỡng tối đa (max_connections), CPU tăng vọt và hệ thống rơi vào trạng thái sập dịch vụ cục bộ.

Tại sao câu lệnh tưởng chừng vô hại đó lại gây ra thảm họa? Và cơ chế CONCURRENTLY đã giải cứu các hệ thống production như thế nào?

1. Mối nguy hiểm của lệnh CREATE INDEX tiêu chuẩn

Khi bạn chạy lệnh CREATE INDEX thông thường (không có từ khóa CONCURRENTLY):

1.1. Cấp độ khóa bảng: ShareLock

PostgreSQL sẽ xin một loại khóa trên bảng gọi là ShareLock.

• Cho phép: Các câu lệnh đọc dữ liệu (SELECT).

• Chặn hoàn toàn (Block): Mọi câu lệnh ghi dữ liệu: INSERT, UPDATE, DELETE.

LỆNH CREATE INDEX THÔNG THƯỜNG:
[ CREATE INDEX ] ──► Chiếm giữ ShareLock trên bảng
                          │
     ┌────────────────────┴────────────────────┐
     ▼                                         ▼
[ SELECT ] (Vẫn đọc được)             [ INSERT / UPDATE / DELETE ]
                                                │
                                                ▼
                                      BỊ CHẶN LẠI HOÀN TOÀN!
                               (Xếp hàng chờ CREATE INDEX chạy xong)

1.2. Hiệu ứng domino: Tích tụ hàng đợi kết nối (Connection Starvation)

Nếu bảng có 50 triệu dòng, lệnh CREATE INDEX có thể mất từ 5 đến 30 phút để hoàn tất việc đọc bảng, sắp xếp và ghi cây B-Tree.

1. Trong suốt 30 phút đó, mọi request ghi từ người dùng đều bị bắt buộc phải xếp hàng chờ giải phóng khóa.

2. Ứng dụng backend (như PHP-FPM workers, Go goroutines, Node.js connection pools) sẽ giữ kết nối mở để chờ database phản hồi.

3. Chỉ trong vòng vài chục giây, toàn bộ Connection Pool bị cạn kiệt. Các service backend không thể mở thêm kết nối mới và bắt đầu trả về lỗi HTTP 504 Gateway Timeout hoặc 500 Internal Server Error.

2. Giải pháp cứu tinh: CREATE INDEX CONCURRENTLY

Để cho phép tạo index mà không chặn bất kỳ thao tác đọc hay ghi nào, PostgreSQL cung cấp tùy chọn CONCURRENTLY:

SQL

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

2.1. Cấp độ khóa: ShareUpdateExclusiveLock

Khi dùng cờ CONCURRENTLY, PostgreSQL chỉ xin khóa ShareUpdateExclusiveLock:

• Khóa này tương thích hoàn toàn với cả lệnh đọc (SELECT) lẫn các lệnh ghi (INSERT, UPDATE, DELETE).

• Khách hàng vẫn mua hàng, đặt đơn, hủy đơn bình thường trong suốt quá trình index đang được dựng dưới nền.

• Khóa này chỉ xung đột với các lệnh DDL thay đổi cấu trúc bảng khác (như ALTER TABLE, VACUUM FULL, hoặc một lệnh CREATE INDEX CONCURRENTLY khác chạy đồng thời trên cùng một bảng).

3. Cơ chế hoạt động bên trong: Quy trình 2-Phase của CONCURRENTLY

Để vừa xây dựng cây index vừa đảm bảo không bỏ sót bất kỳ dòng dữ liệu nào đang được người dùng sửa đổi liên tục, PostgreSQL bắt buộc phải trả giá bằng việc quét bảng 2 lần (2-Phase Table Scan).

TIẾN TRÌNH THỰC THI CONCURRENTLY:

GIAI ĐOẠN 1: Đăng ký Catalog & Snapshot
  ├── Ghi nhận Index vào pg_class với cờ: "Chỉ ghi, không dùng để đọc" (indisready = true, indisvalid = false).
  ├── Chờ toàn bộ các transaction cũ đang chạy hoàn tất (Wait for old transactions).
  └── Bắt đầu từ lúc này: Mọi lệnh INSERT/UPDATE/DELETE mới sẽ tự động cập nhật thêm vào index mới này.

GIAI ĐOẠN 2: Quét bảng lần 1 (First Scan)
  └── Đọc toàn bộ bảng Heap hiện tại và xây dựng khung cây B-Tree ban đầu.

GIAI ĐOẠN 3: Chờ Snapshot mới & Quét bảng lần 2 (Second Scan)
  ├── Chờ các transaction ở Giai đoạn 2 kết thúc.
  ├── Quét lại bảng để gom tất cả các thay đổi (tuple mới/sửa) xảy ra trong lúc Giai đoạn 2 đang chạy.
  └── Đánh dấu Index hợp lệ: indisvalid = true. Index sẵn sàng phục vụ các câu lệnh SELECT!

Cái giá phải trả của CONCURRENTLY:

• Thời gian chạy lâu hơn gấp 2 đến 3 lần so với lệnh thường (vì phải quét bảng 2 lần và chờ các transaction kết thúc).

• Tốn nhiều tài nguyên I/O CPU hơn.

• Không được phép chạy bên trong một Transaction Block (BEGIN ... COMMIT).

4. Cạm bẫy Index Bị Hỏng: Hiện tượng INVALID INDEX

Vì CREATE INDEX CONCURRENTLY phải chờ đợi và chạy song song với các thao tác của người dùng, nó có nguy cơ bị lỗi giữa chừng do:

• Bị Deadlock với một transaction khác.

• Lệnh bị hủy ngang (statement_timeout, người vận hành bấm Ctrl+C).

• Vi phạm ràng buộc (ví dụ: tạo UNIQUE INDEX CONCURRENTLY nhưng trong lúc đang quét bảng thì có 2 dòng trùng nhau được chèn vào).

4.1. Hậu quả khi thất bại

Khi gặp lỗi, PostgreSQL sẽ không tự động dọn dẹp hoặc xóa index đó đi. Thay vào đó, nó để lại một index bị gắn cờ INVALID trong cơ sở dữ liệu.

HIỆN TƯỢNG INVALID INDEX:
- Query Planner hoàn toàn KHÔNG THỂ SỬ DỤNG nó cho câu lệnh SELECT.
- NHƯNG mỗi khi có lệnh INSERT/UPDATE/DELETE, engine VẪN PHẢI GHI CẬP NHẬT vào index hỏng này!
──► Gây lãng phí dung lượng đĩa và làm chậm hiệu năng ghi một cách vô ích.

4.2. Nhận diện Invalid Index trong hệ thống

Để kiểm tra xem cơ sở dữ liệu của bạn có đang chứa "rác" index hỏng hay không, hãy chạy truy vấn vào pg_index:

SQL

SELECT 
    c.relname AS index_name,
    t.relname AS table_name,
    i.indisvalid,
    i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE i.indisvalid = FALSE;

Nếu cột indisvalid = FALSE, index đó đã bị hỏng.

4.3. Cách xử lý Index bị INVALID an toàn

Tuyệt đối không chạy thẳng lại lệnh CREATE INDEX CONCURRENTLY cũ vì nó sẽ tạo thêm một index trùng lặp khác. Quy trình xử lý chuẩn:

SQL

-- Bước 1: Xóa index hỏng một cách an toàn (Cũng phải dùng CONCURRENTLY để tránh khóa bảng!)
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_id;

-- Bước 2: Tạo lại index mới từ đầu
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

(Hoặc từ PostgreSQL 12+, bạn có thể dùng lệnh tái tạo index trực tiếp: REINDEX INDEX CONCURRENTLY idx_orders_customer_id;).

5. Quy trình 4 bước tạo Index an toàn tuyệt đối trên Production

Để đảm bảo an toàn 100% cho hệ thống có lưu lượng tải lớn (High-traffic System), hãy luôn tuân thủ quy trình sau:

Bước 1: Kiểm tra các Transaction chạy dài (Long-running Transactions)

Vì CONCURRENTLY phải chờ toàn bộ các transaction đang mở kết thúc trước khi hoàn thành, nếu hệ thống đang có một transaction bị treo hoặc một câu query chạy ngốn hàng giờ, lệnh tạo index sẽ bị chặn ở trạng thái chờ (waiting):

SQL

-- Kiểm tra xem có transaction nào đang chạy quá 5 phút hay không:
SELECT pid, now() - xact_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle' 
  AND (now() - xact_start) > interval '5 minutes';

Hành động: Cần chờ hoặc ngắt (pg_terminate_backend) các transaction treo này trước khi bắt đầu.

Bước 2: Thiết lập Timeout an toàn cho phiên làm việc

Tránh việc lệnh tạo index chạy treo vô tận làm cạn kiệt tài nguyên:

SQL

-- Trong phiên làm việc psql thực hiện tạo index:
SET statement_timeout = '2h'; -- Cho phép chạy tối đa 2 giờ, sau đó tự hủy
SET lock_timeout = '10s';     -- Nếu phải chờ xin khóa quá 10 giây thì hủy ngay, không làm nghẽn app

Bước 3: Tạm thời tăng bộ nhớ bảo trì (maintenance_work_mem)

Tốc độ tạo index phụ thuộc trực tiếp vào dung lượng RAM dành cho tác vụ bảo trì. Mặc định maintenance_work_mem thường chỉ là 64MB. Hãy tăng con số này lên riêng cho session hiện tại:

SQL

-- Cấp 1GB hoặc 2GB RAM riêng cho phiên này để B-Tree sort nhanh hơn:
SET maintenance_work_mem = '2GB';

Bước 4: Thực thi tạo Index

SQL

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

Sau khi hoàn thành, kiểm tra lại xem index có hợp lệ không (indisvalid = true) bằng query ở Mục 4.2.

6. Tổng kết Phần 4 & Chuẩn bị bước sang Phần 5

Chúc mừng bạn đã hoàn thành trọn vẹn Phần 4: Indexing & Tối ưu hóa truy vấn chuyên sâu (từ Bài 21 đến Bài 28). Hãy điểm lại các cột mốc kiến thức bạn đã làm chủ:

• Bài 21: Cơ chế Data Page 8KB, Tuple Header 23-byte, hiện tượng phân mảnh và kỹ thuật fillfactor phục vụ HOT Update.

• Bài 22: Cấu trúc cân bằng $B^+$Tree, quy tắc Leftmost Prefix, nguyên tắc Đẳng thức trước - Phạm vi sau và Covering Index (INCLUDE).

• Bài 23: Phân loại chuyên sâu họ Index: B-Tree, GIN, GiST, BRIN, Hash, SP-GiST.

• Bài 24: Kỹ thuật Partial Index tiết kiệm RAM và Expression-based Index với hàm IMMUTABLE.

• Bài 25: Đọc Execution Plan với EXPLAIN (ANALYZE, BUFFERS), phân tích chi phí I/O qua shared hit vs read.

• Bài 26: Bộ não của Query Planner, bảng thống kê pg_stats, MCV, Histograms và Extended Statistics (CREATE STATISTICS).

• Bài 27: Các lỗi kinh điển khiến Index bị vô hiệu hóa (ép kiểu ngầm, hàm vế trái, ký tự % đứng đầu).

• Bài 28: Kỹ thuật tạo Index không gián đoạn dịch vụ với CREATE INDEX CONCURRENTLY.

Bước sang Phần 5: Transaction, Concurrency & Locking

Bài 29 xem tiếp: Cơ chế MVCC (Multi-Version Concurrency Control) trong PostgreSQL hoạt động ra sao? — chúng ta sẽ đi sâu vào trái tim của tính nhất quán dữ liệu: cách PostgreSQL xử lý hàng ngàn kết nối đọc-ghi đồng thời mà không chặn lẫn nhau ("Readers never block writers, and writers never block readers"), ý nghĩa của các trường ẩn xmin, xmax và cơ chế chụp ảnh trạng thái Snapshot.