Sắp xếp: tạo index online trên bảng lớn Trung cấp

CREATE INDEX CONCURRENTLY không chặn ghi, nhưng nó không tự an toàn: chạy trong transaction thì lỗi, lỗi giữa chừng thì để lại index INVALID, và chạy lại với IF NOT EXISTS thì lặng lẽ bỏ qua.

Sắp xếp 7 bước dưới đây. Bấm Kiểm tra để biết bước nào sai chỗ và sai thì hỏng gì. Các hành vi trong phần giải thích đã chạy thử trên PostgreSQL 17.

Sắp xếp

Thêm index cho orders 80 triệu row đang nhận ghi

API "đơn gần đây của khách" chạy WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20, p99 900 ms. Bảng orders 80 triệu row, giờ cao điểm 3.000 ghi/giây, đã có index orders_customer_id_idx (customer_id). Migration chạy bằng Knex, role app có statement_timeout = 30s.

Sắp xếp các bước để có index mới mà không chặn ghi giây nào và không để lại rác.

Dùng ▲▼ để đổi chỗ từng bước, hoặc chạm ⠿ của một bước rồi chạm vào vị trí mới. Trên máy tính có thể kéo thả.

1. 1⠿Kiểm tra pg_index.indisvalid; nếu false thì DROP INDEX CONCURRENTLY rồi tạo lạiSELECT indexrelid::regclass, indisvalid FROM pg_index WHERE indrelid = 'orders'::regclass▲▼
2. 2⠿Session migration: SET statement_timeout = 0, lock_timeout dài hơn transaction bình thường dài nhất; kiểm tra không có transaction mở lâuSELECT pid, application_name, now() - xact_start FROM pg_stat_activity WHERE xact_start < now() - interval '5 min'▲▼
3. 3⠿ANALYZE nếu là expression index, rồi EXPLAIN xác nhận plan dùng index mới và p99 giảmIndex Scan Backward using orders_customer_created_idx, không còn Sort▲▼
4. 4⠿Sau vài ngày idx_scan của orders_customer_id_idx không tăng (trên primary và replica): DROP INDEX CONCURRENTLY▲▼
5. 5⠿EXPLAIN (ANALYZE, BUFFERS) query với tham số thật, chốt index cần là (customer_id, created_at)Plan hiện tại: Bitmap Index Scan on orders_customer_id_idx → Sort → Limit▲▼
6. 6⠿Theo dõi pg_stat_progress_create_index: phase, blocks_done / blocks_total, current_locker_pid▲▼
7. 7⠿CREATE INDEX CONCURRENTLY orders_customer_created_idx ON orders (customer_id, created_at) trong migration có config.transaction = false▲▼

Kiểm tra thứ tự↺ Xáo lại

Lock của từng loại DDL và index, xem Table-level locks.