Posted inDatabase

PostgreSQL pg_trgm: Xây Dựng Fuzzy Search, Truy Vấn Chịu Lỗi Chính Tả và Autocomplete với Trigram Index

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Bắt Đầu Nhanh — Chạy Fuzzy Search Trong 5 Phút

Sau nhiều năm làm việc với MySQL, PostgreSQL và MongoDB trên nhiều dự án khác nhau, tôi liên tục gặp phải cùng một vấn đề UX: người dùng gõ “postgress” thay vì “postgres” và không tìm thấy kết quả nào. Full-text search không giải quyết được vấn đề này — nó chỉ khớp token chính xác. Đó là lúc pg_trgm trở thành một trong những tính năng PostgreSQL tôi dùng nhiều nhất.

Đầu tiên, bật extension. Nó đi kèm với PostgreSQL, không cần cài đặt thêm:

-- Chạy với quyền superuser hoặc user có quyền CREATE
CREATE EXTENSION IF NOT EXISTS pg_trgm;

Kiểm tra xem nó đang hoạt động:

SELECT * FROM pg_extension WHERE extname = 'pg_trgm';

Bây giờ hãy kiểm tra hàm cốt lõi ngay:

SELECT similarity('postgress', 'postgres');
-- Trả về: 0.7

SELECT similarity('database', 'databse');
-- Trả về: 0.615

Hàm similarity() trả về số thực từ 0 đến 1. Giá trị trên ~0.3 thường có nghĩa là các chuỗi có liên quan. Đó là toàn bộ phần bắt đầu nhanh — chỉ hai dòng SQL và bạn đã có fuzzy matching.

Một truy vấn tìm kiếm thực tế trông như thế này:

SELECT title, similarity(title, 'postgress') AS score
FROM articles
WHERE similarity(title, 'postgress') > 0.3
ORDER BY score DESC
LIMIT 10;

Tìm Hiểu Sâu — Trigram Thực Sự Hoạt Động Như Thế Nào

Trigram là chuỗi gồm 3 ký tự liên tiếp. Extension chia chuỗi thành các trigram chồng lên nhau và so sánh các tập hợp đó. Điểm similarity = (trigram chung) / (tổng trigram duy nhất trong cả hai chuỗi).

-- Xem các trigram mà một chuỗi tạo ra
SELECT show_trgm('postgres');
-- {"  p"," po","gre","pos","res","sql","sql","ost","str","tgr"}
-- (PostgreSQL thêm khoảng trắng: "  p" và " po" là các trigram đầu tiên)

Hiểu điều này giúp bạn dự đoán khi nào fuzzy search hoạt động tốt và khi nào không. Chuỗi rất ngắn (1-2 ký tự) tạo ra hầu như không có trigram, nên điểm similarity trở nên không đáng tin cậy. Kinh nghiệm của tôi: fuzzy search bắt đầu hoạt động ổn định khi chuỗi có từ 4 ký tự trở lên.

Ba Hàm Similarity Bạn Cần Biết

Hầu hết các hướng dẫn chỉ đề cập đến similarity(), nhưng pg_trgm cung cấp cho bạn ba hàm riêng biệt:

  • similarity(a, b) — So sánh toàn bộ chuỗi. Tốt nhất cho việc khớp tên ngắn, tag hoặc mã sản phẩm khi bạn kỳ vọng toàn bộ chuỗi khớp gần đúng.
  • word_similarity(word, text) — Kiểm tra xem word có tương tự với bất kỳ đoạn liên tiếp nào trong text không. Đây là hàm tôi dùng cho autocomplete tìm kiếm theo từng chữ — người dùng gõ một phần từ và bạn khớp nó với tiêu đề dài hơn.
  • strict_word_similarity(word, text) — Tương tự word_similarity nhưng đoạn phải bao gồm nguyên từ. Chính xác hơn, ít false positive hơn.
-- Người dùng gõ "dockerr compose"
SELECT word_similarity('dockerr compose', 'Bắt đầu với Docker Compose trên Ubuntu');
-- Trả về: 0.571 — bắt được lỗi chính tả và khớp cụm từ một phần

