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

User gõ "Find similar tasks" vào TaskFlow search bar. Họ nhập "fix payment bug" — nhưng trong database không có task nào mang đúng title đó. Task thực sự đang mang tên "resolve checkout error". Keyword search trả về rỗng.

Đây không phải bug trong search implementation. Đây là giới hạn căn bản của keyword search: nó so khớp ký tự, không so khớp ý nghĩa. "Fix payment bug" và "resolve checkout error" có cùng semantic intent nhưng không có từ nào chung — ngoại trừ stopword.

Embedding và vector similarity là answer cho gap này. Và từ 2023 trở đi, PostgreSQL có thể làm điều đó native qua pgvector — không cần Pinecone, không cần Weaviate riêng, không cần thêm infrastructure. Bài này giải thích cơ chế hoạt động, cách setup production-ready với HNSW index, và bonus: Text-to-SQL — cho phép user hỏi database bằng ngôn ngữ tự nhiên.

1. Analogy — tìm bài hát tương tự

Hãy nghĩ đến hai cách tìm nhạc:

Cách 1 — keyword search: Tìm "bài hát tên có chữ love". Spotify trả về mọi bài có chữ "love" trong title — "Love Story", "Lovefool", "Lovesick Girls". Không thấy "Someone Like You" dù bài này có cùng cảm xúc nhớ nhung.

Cách 2 — embedding search: Tìm "bài hát có cảm xúc tương tự bài này". Hệ thống phân tích mood, tempo, lyrical theme, chord progression — rồi trả về bài có đặc điểm gần nhất trong không gian đặc trưng đó. "Someone Like You" xuất hiện dù không chứa từ nào giống bài gốc.

Embedding search hoạt động vì mô hình ML đã học cách biểu diễn ý nghĩa thành vector số. Hai văn bản cùng ý nghĩa sẽ cho ra vector gần nhau trong không gian N chiều.

2. Vấn đề semantic gap — keyword search không đủ

TaskFlow users dùng nhiều cách diễn đạt khác nhau cho cùng một loại công việc:

Full-text search với tsvector (bài 5 của module này) giải quyết được stemming và stop words, nhưng vẫn không bridge semantic gap. Từ điển FTS không biết "fix" và "resolve" là synonym trong context kỹ thuật, hay "payment" và "checkout" liên quan nhau trong domain e-commerce.

Semantic gap xuất hiện đặc biệt rõ trong:

• Ticket/task search: "database slow" vs "query performance" vs "latency spike"
• Knowledge base: "cannot login" vs "authentication failure" vs "access denied"
• Incident search: "server down" vs "service unavailable" vs "5xx errors"
• Docs search: "how to add user" vs "user creation API" vs "create account endpoint"

3. Embedding — vector representation của text

Embedding model là một neural network được train để chuyển đổi text thành vector số. Input là một đoạn text, output là một mảng float với số chiều cố định (dimension).

"fix payment bug"      → [0.0234, -0.0156, 0.0892, ..., -0.0341]  (1536 floats)
"resolve checkout error" → [0.0228, -0.0149, 0.0887, ..., -0.0338]  (1536 floats)
"bake chocolate cake"  → [-0.1203, 0.0892, -0.0445, ..., 0.1123]  (1536 floats)

Hai câu đầu có vector gần nhau — khoảng cách cosine nhỏ. Câu thứ ba thuộc domain hoàn toàn khác — khoảng cách lớn.

Model embedding phổ biến cho production:

Điểm quan trọng: chiều của embedding là property của model. text-embedding-3-small luôn cho ra 1536 floats. Khi tạo column trong PostgreSQL, dimension phải khớp với model bạn sẽ dùng.

4. pgvector — extension setup và schema

pgvector là PostgreSQL extension cho phép lưu và query vector trực tiếp trong database. Phiên bản v0.8 (released 2024) là production-ready với HNSW index nhanh hơn 9 lần so với IVFFlat của v0.5.

-- Bat pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Them embedding column vao tasks
ALTER TABLE tasks ADD COLUMN embedding vector(1536);
-- 1536 = OpenAI text-embedding-3-small
-- 768  = nomic-embed-text (Ollama local)
-- 384  = all-MiniLM-L6-v2 (sentence-transformers)

