~30 phútJSONB, full-text & pgvector… lượt xem

Mini-challenge M09 — TaskFlow advanced search: FTS + JSONB + pgvector trong 1 query

Build advanced search production-ready cho TaskFlow: combine 3 index (GIN FTS, GIN JSONB, B-tree due_at) trong 1 query Bitmap And, thêm pgvector semantic similarity và hybrid rerank manual. Bài cuối Tier 2.

TaskFlow vừa nhận yêu cầu từ enterprise customer: search box phải hỗ trợ 3 chiều cùng lúc — keyword tìm trong nội dung task (có dấu hoặc không dấu), filter theo label như urgent hay backend, và lọc theo deadline trong 7 ngày tới. Query phải chạy dưới 20ms trên 1 triệu task.

Đây là bài cuối Module 9 — và bài cuối Tier 2 của khoá học. Bài này tổng hợp toàn bộ module: JSONB operators (bài 2 và bài 3 của module này), GIN index (bài 3 của module này), FTS tiếng Việt với unaccent (bài 5 của module này), và pgvector HNSW (bài 6 của module này). Mục tiêu: một file SQL hoàn chỉnh, copy-paste-ready, với EXPLAIN output chứng minh Bitmap And combine 3 index.

Setup — extension + schema extend

Bắt đầu từ TaskFlow canonical schema. Extension và FTS config cần tạo trước:

-- =====================================================
-- Extension setup (chay mot lan)
-- =====================================================
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS vector;

