Tìm kiếm PostgreSQL mọi nơi: full-text, trigram, chỉ mục partial

"Search everywhere" thực sự nghĩa là gì với công cụ nội bộ

Trên một màn hình nội bộ, “search everywhere” thường có nghĩa: “Giúp tôi tìm đúng bản ghi tôi đang nghĩ tới, nhanh, ngay cả khi tôi không nhớ chính xác.” Người dùng không đang duyệt, họ muốn nhảy thẳng tới một khách hàng, ticket, hóa đơn, hoặc thiết bị.

Đó là lý do tại sao tìm kiếm chậm còn khó chịu hơn tải trang chậm. Một lần tải trang xảy ra một lần. Tìm kiếm lặp đi lặp lại, thường khi ai đó đang nghe cuộc gọi hoặc làm triage. Nếu kết quả mất 2–3 giây, người dùng sẽ thay đổi truy vấn, xóa ký tự, thử từ khác, và bạn kết thúc với tải nhiều hơn và nhiều bực bội hơn.

Từ một ô tìm kiếm, người dùng kỳ vọng một gói hành vi: khớp một phần ("alex" tìm thấy "Alexander"), chịu lỗi chính tả nhỏ ("microsfot" vẫn tìm ra "Microsoft"), sắp xếp "kết quả tốt nhất" hợp lý (ID hoặc email chính xác lên đầu), thiên vị tần suất/độ mới một chút, và các bộ lọc áp theo mặc định (ticket mở, khách hàng hoạt động).

Vấn đề là một ô nhập thường che giấu nhiều ý định. Một agent có thể dán số ticket, gõ mẩu tên, tìm email, hoặc nhập số điện thoại. Mỗi ý định cần chiến lược khác nhau, chỉ mục khác nhau, và đôi khi quy tắc xếp hạng khác nhau.

Vì vậy đừng bắt đầu bằng chỉ mục. Hãy liệt kê vài ý định tìm kiếm thực sự của người dùng, và tách trường định danh (IDs, emails) khỏi trường mơ hồ (tên, subject) và văn bản dài (ghi chú).

Bắt đầu bằng việc đặt tên dữ liệu và hành vi tìm kiếm

Trước khi chọn chỉ mục, hãy viết ra người ta thực sự gõ gì. “PostgreSQL search everywhere” nghe như một tính năng duy nhất, nhưng thực tế thường là hỗn hợp của nhiều kiểu tìm kiếm rất khác nhau.

Công cụ nội bộ trộn lẫn các định danh “cứng” (order ID, ticket number, invoice code) với văn bản “mềm” (tên khách hàng, email, ghi chú, tag). Những nhóm đó có hành vi khác nhau trong PostgreSQL, nên xử chúng như nhau là con đường nhanh tới truy vấn chậm.

Tiếp theo, tách các hành vi:

• Tra cứu chính xác: ai đó tìm TCK-104883 mong đợi một kết quả chính xác.
• Tra cứu mơ hồ: ai đó gõ john smth muốn khớp rộng rãi trên tên (và có thể email) và sẽ quét một danh sách ngắn.
• Tìm kiếm dựa trên bộ lọc: ai đó chọn “Status = Open” và “Assigned to = Me” thực chất đang lọc; ô text chỉ là phụ.

Quyết định sớm xem kết quả có cần xếp hạng (kết quả tốt nhất trước) hay chỉ cần lọc. Xếp hạng quan trọng với ghi chú và mô tả dài. Với ID và email, xếp hạng thường khiến kết quả có vẻ ngẫu nhiên và tăng chi phí.

Một checklist ngắn thường đủ:

• Những trường nào được tìm kiếm hàng ngày?
• Các input nào là chính xác (IDs, mã), mơ hồ (tên), hay văn bản dài (ghi chú)?
• Những bộ lọc nào áp dụng hầu như cho mọi lần tìm?
• Bạn cần thứ tự "best match" hay bất kỳ khớp nào cũng chấp nhận được?
• Bảng sẽ lớn tới mức nào: hàng nghìn, hàng trăm nghìn, hay hàng triệu?

Nếu bạn đặt tên những quyết định đó trước, việc chọn chỉ mục sau này sẽ bớt đoán mò.

Mốc cơ bản: tra cứu chính xác và lý do ILIKE thường gây hại

Khóa các lợi ích dễ trước. Với nhiều màn hình nội bộ, một chỉ mục B-tree bình thường đã cho kết quả ngay lập tức với các tra cứu chính xác như IDs, số đơn, email và tham chiếu bên ngoài.