-- Verify: psql \d tasks se hien thi column type "vector(1536)"

Column vector(1536) lưu một mảng 1536 floating-point numbers. PostgreSQL validate dimension tại insert time — insert vector 768 chiều vào column vector(1536) sẽ báo lỗi ngay.

Thêm embedding cho table tasks trong TaskFlow:

-- Schema tasks (TaskFlow, simplified)
CREATE TABLE tasks (
  id          BIGSERIAL PRIMARY KEY,
  project_id  BIGINT NOT NULL REFERENCES projects(id),
  assignee_id BIGINT REFERENCES users(id),
  title       TEXT NOT NULL,
  description TEXT,
  status      TEXT NOT NULL DEFAULT 'todo',
  embedding   vector(1536)   -- NULL ban dau, fill sau khi goi embedding API
);

5. Ba distance operator — <->, <=>, <#>

pgvector cung cấp 3 operator đo khoảng cách giữa hai vector:

Với text embedding, <=> (cosine distance) là lựa chọn đúng vì:

• Normalize-invariant: cosine similarity chỉ quan tâm đến góc giữa hai vector, không phụ thuộc vào độ dài. Text "fix bug" và "fix bug fix bug fix bug" (repeat 3 lần) có cùng ý nghĩa — cosine similarity cao, L2 distance lại khác nhau vì vector dài hơn.
• Text embedding thường được normalize: OpenAI text-embedding-3-small output đã normalize về unit sphere — inner product và cosine similarity cho kết quả tương đương, nhưng cosine distance rõ ràng về semantic hơn.

-- Query: tim 5 task tuong tu "fix payment bug"
-- $1 la embedding vector cua query string, tinh truoc o app layer
SELECT
  id,
  title,
  embedding <=> $1 AS distance   -- 0.0 = hoan toan giong, 2.0 = hoan toan khac
FROM tasks
WHERE embedding IS NOT NULL
ORDER BY embedding <=> $1
LIMIT 5;

-- Ket qua vi du:
-- id=42  "resolve checkout error"    distance=0.0523
-- id=87  "payment gateway timeout"   distance=0.1034
-- id=156 "stripe webhook failing"    distance=0.1489
-- id=203 "billing service down"      distance=0.1892
-- id=318 "fix auth bug"              distance=0.2103

Giá trị distance từ 0 đến 2 với cosine distance (0 = identical, 2 = opposite). Trong thực tế, distance dưới 0.3 thường là "related", dưới 0.1 là "very similar".

6. HNSW index — graph-based approximate nearest neighbor

Không có index, query ORDER BY embedding <=> $1 phải scan toàn bộ bảng và tính cosine distance với mọi row — O(n). Với 1 triệu task, đây là 1 triệu phép tính vector distance.

HNSW (Hierarchical Navigable Small World) giải quyết bằng cách pre-build một graph:

Layer 2 (sparse):     [A] ---- [G]
                       |
Layer 1 (medium):     [A] -- [C] -- [G] -- [K]
                       |      |
Layer 0 (dense):  [A]-[B]-[C]-[D]-[E]-[F]-[G]-[H]-[I]-[J]-[K]

• Layer trên: ít node, mỗi node link với các node "globally similar". Navigate nhanh để đến vùng đúng.
• Layer 0: tất cả vector, link với neighbors gần nhất. Refine kết quả sau khi navigate từ trên xuống.
• Query: bắt đầu từ entry point ở layer trên cùng, navigate greedy (đi về phía node gần query nhất), xuống layer dưới, tiếp tục cho đến layer 0, trả về k nearest.

-- Tao HNSW index cho embedding column
CREATE INDEX idx_tasks_embedding
ON tasks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- m: so neighbor moi node luu trong graph
--    Default 16. Tang len 32-64 -> better recall, lon hon, build cham hon.
-- ef_construction: candidate set size khi build
--    Default 64. Tang len 128 -> better recall, build cham hon.
-- Tuning: prod thong thuong m=16-32, ef_construction=64-128

Để tune query-time accuracy:

-- ef_search: candidate set size khi query (default 40)
-- Tang len -> better recall, cham hon
SET hnsw.ef_search = 100;
SELECT ... ORDER BY embedding <=> $1 LIMIT 5;

So sánh HNSW vs IVFFlat (phiên bản trước của pgvector):

Với dataset dưới 10M vector và query-heavy workload (typical), HNSW là lựa chọn đúng.

7. Workflow end-to-end — từ task creation đến similarity search

7.1 Tính và lưu embedding

Khi user tạo task mới, app tính embedding cho title+description rồi lưu vào PostgreSQL:

# App layer (Python pseudo, dung openai SDK)
import openai
import psycopg2

def embed_task(task_id: int, title: str, description: str):
    # Gom title + description thanh 1 string de embed
    text = f"{title}. {description}" if description else title

    # Goi OpenAI embedding API
    response = openai.embeddings.create(
        input=text,
        model="text-embedding-3-small"
    )
    embedding = response.data[0].embedding
    # embedding = [0.0234, -0.0156, ..., 0.0892]  -- 1536 floats

    # Luu vao PG (pgvector nhan list of float truc tiep)
    conn.execute(
        "UPDATE tasks SET embedding = %s WHERE id = %s",
        (embedding, task_id)
    )

7.2 Backfill cho tasks hiện có

Nếu bảng tasks đã có data trước khi thêm embedding:

-- Tasks chua co embedding
SELECT id, title, description
FROM tasks
WHERE embedding IS NULL
ORDER BY id
LIMIT 100;  -- batch 100 row, process, roi tiep tuc

# Batch backfill -- chay offline, khong can real-time
def backfill_embeddings(batch_size: int = 100):
    while True:
        rows = db.query(
            "SELECT id, title, description FROM tasks "
            "WHERE embedding IS NULL ORDER BY id LIMIT %s",
            (batch_size,)
        )
        if not rows:
            break

        texts = [f"{r.title}. {r.description or ''}" for r in rows]
        # Batch API call -- giam cost va latency
        responses = openai.embeddings.create(
            input=texts,
            model="text-embedding-3-small"
        )

        for row, emb_data in zip(rows, responses.data):
            db.execute(
                "UPDATE tasks SET embedding = %s WHERE id = %s",
                (emb_data.embedding, row.id)
            )

7.3 Query similarity

-- "Find similar tasks" feature:
-- $1 = embedding cua user query string (tinh o app layer truoc khi goi PG)
-- $2 = project_id (scope search trong project hien tai)
-- $3 = distance threshold (chi lay task du tuong dong)

SELECT
  t.id,
  t.title,
  t.status,
  t.assignee_id,
  (embedding <=> $1) AS distance
FROM tasks t
WHERE
  t.project_id = $2
  AND t.embedding IS NOT NULL
  AND (embedding <=> $1) < 0.3   -- chi lay task tuong dong (distance < 0.3)
ORDER BY embedding <=> $1
LIMIT 10;

HNSW index sẽ được dùng tự động bởi query planner khi có ORDER BY embedding <=> $1 LIMIT N. Dùng EXPLAIN ANALYZE để verify:

EXPLAIN ANALYZE
SELECT id, title, embedding <=> $1 AS distance
FROM tasks
ORDER BY embedding <=> $1
LIMIT 5;
-- Nen thay "Index Scan using idx_tasks_embedding"
-- Neu thay "Seq Scan" thi index chua duoc dung -- kiem tra enable_indexscan

8. Text-to-SQL teaser — LLM + schema + few-shot

Embedding search giải quyết "find similar content". Một AI feature khác đang phổ biến nhanh trong 2024-2025 là Text-to-SQL: user hỏi bằng ngôn ngữ tự nhiên, LLM tự generate SQL query.

Ví dụ: user gõ "Show me top 10 tasks completed last month by each user" → LLM generate:

SELECT
  u.name,
  COUNT(*) AS completed_count
FROM tasks t
JOIN users u ON u.id = t.assignee_id
WHERE
  t.status = 'done'
  AND t.updated_at >= date_trunc('month', now() - interval '1 month')
  AND t.updated_at <  date_trunc('month', now())
GROUP BY u.id, u.name
ORDER BY completed_count DESC
LIMIT 10;

8.1 Pattern few-shot prompting

LLM cần biết schema và vài ví dụ để generate SQL đúng:

# Pseudo-code Text-to-SQL pattern
schema = """
CREATE TABLE projects (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  owner_id BIGINT REFERENCES users(id)
);
CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  project_id BIGINT REFERENCES projects(id),
  assignee_id BIGINT REFERENCES users(id),
  title TEXT NOT NULL,
  status TEXT DEFAULT 'todo',  -- 'todo','in_progress','done'
  updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);
"""

examples = """
Q: How many tasks per project?
A: SELECT project_id, COUNT(*) FROM tasks GROUP BY project_id;

Q: Top 5 most active users this week?
A: SELECT u.name, COUNT(*) AS task_count
   FROM tasks t JOIN users u ON u.id = t.assignee_id
   WHERE t.updated_at > now() - interval '7 days'
   GROUP BY u.id, u.name ORDER BY task_count DESC LIMIT 5;
"""

def text_to_sql(user_question: str) -> str:
    prompt = (
        f"Schema:\n{schema}\n\n"
        f"Examples:\n{examples}\n\n"
        f"Q: {user_question}\n"
        f"A:"
    )
    # LLM generate SQL completion
    sql = llm.complete(prompt, stop=["\n\n"]).strip()
    return sql

8.2 Validation pipeline — bắt buộc

Text-to-SQL mà không validate là security nightmare. LLM có thể generate DROP TABLE hoặc UPDATE users SET role='admin'. Pipeline validation bắt buộc trước khi execute:

import sqlparse
from sqlparse.sql import Statement
from sqlparse.tokens import Keyword, DDL, DML

def validate_sql(sql: str, allowed_tables: set) -> tuple[bool, str]:
    parsed = sqlparse.parse(sql)
    if not parsed:
        return False, "Cannot parse SQL"

    stmt = parsed[0]
    stmt_type = stmt.get_type()

    # Chi allow SELECT
    if stmt_type != 'SELECT':
        return False, f"Only SELECT allowed, got: {stmt_type}"

    # Extract table names, check against whitelist
    tables_used = extract_tables(stmt)
    for t in tables_used:
        if t not in allowed_tables:
            return False, f"Table not in schema: {t}"

    return True, "OK"

def execute_safe(sql: str, conn) -> list:
    # Execute trong read-only transaction voi timeout
    conn.execute("BEGIN READ ONLY")
    conn.execute("SET LOCAL statement_timeout = '5s'")
    try:
        result = conn.execute(sql).fetchmany(200)  # max 200 row
        conn.execute("COMMIT")
        return result
    except Exception as e:
        conn.execute("ROLLBACK")
        raise

Checklist validation:

1. Parse SQL — reject nếu không parse được.
2. Chỉ cho phép SELECT — reject INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE.
3. Chỉ cho phép table trong schema đã khai báo — reject table không tồn tại.
4. Execute trong BEGIN READ ONLY — database-level prevent DML.
5. SET LOCAL statement_timeout = '5s' — kill query nếu chạy quá lâu.
6. Limit result row (fetchmany(200)) — tránh OOM từ SELECT * FROM huge_table.

8.3 Tooling phổ biến

• LangChain SQLDatabaseToolkit: high-level abstraction, tự introspect schema, tự validate. Phù hợp prototyping nhanh.
• Vanna AI: RAG-augmented Text-to-SQL open source. Fine-tune trên schema cụ thể của bạn bằng cách feed DDL + example queries. Accuracy cao hơn zero-shot prompting.

9. Pitfall — storage, hybrid search, build time, model consistency

-- halfvec: half precision, 3 KB/row thay vi 6 KB
ALTER TABLE tasks ADD COLUMN embedding_half halfvec(1536);

-- Quantize xuong 8-bit binary (experimental, pgvector 0.7+):
-- bit(1536) = 192 bytes/row -- lossy nhung rat nho

-- Hybrid: combine vector top-50 + FTS top-50, rerank
WITH vector_results AS (
  SELECT id, (embedding <=> $1) AS vec_score
  FROM tasks
  ORDER BY embedding <=> $1
  LIMIT 50
),
fts_results AS (
  SELECT id, ts_rank(search_vector, plainto_tsquery('english', $2)) AS fts_score
  FROM tasks
  WHERE search_vector @@ plainto_tsquery('english', $2)
  ORDER BY fts_score DESC
  LIMIT 50
),
combined AS (
  SELECT
    COALESCE(v.id, f.id) AS id,
    COALESCE(1.0 - v.vec_score, 0.0) * 0.6    -- vector weight 60%
    + COALESCE(f.fts_score, 0.0) * 0.4          -- fts weight 40%
    AS combined_score
  FROM vector_results v
  FULL OUTER JOIN fts_results f ON f.id = v.id
)
SELECT t.id, t.title, c.combined_score
FROM combined c
JOIN tasks t ON t.id = c.id
ORDER BY combined_score DESC
LIMIT 10;

-- Ten column encode model va dimension
ALTER TABLE tasks ADD COLUMN embedding_te3s_1536 vector(1536);
-- te3s = text-embedding-3-small, 1536 = dimension
-- Neu sau nay doi model, add column moi, khong doi column cu

10. Applied — TaskFlow "Find similar tasks" end-to-end

Tổng hợp toàn bộ flow cho feature "Find similar tasks" trong TaskFlow:

User action:                    App layer:                    Database:
-------------------             -------------------           -------------------
User types query                Receive query string
"fix payment bug"
                                Call OpenAI embed API
                                → [0.023, -0.015, ...]        
                                                              SELECT id, title,
                                Pass vector $1 to PG          (emb <=> $1) AS dist
                                                              FROM tasks
                                                              WHERE project_id = $2
                                                              ORDER BY emb <=> $1
                                                              LIMIT 10
                                Receive ranked results
                                [id=42, dist=0.052,
                                 id=87, dist=0.103, ...]
Return to user:
"resolve checkout error"  ✓
"payment gateway timeout" ✓
"stripe webhook failing"  ✓

Ngoài TaskFlow, pattern tương tự áp dụng cho:

• Ops team: "Similar incident" — search incident report cũ theo semantic, không cần nhớ đúng keywords.
• Knowledge base search: "How do I reset my password" match với doc "Account recovery guide" dù không có từ chung.
• Support ticket routing: tự classify ticket mới bằng cách tìm ticket cũ tương tự + xem team nào đã handle.

11. Deep Dive

12. Tóm tắt

• Semantic gap: keyword search so chữ, không so ý nghĩa. "Fix payment bug" và "resolve checkout error" — keyword search miss, embedding search match.
• Embedding: neural network chuyển text thành vector N chiều. Text gần ý nghĩa → vector gần nhau trong không gian đó. OpenAI text-embedding-3-small cho ra 1536 floats.
• pgvector v0.8: PostgreSQL native extension, production-ready cho 1-10M vector, không cần Pinecone hay Weaviate riêng.
• Ba distance operator: <-> L2 (geometric), <=> cosine (text embedding, normalize-invariant), <#> negative inner product (pre-normalized). Cosine là lựa chọn đúng cho text.
• HNSW index: graph hierarchical, query nhanh hơn 9x so với IVFFlat cũ. Tham số m (neighbors per node) và ef_construction (build candidate set) điều chỉnh recall vs build time.
• Text-to-SQL: LLM nhận schema DDL + few-shot examples + user question → generate SQL. Bắt buộc có validation pipeline (only SELECT, only known tables, read-only transaction, timeout).
• Pitfall storage: 1536 floats × 4 bytes × 1M row = 6 GB. Dùng halfvec (PG 16+) giảm còn 3 GB. HNSW index thêm bằng đó.
• Hybrid search manual: vector top-50 UNION FTS top-50, weighted rerank → top 10. pgvector chưa có native hybrid.
• Model consistency: index và query phải cùng embedding model. Đổi model = re-embed toàn bộ bảng. Encode model name vào column name để tránh nhầm.

13. Tự kiểm tra

Bài tiếp theo: Mini-challenge — TaskFlow advanced search (FTS + JSONB + vector)