SELECT similarity('dockerr compose', 'Bắt đầu với Docker Compose trên Ubuntu');
-- Trả về: 0.24 — quá thấp, hai chuỗi quá khác nhau

Với các ô tìm kiếm, word_similarity hầu như luôn cho kết quả tốt hơn similarity.

GIN hay GiST — Chọn Loại Nào

Không có index, pg_trgm phải quét toàn bộ bảng. Với 100k+ hàng, điều đó trở nên rất chậm. Hai loại index hoạt động với trigram:

-- GIN index (khuyến nghị cho hầu hết trường hợp)
CREATE INDEX idx_articles_title_gin ON articles USING GIN (title gin_trgm_ops);

-- GiST index (lựa chọn thay thế)
CREATE INDEX idx_articles_title_gist ON articles USING GIST (title gist_trgm_ops);

Đây là sự khác biệt thực tế từ kinh nghiệm của tôi:

  • GIN: Đọc nhanh hơn, index lớn hơn trên đĩa, chậm hơn khi xây dựng/cập nhật. Dùng cho các bảng chủ yếu là đọc.
  • GiST: Index nhỏ hơn, cập nhật nhanh hơn, truy vấn chậm hơn một chút. Phù hợp hơn cho các bảng ghi nhiều khi index liên tục được cập nhật.

Với hầu hết các kịch bản tìm kiếm (đọc nhiều), GIN thắng. Tôi đã thấy thời gian truy vấn giảm từ 800ms xuống dưới 5ms trên bảng 500k hàng sau khi thêm GIN trigram index.

Nâng Cao — Autocomplete, Tăng Tốc LIKE và Ngưỡng Similarity

Xây Dựng Autocomplete Tìm Kiếm Theo Từng Chữ

Đây là nơi pg_trgm thực sự tỏa sáng so với chỉ dùng LIKE '%keyword%'. Đây là pattern tôi dùng cho các endpoint autocomplete:

-- Autocomplete: người dùng gõ "kuber"
SELECT
  title,
  word_similarity('kuber', title) AS score
FROM articles
WHERE title % 'kuber'   -- toán tử % dùng ngưỡng đã cấu hình
OR title ILIKE '%kuber%'
ORDER BY score DESC, title
LIMIT 8;

Toán tử % là cách viết tắt của similarity() > threshold. Nó tự động tận dụng GIN index — bộ planner biết cách sử dụng nó.

Bạn có thể điều chỉnh ngưỡng toàn cục hoặc theo phiên:

-- Kiểm tra ngưỡng hiện tại
SHOW pg_trgm.similarity_threshold;
-- Mặc định: 0.3

-- Giảm để bắt được nhiều lỗi chính tả hơn (nhiều false positive hơn)
SET pg_trgm.similarity_threshold = 0.2;

-- Tăng để khớp chặt hơn
SET pg_trgm.similarity_threshold = 0.4;

Đối với word_similarity, toán tử khớp là <%:

-- Toán tử word_similarity: word <% text
SELECT title FROM articles
WHERE 'kuber' <% title
ORDER BY word_similarity('kuber', title) DESC
LIMIT 5;

Tăng Tốc LIKE và ILIKE với Trigram Index

Một lợi ích ít được biết đến của pg_trgm: nó cũng tăng tốc các truy vấn LIKE và ILIKE thông thường. Không có trigram index, WHERE title ILIKE '%postgres%' phải quét toàn bộ bảng. Với index, bộ planner có thể dùng nó:

-- Truy vấn này sẽ tự động dùng GIN trigram index của bạn
SELECT * FROM articles
WHERE title ILIKE '%postgresql performance%';

-- Kiểm tra với EXPLAIN
EXPLAIN SELECT * FROM articles WHERE title ILIKE '%postgresql%';
-- Kết quả nên hiển thị: Bitmap Index Scan on idx_articles_title_gin

Điều này đã đủ lý do để thêm trigram index dù bạn không dùng fuzzy search — nó giúp wildcard LIKE chạy nhanh.

Kết Hợp pg_trgm với Full-Text Search

Fuzzy search và full-text search giải quyết các vấn đề khác nhau. Tôi thường kết hợp chúng bằng UNION hoặc bằng cách tính điểm cả hai:

