Cái Postgres index lẽ ra tôi nên thêm sớm hơn mười tám tháng

Cái search chạy chín giây mà ai cũng làm như không sao#

Search candidate ở Remolution có một vấn đề mà nhìn lần đầu không giống vấn đề. Trang load được. Filter render xong. Bạn gõ hai từ, chọn một location, bấm Search — rồi cái spinner nó cứ ngồi đó lâu đến mức recruiter alt-tab qua Slack và quên luôn mình đang làm gì.

Trong team tụi tôi gọi nó là the nine-second query, mà gọi vậy là rộng lượng rồi. Có tenant chỉ hai giây, có tenant tới mười lăm. Phụ thuộc vào số candidate họ có và họ hay dựa vào filter nào. Một tenant enterprise trung bình lúc tôi nhận mảng search năm 2024 đã có hơn trăm ngàn dòng candidate.

Phản xạ đầu tiên — và tôi nhận cái này, vì là của tôi — là quăng cho nó một cái index.

Vì sao cái index hiển nhiên lại không phải cái đúng#

Query search join candidates với vài bảng liên quan và filter trên cỡ chục cột: location, role hiện tại, số năm kinh nghiệm, ngày last-contacted, tags, source. Vài filter xuất hiện trong gần như mọi query. Đa số filter còn lại thì gần như không bao giờ xuất hiện.

Tôi tạo composite index trên những cột tôi nghĩ là hot — location, role, experience. Chạy EXPLAIN. Planner cười vào mặt tôi. Đa số query vẫn sequential scan, vì kiểu kết hợp filter thật của recruiter không khớp với leading column của composite tôi viết — không đủ thường xuyên để planner thèm để ý.

Đây là phần không ai nói cho bạn nghe khi thêm index. Index không phải cách sửa cho query chậm — nó là một cái cược về việc user thật ra hỏi tới những row nào. Tôi đã cược dựa trên một tổ hợp filter sạch sẽ đọc ra từ schema. Recruiter thì đang cược trên một tổ hợp lộn xộn đến từ việc họ đi làm thật.

Nên tôi đi kéo query log về. Không phải slow-query log — mà là all-queries log, suốt một tuần. Rồi bucket theo tổ hợp filter. Hai pattern chiếm khoảng một nửa traffic của search. Không cái nào khớp với composite tôi đã viết.

Cái partial index, và cái cột tôi không biết là tôi cần#

Cả hai pattern hot đều dùng chung một thứ: filter archived_at IS NULL. Recruiter gần như không bao giờ tìm candidate đã archive. Cột đó nằm trong gần như mọi search, và đó chính là cột tôi bỏ qua vì nó không cảm giác giống một filter — nó cảm giác giống một default cho lành.

Cái fix là một partial index. Không phải trên những cột filter tôi đoán, mà trên bảng candidates giới hạn ở row active, với một composite nhỏ hơn cho những cột mà các pattern hot thật sự chia sẻ.

CREATE INDEX CONCURRENTLY idx_candidates_active_search
  ON candidates (tenant_id, location_id, last_contacted_at DESC)
  WHERE archived_at IS NULL;

Partial index nhỏ hơn nhiều so với full index tương đương, vì row archived chiếm phần không nhỏ ở những tenant đã chạy lâu. Index nhỏ nghĩa là phần lớn nó nằm hot trong memory. Planner picked nó lên ngay với các pattern hot, và median search rơi từ vài giây xuống một con số mà trên browser cảm giác như tức thì.

Thứ tự cột trong composite cũng quan trọng. tenant_id đứng trước, vì mọi query đều multi-tenant — đây là cột selective nhất tụi tôi có. location_id thứ hai, vì nó lọc tiếp những query có dùng tới. last_contacted_at DESC cuối, vì UI sort theo nó mặc định, và sort sẵn trong index thì né được một bước sort riêng.

Tôi cũng thêm một GIN index riêng cho cột tags dạng array, vì filter tag — khi có dùng — đụng tới những row khác với filter location. Một composite không phục vụ được cả hai, và cố nhồi nó phục vụ cả hai thì sẽ thành không phục vụ được cái nào.

Cách tôi đọc query plan đã thay đổi như thế nào#

Bài học đọng lại không phải về partial index. Tôi biết partial index tồn tại; tôi đã đọc Postgres docs về nó trước đó. Bài học là về việc dữ liệu cho quyết định index đến từ đâu.

Trước đó, tôi đọc schema, đọc slow-query log, rồi viết index bằng trực giác. Cách đó ổn với hobby project, nơi workload là bất cứ thứ gì bạn làm trong mười phút tới. Nó không ổn với một SaaS có cả trăm tenant enterprise, mà recruiter của mỗi tenant lại dùng product theo một kiểu hơi khác nhau.

Cái thật sự work:

• Log mọi search query trong một khoảng thời gian đại diện. Một tuần thường là đủ. Một ngày thì không.
• Bucket theo tổ hợp filter, không phải theo nội dung query. Hai recruiter gõ keyword khác nhau nhưng cùng filter — với planner là cùng một query.
• Nhìn xem filter nào xuất hiện trong đa số query — kể cả những cái không cảm giác giống filter. Soft-delete flag, status enum, tenant scope. Đó là những cột thuộc về predicate của partial index, hoặc thuộc về vị trí leading của composite.
• Chạy EXPLAIN ANALYZE trên đúng top-N tổ hợp filter sau khi build index. Không phải trên một query bạn vừa viết ra từ schema.

Postgres docs cho bạn syntax của partial index. Câu "user của tôi thật sự nhìn vào row nào" là việc của bạn, và cách duy nhất để trả lời nó cho thành thật là đi nhìn.

Điều tôi muốn nói với tôi của ngày trước#

Điều tôi muốn nói với tôi của ngày trước

Cái index đầu tiên bạn viết từ trực giác sẽ sai. Không sai đến mức thảm họa — chỉ sai vừa đủ để query vẫn chậm, rồi bạn bắt đầu nghĩ chắc vấn đề nằm chỗ khác.

Đọc query log trước khi đọc schema. Schema cho bạn biết cái gì có thể xảy ra. Log cho bạn biết cái gì đang xảy ra.

Vài tháng sau có một trang chậm khác — view timeline của candidate. Cùng một hình dạng vấn đề: một cái index nhìn schema thấy hiển nhiên, một workload thật lại muốn cái khác. Lần này tôi đi thẳng vào log, và index landed ngay lần đầu.

Đó là cái thắng nhỏ không bao giờ lên résumé của ai. Không phải tôi làm search nhanh gấp 10 lần. Mà là: tôi ngừng viết index bằng trực giác. Cộng dồn qua một năm làm việc với database, hiệu ứng nó lớn hơn bất kỳ cái nine-second query đơn lẻ nào.