Nếu người dùng dán một giá trị chính xác, đảm bảo truy vấn thực sự là chính xác. WHERE id = ... hoặc WHERE email = ... có thể rất nhanh với chỉ mục bình thường. Một chỉ mục unique trên email thường có lợi hai lần: tốc độ và chất lượng dữ liệu tốt hơn.

Rắc rối bắt đầu khi “search everywhere” lặng lẽ biến thành ILIKE. Một truy vấn như name ILIKE '%ann%' có wildcard ở đầu, nên PostgreSQL không thể dùng B-tree thông thường. Nó phải kiểm tra nhiều hàng, và sẽ chậm dần theo kích thước bảng.

Tìm tiền tố có thể hoạt động, nhưng chỉ khi pattern được neo ở đầu: name ILIKE 'ann%'. Ngay cả khi vậy, chi tiết vẫn quan trọng (collation, xử lý chữ hoa/thường, và liệu bạn có index cùng biểu thức mà bạn query không). Nếu UI cần không phân biệt hoa thường, cách phổ biến là query lower(name) và tạo index phù hợp trên lower(name).

Cũng hữu ích khi thống nhất thế nào là “nhanh”:

• Khoảng 200 ms hoặc ít hơn cho phần công việc database khi cache ấm
• Dưới 1 giây end-to-end bao gồm mạng và render
• Không có trạng thái loading thấy rõ cho các tìm kiếm phổ biến

Với tiêu chuẩn đó, dễ quyết xem giữ các match chính xác và prefix có đủ hay đã đến lúc dùng full-text hoặc chỉ mục trigram.

Khi nào dùng full-text search là phù hợp

Full-text search phù hợp nhất khi người dùng gõ ngôn ngữ tự nhiên và mong hệ thống tìm các mục đúng, không chỉ khớp chính xác. Nghĩ tới message ticket, ghi chú nội bộ, mô tả dài, bài knowledge base, và log cuộc gọi.

Lợi ích lớn là xếp hạng. Thay vì trả về danh sách dài mà kết quả tốt nhất bị chôn, full-text search có thể sắp xếp theo relevance. Trong công cụ nội bộ, điều đó quan trọng: ai đó cần câu trả lời trong vài giây, không phải sau khi quét 50 hàng.

Ở mức cao, full-text search có ba phần di chuyển:

• Một tsvector (văn bản có thể tìm kiếm, lưu hoặc sinh động)
• Một tsquery (những gì người dùng gõ, chuyển thành truy vấn)
• Cấu hình ngôn ngữ (cách các từ được chuẩn hóa)

Cấu hình ngôn ngữ là nơi hành vi trở nên rõ ràng. PostgreSQL loại bỏ stop words phổ biến (như “the” hay “and”) và áp dụng stemming, nên “pay”, “paid” và “payment” có thể khớp. Điều này tuyệt vời cho ghi chú và tin nhắn, nhưng có thể làm người dùng bất ngờ khi họ tìm một từ ngắn phổ biến và không được trả về gì.

Từ đồng nghĩa là điểm quyết định khác. Chúng hữu ích khi công ty bạn dùng từ khác nhau cho cùng một khái niệm (ví dụ “refund” vs “chargeback”), nhưng cần duy trì cẩn thận. Giữ danh sách từ đồng nghĩa ngắn và dựa trên cách support/ops thực sự gõ.

Ví dụ thực tế: tìm “can’t login after reset” nên trả về ticket có nội dung “cannot log in after password reset” dù cách diễn đạt khác nhau. Hành vi “tìm liên quan” đó là điều full-text search sinh ra để làm, và thường là lựa chọn tốt hơn so với cố ép ILIKE thành search engine.

Khi nào chỉ mục trigram thắng

Chỉ mục trigram là lựa chọn mạnh khi người dùng gõ mảnh, viết sai, hoặc chỉ nhớ “đại khái”. Chúng rất tốt trên trường ngắn mà full-text quá nghiêm: tên người, tên công ty, subject ticket, SKU, mã đơn hàng, mã sản phẩm.

Trigram là các đoạn 3 ký tự. PostgreSQL so sánh hai chuỗi bằng bao nhiêu trigram chúng chia sẻ. Vì vậy nó có thể khớp "Jon Smth" với "John Smith", hoặc "ACM" với "ACME", và tìm khi truy vấn nằm giữa một từ.

Đây thường là con đường nhanh nhất để có một ô “PostgreSQL search everywhere” dễ chịu khi nhiệm vụ là “tìm đúng hàng”, không phải “tìm tài liệu về chủ đề”.

Nơi nó hơn full-text

Full-text tốt cho văn bản dài và xếp hạng theo ý nghĩa, nhưng không xử lý tự nhiên chuỗi con và lỗi nhỏ trên trường ngắn. Trigram được xây cho loại mơ hồ này.

Giữ chi phí ghi hợp lý

Chỉ mục trigram lớn hơn và thêm overhead khi ghi, nên hãy chọn lọc. Index các cột mà người dùng thực sự dùng:

• Name, email, company, username
• Các định danh ngắn (SKU, code, reference)
• Trường title ngắn gọn (không phải ghi chú lớn)

Nếu bạn nêu được các trường chính xác người trong team gõ vào ô tìm kiếm, thường có thể giữ kích thước chỉ mục trigram nhỏ và nhanh.

Chỉ mục partial cho các bộ lọc người thực sự dùng

Một ô “search everywhere” thường có các mặc định ẩn. Người tìm kiếm trong workspace, trên mục hoạt động, loại trừ đã xóa. Nếu các bộ lọc đó xuất hiện trong hầu hết câu hỏi, hãy làm cho trường hợp phổ biến nhanh bằng cách index chỉ các hàng khớp.

Partial index là chỉ mục bình thường có WHERE clause. PostgreSQL giữ nó nhỏ hơn vì chỉ lưu các mục cho hàng bạn quan tâm nhất. Điều đó thường có nghĩa là ít page hơn để đọc và tỷ lệ cache hit tốt hơn.

Mục tiêu partial-index phổ biến gồm hàng active (status = 'active'), soft deletes (deleted_at IS NULL), tenant scoping, và cửa sổ “gần đây” (ví dụ 90 ngày gần nhất).

Chìa khóa là khớp UI. Nếu màn hình luôn ẩn các dòng đã xóa, truy vấn của bạn nên luôn bao gồm deleted_at IS NULL, và partial index của bạn phải dùng đúng điều kiện đó. Những sai khác nhỏ, như dùng is_deleted = false ở chỗ này và deleted_at IS NULL ở chỗ kia, có thể khiến planner không dùng được index.

Partial index cũng hoạt động cùng với full-text và trigram. Ví dụ, index tìm kiếm toàn văn chỉ cho hàng không bị xóa giữ kích thước index trong tầm kiểm soát.

Đổi lấy: partial index kém hữu ích cho truy vấn hiếm. Nếu ai đó thỉnh thoảng tìm trên bản ghi đã xóa hoặc toàn bộ workspace, PostgreSQL có thể quay lại plan chậm hơn. Xử lý bằng đường dẫn admin-only riêng, hoặc thêm index thứ hai chỉ khi truy vấn hiếm trở nên phổ biến.

Kết hợp các phương án mà không biến tìm kiếm thành bí ẩn

Hầu hết đội cuối cùng trộn kỹ thuật vì một ô tìm kiếm phải xử lý nhiều ý định. Mục tiêu là làm cho thứ tự thao tác rõ ràng để kết quả có cảm giác dự đoán được.

Một thứ tự ưu tiên đơn giản giúp, dù bạn triển khai bằng nhiều truy vấn riêng hay một truy vấn với logic CASE rõ ràng.

Thứ tự ưu tiên dễ đoán

Bắt đầu nghiêm ngặt, rồi nới dần nếu cần:

• Exact match trước (IDs, email, ticket number, SKU) dùng B-tree
• Prefix match tiếp theo nếu hợp lý
• Trigram match sau đó cho lỗi và mảnh trên tên và tiêu đề
• Full-text search cuối cùng cho ghi chú dài, mô tả, và nội dung tự do

Khi bạn tuân theo cùng một thang này, người dùng sẽ hiểu ô tìm kiếm “nghĩa là gì.” Họ ngừng nghĩ hệ thống hỏng khi “12345” tìm ticket ngay lập tức trong khi “refund policy” lại tìm trong văn bản dài và mất lâu hơn.

Lọc trước, rồi làm mờ

Tìm kiếm mờ trở nên đắt khi nó phải xem toàn bộ bảng. Hẹp tập ứng viên bằng các bộ lọc người dùng thực sự dùng (status, assigned team, date range, account), rồi chạy trigram hoặc full-text trên phần còn lại. Ngay cả chỉ mục trigram nhanh cũng có thể cảm thấy chậm nếu bạn bắt nó chấm điểm hàng triệu hàng.

Cũng đáng để viết một quy tắc ngắn cho đồng nghiệp không kỹ thuật hiểu, ví dụ: “Chúng tôi khớp số ticket chính xác, sau đó tên khách hàng với chịu lỗi chính tả, rồi tìm trong ghi chú.” Định nghĩa chung này ngăn tranh cãi sau này về lý do một hàng xuất hiện.

Từng bước: chọn phương pháp và triển khai an toàn

Một ô tìm kiếm nhanh là tập hợp các quyết định nhỏ. Viết chúng ra trước, công việc database sẽ bớt phức tạp.

1. Xác định input. Là chỉ một ô, hay một ô cộng bộ lọc (status, owner, date range)?
2. Chọn kiểu khớp theo trường. IDs và mã muốn match chính xác. Tên và email thường cần prefix hoặc fuzzy. Ghi chú và mô tả dài phù hợp với tìm kiếm ngôn ngữ tự nhiên.
3. Thêm chỉ mục phù hợp và xác nhận chúng được dùng. Tạo index, rồi kiểm tra truy vấn thực với EXPLAIN (ANALYZE, BUFFERS).
4. Thêm xếp hạng hoặc sắp xếp phù hợp với ý định. Nếu người dùng gõ “invoice 1042”, kết quả chính xác nên lên đầu. Nếu họ gõ tên sai chính tả, xếp hạng theo similarity nên thắng.
5. Test với truy vấn thực. Thử sai chính tả, từ rất ngắn (như “al”), văn bản dán dài, input rỗng, và chế độ “chỉ bộ lọc”.

Để triển khai an toàn, thay đổi từng thứ một và giữ khả năng rollback dễ. Với chỉ mục mới trên bảng lớn, ưu tiên CREATE INDEX CONCURRENTLY để không chặn ghi. Nếu có thể, bật sau feature flag và so sánh độ trễ trước và sau.

Mẫu thực tiễn cho “PostgreSQL search everywhere”: exact match trước (nhanh và chính xác), trigram cho các trường “con người” hay gõ sai, và full-text cho văn bản dài cần xếp hạng.

Ví dụ thực tế: một ô tìm kiếm trong support admin panel

Hãy tưởng tượng một support admin panel nơi team có một ô tìm kiếm, nhưng mong nó tìm khách hàng, ticket, và cả ghi chú. Đây là bài toán “một input, nhiều nghĩa” kinh điển.

Chiến thắng đầu tiên là làm cho ý định hiển thị mà không gây friction. Nếu truy vấn trông như email hoặc số điện thoại, xử như tra cứu khách hàng. Nếu trông như ticket ID (ví dụ, "TKT-10482"), dẫn thẳng tới tickets. Còn lại fallback sang tìm kiếm văn bản trên subject và notes của ticket.

Với tra cứu khách hàng, chỉ mục trigram thường cho cảm giác tốt nhất. Tên và company lộn xộn, và người ta gõ mảnh. Chỉ mục trigram làm các tìm kiểu “jon smi” hoặc “acm” nhanh và khoan dung.

Với ghi chú ticket, dùng full-text search. Ghi chú là câu hoàn chỉnh, và thường muốn kết quả liên quan, không chỉ “chứa chuỗi con”. Xếp hạng hữu ích khi hàng chục ticket nhắc cùng từ khóa.

Bộ lọc quan trọng hơn nhiều đội tưởng. Nếu agents sống trong “open tickets”, thêm partial index chỉ cho hàng open. Tương tự với “active customers”. Điều đó giữ chỉ mục nhỏ và làm đường phổ biến nhanh.

Truy vấn rất ngắn cần quy tắc, nếu không database sẽ làm việc tốn kém cho nhiễu:

• 1–2 ký tự: hiển thị ticket mở gần đây và khách hàng cập nhật gần đây
• 3+ ký tự: chạy trigram cho trường khách hàng và full-text cho nội dung ticket
• Không rõ ý định: hiển thị danh sách hỗn hợp, nhưng giới hạn mỗi nhóm (ví dụ 10 khách hàng và 10 ticket)

Sai lầm phổ biến khiến tìm kiếm chậm hoặc khó hiểu

Hầu hết lỗi “tại sao tìm kiếm chậm?” là tự gây ra. Mục tiêu không phải index mọi thứ, mà là index những gì người dùng thực sự làm.

Bẫy thường gặp là thêm index vào nhiều cột “phòng trường hợp”. Đọc có thể cải thiện, nhưng mỗi insert/update giờ phải làm nhiều việc hơn. Trong công cụ nội bộ nơi bản ghi thay đổi suốt ngày (ticket, đơn hàng, user), tốc độ ghi quan trọng.

Một sai lầm khác là dùng full-text khi bạn thực sự cần tra cứu chịu lỗi chính tả trên tên hoặc email. Full-text tốt cho tài liệu và mô tả; nó không phải giải pháp cho “Jon” vs “John” hoặc “gmail.con” vs “gmail.com.” Thường đó là việc của trigram.

Bộ lọc cũng có thể âm thầm phá vỡ plan. Nếu hầu hết tìm kiếm có bộ lọc cố định (như status = 'open' hoặc org_id = 42), chỉ mục tốt nhất có thể là partial index phù hợp điều kiện đó. Nếu bạn quên điều này, PostgreSQL có thể quét nhiều hàng hơn bạn nghĩ.

Một số lỗi hay gặp:

• Thêm nhiều chỉ mục mà không đo lường chi phí ghi
• Hi vọng full-text sẽ xử lý autocomplete chịu lỗi chính tả
• Bỏ qua cách bộ lọc phổ biến thay đổi index có thể dùng
• Test trên dữ liệu nhỏ, sạch thay vì tần suất từ thực tế (từ phổ biến vs ID hiếm)
• Sắp xếp theo cột không có index hỗ trợ, buộc phải sort chậm

Ví dụ: màn support tìm ticket theo subject, customer name, và ticket number, rồi sắp xếp theo latest activity. Nếu latest_activity_at không được index cho tập lọc (ví dụ, open tickets), sort đó có thể xoá hết tốc độ bạn có được từ chỉ mục tìm kiếm.

Kiểm tra nhanh trước khi phát hành

Trước khi gọi tính năng “search everywhere” hoàn thành, cụ thể hoá hành vi bạn hứa với người dùng.

• Người dùng có tìm bản ghi bằng định danh chính xác (ticket number, email)?
• Họ mong khớp chịu lỗi chính tả không?
• Họ muốn kết quả được xếp hạng từ ghi chú và mô tả dài không?

Nếu bạn trộn nhiều chế độ, quyết định chế độ nào thắng khi xung đột.

Rồi xác định 2–3 trường chi phối phần lớn tìm kiếm. Nếu 80% tìm kiếm theo email, tên và ticket ID, tối ưu những trường đó trước và coi phần còn lại là phụ.

Checklist ngắn trước khi phát hành:

• Xác nhận chế độ khớp chính cho mỗi trường (tra cứu chính xác, fuzzy, hay text có xếp hạng)
• Liệt kê các bộ lọc người dùng áp hàng ngày và đảm bảo index khớp các kết hợp đó
• Quyết định xử lý truy vấn rất ngắn và input rỗng (ví dụ yêu cầu 2–3 ký tự cho tìm mờ; hiển thị “recent” với input rỗng)
• Làm cho thứ tự dễ giải thích: mới nhất, khớp văn bản tốt nhất, hoặc quy tắc kết hợp đơn giản

Cuối cùng, test với kích thước dữ liệu và thời gian thực, không chỉ độ đúng. Một truy vấn tưởng như tức thời với 1,000 hàng có thể chậm với 1,000,000.

Bước tiếp theo: biến kế hoạch thành màn tìm kiếm nội bộ nhanh

Một ô tìm kiếm giữ được nhanh khi cả team đồng ý nó nên làm gì. Viết quy tắc bằng ngôn ngữ thường: “khớp” nghĩa là gì (chính xác, prefix, chịu lỗi chính tả), trường nào được tìm, và bộ lọc thay đổi tập kết quả ra sao.

Giữ một bộ test nhỏ gồm các truy vấn thực và coi nó như suite hồi quy. 10–20 truy vấn thường đủ: vài tên phổ biến, vài email mảnh, một lỗi chính tả, một đoạn ghi chú dài, và một trường hợp “không có kết quả”. Chạy chúng trước và sau thay đổi để công việc tối ưu không vô tình làm hỏng độ liên quan.

Nếu bạn xây công cụ nội bộ với AppMaster (appmaster.io), nên định nghĩa các quy tắc tìm kiếm cùng mô hình dữ liệu và business logic để hành vi UI và lựa chọn database không bị lệch khi yêu cầu thay đổi.