-- Vietnamese FTS config: copy simple, add unaccent mapping
-- (detail co trong bai 5 cua module nay)
CREATE TEXT SEARCH CONFIGURATION IF NOT EXISTS vietnamese (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION vietnamese
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, simple;

Extend bảng tasks với 4 column mới: body, task_metadata JSONB, body_tsv generated tsvector, và embedding vector:

-- =====================================================
-- Extend tasks table (chay mot lan, idempotent)
-- =====================================================
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS body          TEXT;
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS task_metadata JSONB NOT NULL DEFAULT '{}';

-- Generated column: tu dong cap nhat khi title/body thay doi
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS body_tsv tsvector
  GENERATED ALWAYS AS (
    to_tsvector('vietnamese',
      coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

-- pgvector: 1536 dims cho text-embedding-3-small
-- NULL ban dau -- backfill tu external script (Step 5)
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS embedding vector(1536);

Tại sao GENERATED ALWAYS AS ... STORED? Column này được PostgreSQL tự tính lại mỗi khi title hoặc body thay đổi — không cần trigger, không cần app code. STORED nghĩa là giá trị lưu trên disk (không recompute mỗi SELECT), phù hợp để GIN index trực tiếp trên column này.

Step 1 — Generate sample dataset

Seed 100.000 task với title tiếng Việt realistic, task_metadata JSONB chứa labels array, và due_at trải trong 30 ngày tới:

-- =====================================================
-- Seed: 100k task voi title VN + metadata
-- Mat khoang 30-60 giay
-- =====================================================
INSERT INTO tasks (
  project_id, assignee_id, title, body,
  status, due_at, task_metadata, created_at, updated_at
)
SELECT
  (random() * 10 + 1)::int,               -- project_id 1-10
  (random() * 20 + 1)::int,               -- assignee_id 1-20

  -- Title tieng Viet diverse
  (ARRAY[
    'Trien khai feature thanh toan ' || gs,
    'Sua loi dang nhap nguoi dung ' || gs,
    'Toi uu toc do truy van ' || gs,
    'Viet unit test cho module gio hang ' || gs,
    'Review code pull request ' || gs,
    'Cap nhat tai lieu API ' || gs,
    'Fix payment gateway timeout ' || gs,
    'Deploy len moi truong staging ' || gs,
    'Kiem tra bao mat endpoint ' || gs,
    'Khac phuc loi xuat hoa don ' || gs,
    'Tich hop webhook thanh toan ' || gs,
    'Refactor service xu ly don hang ' || gs,
    'Monitor latency sau deploy ' || gs,
    'Cau hinh alert cho loi 5xx ' || gs,
    'Migrate du lieu sang schema moi ' || gs
  ])[floor(random() * 15) + 1],

  -- Body mo ta chi tiet hon
  (ARRAY[
    'Can kiem tra flow thanh toan end-to-end truoc khi len prod.',
    'Nguoi dung bao cao loi khi dang nhap bang Google OAuth.',
    'Query dashboard chay 3 giay, can them index phu hop.',
    'Stripe webhook tra ve 500 trong 2 phan tram request.',
    'Sau deploy 14h, p99 latency tang tu 120ms len 450ms.'
  ])[floor(random() * 5) + 1],

  -- Status phan bo realistic
  (ARRAY['todo','doing','done','archived'])[
    CASE
      WHEN random() < 0.45 THEN 1   -- 45% todo
      WHEN random() < 0.75 THEN 2   -- 30% doing
      WHEN random() < 0.95 THEN 3   -- 20% done
      ELSE 4                         -- 5% archived
    END
  ],

  -- due_at: 80% trong 30 ngay toi, 20% da qua han
  now() + ((random() * 40 - 8) * interval '1 day'),

  -- task_metadata JSONB: labels + priority + source
  jsonb_build_object(
    'labels', (
      CASE floor(random() * 8)::int
        WHEN 0 THEN '["urgent","backend"]'::jsonb
        WHEN 1 THEN '["urgent","frontend"]'::jsonb
        WHEN 2 THEN '["backend","performance"]'::jsonb
        WHEN 3 THEN '["frontend","ui"]'::jsonb
        WHEN 4 THEN '["urgent","security"]'::jsonb
        WHEN 5 THEN '["backend","migration"]'::jsonb
        WHEN 6 THEN '["devops","deployment"]'::jsonb
        ELSE        '["testing"]'::jsonb
      END
    ),
    'priority', (ARRAY['low','medium','high','critical'])[floor(random() * 4) + 1],
    'source',   (ARRAY['jira','linear','internal'])[floor(random() * 3) + 1]
  ),

  now() - (random() * interval '60 days'),
  now() - (random() * interval '30 days')
FROM generate_series(1, 100000) gs;

-- Verify
SELECT status, count(*) FROM tasks GROUP BY status ORDER BY count(*) DESC;
-- Expected: todo ~45k, doing ~30k, done ~20k, archived ~5k

SELECT count(*) FROM tasks WHERE task_metadata @> '{"labels":["urgent"]}';
-- Expected: ~25k task co label urgent

Sau khi insert xong, chạy ANALYZE để planner có statistics chính xác:

ANALYZE tasks;

Step 2 — 3 index GIN/B-tree + 1 HNSW

Tạo 4 index: GIN trên body_tsv (FTS), GIN trên task_metadata (JSONB), B-tree trên due_at, và HNSW trên embedding:

-- =====================================================
-- Index 1: GIN cho FTS (body_tsv generated column)
-- =====================================================
CREATE INDEX IF NOT EXISTS idx_tasks_body_tsv
  ON tasks USING gin(body_tsv);

-- =====================================================
-- Index 2: GIN jsonb_path_ops cho JSONB containment
-- jsonb_path_ops chi ho tro @> (containment) nhung nho hon
-- va nhanh hon gin_default_ops cho pattern nay
-- =====================================================
CREATE INDEX IF NOT EXISTS idx_tasks_metadata
  ON tasks USING gin(task_metadata jsonb_path_ops);

-- =====================================================
-- Index 3: B-tree cho due_at range filter
-- =====================================================
CREATE INDEX IF NOT EXISTS idx_tasks_due_at
  ON tasks(due_at);

-- =====================================================
-- Index 4: HNSW cho pgvector cosine similarity
-- m=16, ef_construction=64 la default production-safe
-- =====================================================
CREATE INDEX IF NOT EXISTS idx_tasks_embedding
  ON tasks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

Tại sao jsonb_path_ops thay vì default? jsonb_path_ops tạo GIN entry nhỏ hơn vì nó hash path + value thay vì index từng element riêng. Trade-off: chỉ hỗ trợ @> (containment), không hỗ trợ ? (key exists) hay ->>. Với pattern labels @> '["urgent"]', đây là lựa chọn đúng — index nhỏ hơn khoảng 30%, lookup nhanh hơn.

Kiểm tra index đã được tạo:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'tasks'
  AND indexname LIKE 'idx_tasks_%'
ORDER BY indexname;

Step 3 — Composite query 3-index Bitmap And

Query kết hợp 3 điều kiện: FTS với unaccent (keyword "deploy" match cả "triển khai", "deployment"), JSONB containment filter label "urgent", và B-tree range filter due_at trong 7 ngày tới:

-- =====================================================
-- Composite search query: 3 dieu kien, 3 index
-- $keyword: tu tim kiem (co dau/khong dau deu match)
-- =====================================================

SELECT
  id,
  title,
  status,
  due_at,
  task_metadata ->> 'priority'             AS priority,
  task_metadata -> 'labels'                AS labels,
  ts_headline(
    'vietnamese',
    body,
    websearch_to_tsquery('vietnamese', 'deploy'),
    'MaxWords=20, MinWords=5, StartSel=**, StopSel=**'
  )                                        AS snippet,
  ts_rank(body_tsv,
    websearch_to_tsquery('vietnamese', 'deploy'))
                                           AS rank
FROM tasks,
     websearch_to_tsquery('vietnamese', 'deploy') query
WHERE task_metadata @> '{"labels":["urgent"]}'
  AND body_tsv @@ query
  AND due_at BETWEEN now() AND now() + INTERVAL '7 days'
ORDER BY rank DESC, due_at ASC
LIMIT 20;

Lưu ý cách dùng FROM tasks, websearch_to_tsquery(...) query — đây là implicit cross join với 1 row (giá trị scalar), cho phép tái sử dụng query trong cả WHERE lẫn SELECT mà không cần viết lại websearch_to_tsquery nhiều lần. Pattern này giống CROSS JOIN total_tasks trong bài burndown chart (bài 7 Module 8 của khoá này).

websearch_to_tsquery thay vì to_tsquery vì nó accept input tự do từ user — không ném exception khi user gõ "deploy +feature" hay dấu ngoặc. Với tiếng Việt đã có unaccent trong config vietnamese, "trien khai" sẽ match "triển khai".

ts_headline generate snippet với từ match được highlight — chuỗi trả về là plain text (StartSel/StopSel dùng ** như Markdown bold), app frontend có thể render trực tiếp.

Step 4 — EXPLAIN verify Bitmap And

Chạy EXPLAIN để xác nhận planner dùng cả 3 index và combine bằng Bitmap And:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
  id, title, due_at,
  ts_rank(body_tsv,
    websearch_to_tsquery('vietnamese', 'deploy')) AS rank
FROM tasks,
     websearch_to_tsquery('vietnamese', 'deploy') query
WHERE task_metadata @> '{"labels":["urgent"]}'
  AND body_tsv @@ query
  AND due_at BETWEEN now() AND now() + INTERVAL '7 days'
ORDER BY rank DESC, due_at ASC
LIMIT 20;

Output tham khảo (số thực tế thay đổi theo hardware và phân bố data):

Limit  (cost=2341.89..2341.94 rows=20 width=96)
       (actual time=8.341..8.347 rows=20 loops=1)
  ->  Sort  (cost=2341.89..2342.01 rows=48 width=96)
            (actual time=8.339..8.343 rows=20 loops=1)
        Sort Key: (ts_rank(...)) DESC, due_at
        Sort Method: quicksort  Memory: 28kB
        ->  Bitmap Heap Scan on tasks
              (cost=312.45..2340.34 rows=48 width=96)
              (actual time=3.812..8.201 rows=47 loops=1)
              Recheck Cond: ((task_metadata @> '{"labels":["urgent"]}')
                             AND (body_tsv @@ query)
                             AND (due_at >= now())
                             AND (due_at <= (now() + '7 days'::interval)))
              Heap Blocks: exact=43
              Buffers: shared hit=312 read=28
              ->  BitmapAnd
                    (cost=312.45..312.45 rows=48 width=0)
                    (actual time=3.641..3.641 rows=0 loops=1)
                    ->  Bitmap Index Scan on idx_tasks_metadata
                          (cost=0.00..89.12 rows=2431 width=0)
                          (actual time=0.912..0.912 rows=2413 loops=1)
                          Index Cond: (task_metadata @>
                                       '{"labels":["urgent"]}'::jsonb)
                    ->  Bitmap Index Scan on idx_tasks_body_tsv
                          (cost=0.00..143.21 rows=1892 width=0)
                          (actual time=1.234..1.234 rows=1876 loops=1)
                          Index Cond: (body_tsv @@ query)
                    ->  Bitmap Index Scan on idx_tasks_due_at
                          (cost=0.00..78.34 rows=7124 width=0)
                          (actual time=0.891..0.891 rows=7089 loops=1)
                          Index Cond: ((due_at >= now()) AND
                                       (due_at <= (now() + '7 days')))
Planning Time: 1.423 ms
Execution Time: 8.891 ms

Đọc plan output:

BitmapAnd là node kết hợp 3 Bitmap Index Scan — mỗi index scan tạo ra một bitmap (tập hợp page ID thỏa điều kiện), BitmapAnd lấy giao của 3 bitmap. Chỉ những page chứa row thỏa cả 3 điều kiện mới được fetch từ heap.

Không có 3 index: Seq Scan toàn bộ 100k row với 3 filter → ước tính 800-1000ms. Với 3 index + Bitmap And: planner chỉ fetch ~43 heap block (Heap Blocks: exact=43) → dưới 10ms, cải thiện hơn 80 lần.

Heap Blocks: exact=43 nghĩa là không cần Recheck (exact mode, không mất mát). Nếu bitmap quá lớn cho work_mem, PostgreSQL chuyển sang "lossy" mode — Recheck Cond trở thành filter thực sự trên heap. Với dataset 100k row và filter hẹp, exact mode là bình thường.

Planner quyết định thứ tự các Bitmap Index Scan dựa trên selectivity ước tính: idx_tasks_metadata được ưu tiên trước vì filter @> thường rất selective (loại trừ phần lớn row ngay từ đầu).

Step 5 — Vector similarity query

pgvector cần embedding được tính trước ở app layer — PostgreSQL không gọi OpenAI API trực tiếp. Workflow:

App layer (Python pseudo, backfill):

import openai

def get_embedding(text: str) -> list[float]:
    resp = openai.embeddings.create(
        input=text,
        model="text-embedding-3-small"   # 1536 dims
    )
    return resp.data[0].embedding

# Backfill: tinh embedding cho tasks chua co
def backfill_embeddings(db, batch_size: int = 100):
    while True:
        rows = db.query(
            "SELECT id, title, body FROM tasks "
            "WHERE embedding IS NULL ORDER BY id LIMIT %s",
            (batch_size,)
        )
        if not rows:
            break
        texts = [f"{r.title}. {r.body or ''}" for r in rows]
        resp = openai.embeddings.create(
            input=texts,
            model="text-embedding-3-small"
        )
        for row, emb in zip(rows, resp.data):
            db.execute(
                "UPDATE tasks SET embedding = %s WHERE id = %s",
                (emb.embedding, row.id)
            )

Sau khi có embedding cho query string "fix payment bug" (tính ở app), truyền vào PostgreSQL qua parameterized query:

-- $1 = embedding vector cua query string
-- Vi du: tinh truoc: get_embedding("fix payment bug")
-- → [0.0234, -0.0156, ..., 0.0892]  (1536 floats)

SELECT
  id,
  title,
  body,
  status,
  embedding <=> $1    AS distance   -- cosine distance: 0=identical, 2=opposite
FROM tasks
WHERE embedding IS NOT NULL
ORDER BY embedding <=> $1
LIMIT 5;

-- Expected results (vi du):
-- id=1423  "Fix payment gateway timeout"          distance=0.0821
-- id=5891  "Khac phuc loi xu ly thanh toan"       distance=0.1034
-- id=2210  "Stripe webhook tra ve 500"            distance=0.1289
-- id=8834  "Tich hop cong thanh toan moi"         distance=0.1534
-- id=3341  "Kiem tra flow checkout end-to-end"    distance=0.1872

Kiểm tra EXPLAIN — phải thấy HNSW Index Scan, không phải Seq Scan:

EXPLAIN ANALYZE
SELECT id, title, embedding <=> $1 AS distance
FROM tasks
WHERE embedding IS NOT NULL
ORDER BY embedding <=> $1
LIMIT 5;

Limit  (cost=0.01..0.26 rows=5 width=40)
       (actual time=12.341..12.398 rows=5 loops=1)
  ->  Index Scan using idx_tasks_embedding on tasks
        (cost=0.01..4123.45 rows=79845 width=40)
        (actual time=12.339..12.394 rows=5 loops=1)
        Order By: (embedding <=> $1)
        Filter: (embedding IS NOT NULL)
Execution Time: 12.543 ms
-- Expected: 10-30ms cho 100k vector co embedding
-- ANN (approximate): HNSW co the bo sot mot it result o bien
-- ef_search mac dinh 40, tang len 100 cho recall tot hon

<=> là cosine distance operator (bài 6 của module này). HNSW Index Scan không scan tuần tự — nó navigate graph hierarchical để tìm approximate nearest neighbors trong O(log n). Với 1 triệu vector, thời gian ước tính 10-30ms (so với 2-5 giây nếu Seq Scan).

Bonus — Hybrid search rerank manual

Combine FTS top-50 và vector top-50 với weighted score — lấy top 10 hybrid. Pattern này giải quyết limitation của từng approach: FTS miss semantic match, vector miss exact keyword match:

-- $1 = embedding vector cua query string (tinh o app layer)
-- $2 = keyword string cho FTS (vi du: 'payment bug')
-- Weight: vector 60%, FTS 40% (dieu chinh theo use case)

WITH fts AS (
  SELECT
    id,
    ts_rank(body_tsv,
      websearch_to_tsquery('vietnamese', $2)) AS fts_score
  FROM tasks
  WHERE body_tsv @@ websearch_to_tsquery('vietnamese', $2)
  ORDER BY fts_score DESC
  LIMIT 50
),

vec AS (
  SELECT
    id,
    1.0 - (embedding <=> $1)   AS vec_score  -- cosine similarity: 1=identical
  FROM tasks
  WHERE embedding IS NOT NULL
  ORDER BY embedding <=> $1
  LIMIT 50
)

SELECT
  t.id,
  t.title,
  t.status,
  t.due_at,
  COALESCE(fts.fts_score,  0) * 0.4
    + COALESCE(vec.vec_score, 0) * 0.6       AS hybrid_score,
  COALESCE(fts.fts_score,  0)                AS fts_score,
  COALESCE(vec.vec_score,  0)                AS vec_score
FROM tasks t
LEFT JOIN fts USING (id)
LEFT JOIN vec USING (id)
WHERE fts.id IS NOT NULL OR vec.id IS NOT NULL
ORDER BY hybrid_score DESC
LIMIT 10;

Tại sao LEFT JOIN thay vì INNER JOIN? Task chỉ có trong FTS top-50 (không có embedding) hoặc chỉ trong vector top-50 (không có FTS match) đều nên xuất hiện trong kết quả. COALESCE(..., 0) cho score 0 khi không có ở một trong hai nhánh — điểm hybrid vẫn tính được từ nhánh còn lại.

Tuning weight: 0.4 / 0.6 là điểm xuất phát phổ biến. Tăng weight FTS khi domain có nhiều jargon kỹ thuật (acronym, tên sản phẩm) mà embedding model không biết. Tăng weight vector khi user query thường là câu ngắn hoặc paraphrase.

Limit 50 cho mỗi nhánh: compromise giữa recall và performance. Tăng lên 100 cho recall tốt hơn nhưng sort + join overhead lớn hơn. Giảm xuống 20 cho performance tốt hơn nhưng có thể miss kết quả tốt.

Deliverable — full SQL script

Tạo file module9_advanced_search.sql bằng cách ghép theo thứ tự:

Section A — Setup: DDL extension + FTS config + ALTER TABLE từ section "Setup" ở trên.
Section B — Seed: INSERT 100k task từ "Step 1".
Section C — Index: 4 lệnh CREATE INDEX từ "Step 2", thêm ANALYZE tasks; sau cùng.
Section D — Queries: composite query (Step 3), EXPLAIN (Step 4), vector query (Step 5), hybrid rerank (Bonus).

Checklist trước khi chạy:

-- Kiem tra extension da duoc cai
SELECT name, installed_version FROM pg_available_extensions
WHERE name IN ('unaccent','pg_trgm','vector');
-- Phai thay ca 3 co installed_version khac NULL

-- Kiem tra 4 index da ton tai
SELECT indexname FROM pg_indexes
WHERE tablename = 'tasks' AND indexname LIKE 'idx_tasks_%'
ORDER BY indexname;
-- Expected: idx_tasks_body_tsv, idx_tasks_due_at,
--           idx_tasks_embedding, idx_tasks_metadata

-- Kiem tra composite query co BitmapAnd
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM tasks,
  websearch_to_tsquery('vietnamese', 'deploy') q
WHERE task_metadata @> '{"labels":["urgent"]}'
  AND body_tsv @@ q
  AND due_at BETWEEN now() AND now() + INTERVAL '7 days'
LIMIT 20;
-- Phai thay: BitmapAnd -> 3x Bitmap Index Scan
-- Execution Time: duoi 20ms

Tự kiểm tra

Tại sao EXPLAIN output cho query composite 3 điều kiện hiển thị BitmapAnd thay vì một trong các node như Seq Scan hay Index Scan thông thường? Giải thích cơ chế Bitmap Index Scan và cách BitmapAnd kết hợp chúng.

▸

Bitmap Index Scan là strategy trung gian: thay vì trả về row trực tiếp (như Index Scan), nó tạo ra một bitmap — mảng bit tương ứng với page ID thỏa điều kiện. Mỗi Bitmap Index Scan trên một index tạo ra bitmap riêng.

BitmapAnd lấy intersection (AND logic) của nhiều bitmap: chỉ page ID xuất hiện trong tất cả bitmap mới được giữ lại. Bitmap Heap Scan đọc chính xác những page đó từ heap, rồi Recheck Cond lọc row-level.

Strategy này có lợi khi mỗi index riêng lẻ loại bỏ được một phần đáng kể row, nhưng không đủ selective để Index Scan thẳng từ một index duy nhất. Ba index GIN + B-tree kết hợp qua BitmapAnd giảm số page cần đọc từ heap xuống còn vài chục, thay vì hàng nghìn với Seq Scan.

Query hybrid rerank dùng `1.0 - (embedding <=> $1)` để chuyển cosine distance thành cosine similarity. Tại sao cần phép chuyển đổi này trước khi combine với fts_score? Điều gì xảy ra nếu dùng thẳng distance trong weighted sum?

▸

embedding <=> $1 là cosine distance: giá trị gần 0 nghĩa là hai vector giống nhau — score thấp = kết quả tốt. Nhưng ts_rank là relevance score: giá trị cao nghĩa là match tốt — score cao = kết quả tốt. Hai giá trị này có semantic ngược chiều nhau.

Nếu combine thẳng distance * 0.6 + fts_score * 0.4: task có vector distance thấp (rất similar) sẽ bị giảm điểm hybrid, trong khi task không similar (distance cao) lại được tăng điểm. Kết quả: ORDER BY hybrid_score DESC sẽ đẩy kết quả tệ nhất lên đầu.

Phép 1.0 - distance chuyển cosine distance thành cosine similarity: giá trị gần 1 nghĩa là rất similar. Sau đó cả hai score đều là "cao = tốt hơn", weighted sum có ý nghĩa đúng và ORDER BY DESC hoạt động đúng.

Column `body_tsv` được khai báo là `GENERATED ALWAYS AS (...) STORED`. Tại sao cần `STORED` thay vì để column tính toán on-the-fly? Khi nào `STORED` là lựa chọn bắt buộc?

▸

STORED nghĩa là PostgreSQL tính toán giá trị khi INSERT/UPDATE và lưu lên disk. Mỗi lần SELECT, giá trị đọc từ disk — không recompute.

VIRTUAL (tính on-the-fly) không được PostgreSQL hỗ trợ cho generated column với STORED data hiện tại — đây không phải một lựa chọn thực sự trong PostgreSQL syntax, chỉ có STORED.

Quan trọng hơn, STORED là bắt buộc để có thể tạo index trực tiếp trên column đó. GIN index trên body_tsv đòi hỏi giá trị được lưu vật lý — không thể index một virtual expression không có storage. Nếu không dùng generated column STORED, cần dùng expression index thủ công: CREATE INDEX ... ON tasks USING gin(to_tsvector(...)), nhưng khi đó phải đảm bảo mọi query đều dùng cùng expression để index được sử dụng.

Tại sao nên dùng `websearch_to_tsquery` thay vì `to_tsquery` hay `plainto_tsquery` cho input đến từ user? Khi nào `to_tsquery` vẫn phù hợp hơn?

▸

to_tsquery nhận biểu thức tsquery cú pháp chặt chẽ — nếu user gõ chuỗi có ký tự đặc biệt như dấu ngoặc không đóng, dấu & hay | không đúng vị trí, hàm ném exception ngay lập tức. Không phù hợp cho input trực tiếp từ user.

plainto_tsquery an toàn hơn — tất cả token được AND với nhau, không có syntax đặc biệt. Nhưng không cho phép user dùng OR, phrase search, hay negation.

websearch_to_tsquery parse cú pháp giống Google: dấu cách là AND, OR là OR, dấu trừ là NOT, dấu ngoặc kép là phrase. Không ném exception với input bất kỳ. Phù hợp nhất cho search box production vì vừa an toàn vừa cho user dùng operator cơ bản.

to_tsquery vẫn phù hợp khi input được construct từ app code — ví dụ programmatically build query từ tag list đã được validate, không phải input tự do từ user.

Hybrid search query dùng LIMIT 50 cho mỗi nhánh FTS và vector trước khi merge. Tại sao không lấy toàn bộ FTS match và toàn bộ vector match rồi merge? Trade-off của việc giảm LIMIT từ 50 xuống 10 là gì?

▸

Lấy toàn bộ FTS match và vector match sẽ có recall tốt nhất về lý thuyết, nhưng không khả thi trong production: FTS có thể match hàng nghìn row, vector luôn trả về kết quả theo distance nên về lý thuyết trả về toàn bộ bảng. Sort + merge + join trên hàng nghìn row tốn nhiều memory và CPU.

LIMIT 50 là heuristic: nếu kết quả tốt nhất không nằm trong top 50 của cả hai nhánh, khả năng cao nó cũng không đủ relevant để show cho user. Đây là recall/performance trade-off có chủ đích.

Giảm LIMIT từ 50 xuống 10: performance tốt hơn (merge ít row hơn, memory ít hơn), nhưng recall giảm đáng kể. Task chỉ đứng thứ 15 trong FTS nhưng đứng thứ 3 trong vector (hybrid score cao) sẽ bị miss hoàn toàn. Với use case search không critical (task management), LIMIT 20-50 thường là sweet spot. Với use case quan trọng hơn (medical document search), cần LIMIT cao hơn hoặc dùng dedicated vector database có native hybrid support.

⁂

Tier 3 tiếp theo: Module 10 — Scaling, replication & multi-tenant

Bài này có giúp bạn hiểu bản chất không?

← Bài trước

pgvector + Text-to-SQL — semantic search và AI query trong PostgreSQL