-- Tìm kiếm kết hợp: full-text trước, fuzzy dự phòng
SELECT title, ts_rank(to_tsvector('english', title), query) AS fts_score,
       similarity(title, 'postgress indexing') AS fuzzy_score
FROM articles,
     to_tsquery('english', 'postgres & indexing') AS query
WHERE to_tsvector('english', title) @@ query
   OR similarity(title, 'postgress indexing') > 0.25
ORDER BY (ts_rank(to_tsvector('english', title), query) + similarity(title, 'postgress indexing')) DESC
LIMIT 10;

Pattern này xử lý cả trường hợp chính xác (đúng chính tả, trả về kết quả FTS) lẫn trường hợp mờ (lỗi chính tả, vẫn trả về kết quả hữu ích).

Mẹo Thực Tế Từ Các Dự Án Thực Tế

Chú Ý Kích Thước Index

GIN trigram index lớn hơn đáng kể so với B-tree index — đôi khi gấp 3-5 lần kích thước dữ liệu của cột. Với cột text 1GB, hãy chuẩn bị cho GIN index 2-4GB. Theo dõi bằng:

SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE tablename = 'articles';

Chuỗi Ngắn Không Đáng Tin Cậy — Thêm Kiểm Tra Độ Dài

-- Chỉ fuzzy-match khi truy vấn đủ dài để có ý nghĩa
SELECT title FROM articles
WHERE
  CASE
    WHEN length('ab') < 4 THEN title ILIKE '%' || 'ab' || '%'
    ELSE similarity(title, 'ab') > 0.3
  END
LIMIT 10;

Ngoài ra, hãy bỏ qua fuzzy matching ở tầng ứng dụng khi truy vấn dưới 3 ký tự và dùng prefix matching thay thế.

Chỉ Index Các Cột Bạn Tìm Kiếm

Đừng thêm trigram index vào mọi cột text. Chỉ index các cột người dùng thực sự gõ vào: title, name, tags. Index cột content hoặc body bằng trigram thường không đáng — index sẽ rất lớn và truy vấn vẫn trả về quá nhiều kết quả. Với nội dung bài viết, hãy dùng full-text search (tsvector).

Dùng pg_trgm để Khớp Tag và Tên Người Dùng

Một trong những use case tôi thích nhất ngoài tìm kiếm bài viết: khớp tag người dùng nhập với danh sách tag chuẩn, hoặc fuzzy-match tên người dùng cho tính năng “bạn có muốn tìm?”.

-- "Bạn có muốn tìm?" cho tags
SELECT tag_name, similarity(tag_name, 'javascrpt') AS score
FROM tags
WHERE similarity(tag_name, 'javascrpt') > 0.4
ORDER BY score DESC
LIMIT 3;
-- Trả về: javascript (0.72), javaScript (0.72), ...

Kiểm Tra Ngưỡng Trước Khi Triển Khai

Ngưỡng mặc định 0.3 chỉ là điểm khởi đầu, không phải câu trả lời toàn năng. Hãy chạy kiểm tra kiểu này với dữ liệu thực trước khi triển khai:

-- Lấy mẫu dữ liệu để tìm ngưỡng phù hợp
SELECT
  threshold,
  COUNT(*) AS matches
FROM
  generate_series(0.1, 0.6, 0.05) AS threshold,
  articles
WHERE similarity(title, 'truy vấn tìm kiếm điển hình của bạn') > threshold
GROUP BY threshold
ORDER BY threshold;

Điều này cho bạn thấy bạn sẽ nhận được bao nhiêu kết quả ở mỗi mức ngưỡng. Quá nhiều kết quả ở 0.2, quá ít ở 0.5 — hãy tìm khoảng phù hợp với lĩnh vực của bạn.

Sau khi làm việc với MySQL, PostgreSQL và MongoDB trên nhiều dự án khác nhau, mỗi cái đều có thế mạnh riêng. Nhưng với tìm kiếm hướng người dùng có khả năng chịu lỗi chính tả, pg_trgm là một trong những tính năng mà PostgreSQL làm tốt hơn bất cứ thứ gì tôi từng dùng — và nó đã được tích hợp sẵn, không cần thêm service nào.

Share:
Tags: