Full-text search

query แบบ LIKE '%search%' หา substring ที่ตรงเป๊ะได้ แต่ไม่เข้าใจภาษา บอกไม่ได้ว่า “running” กับ “ran” มีรากศัพท์ร่วมกัน ไม่ตัดคำที่ไม่มีความหมายอย่าง “the” ทิ้ง และจัดอันดับไม่ได้ว่า match ไหนดีกว่ากัน full-text search ของ PostgreSQL แก้ครบทั้งสามข้อ โดยย่อข้อความให้เป็นคำค้นที่ทำให้เป็นมาตรฐาน (normalized) แล้วจับคู่ query กับคำเหล่านั้น พร้อมให้คะแนนว่าแต่ละ row เข้ากันได้ดีแค่ไหน

ในบทนี้เราค้นหา table ของ articles ตามเนื้อความ (body text)

สองชนิดข้อมูลใหม่

full-text search แนะนำสองชนิดข้อมูล tsvector คือเอกสารที่ถูกแตกออกเป็น lexeme (คำรากศัพท์) ที่ทำให้เป็นมาตรฐานแล้ว พร้อมตำแหน่งของแต่ละคำ ส่วน tsquery คือนิพจน์การค้นหาของ lexeme ที่รวมกันด้วย operator & (and), | (or) และ ! (not)

CREATE TABLE articles (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  body  text NOT NULL
);

คุณแปลงข้อความเป็นชนิดเหล่านี้ด้วยฟังก์ชัน to_tsvector(config, text) สร้าง tsvector ส่วน to_tsquery(config, text) parse query ที่มีโครงสร้าง และ plainto_tsquery(config, text) เปลี่ยน input ดิบจากผู้ใช้ให้เป็น query โดย and คำเหล่านั้นเข้าด้วยกัน

SELECT to_tsvector('english', 'The cats were running quickly');

                to_tsvector
--------------------------------------------
 'cat':2 'quick':5 'run':4
(1 row)

สังเกตว่าเกิดอะไรขึ้น “The” และ “were” ถูกตัดทิ้งในฐานะ stop word “cats” กลายเป็น “cat” “running” กลายเป็น “run” และ “quickly” กลายเป็น “quick” ทั้งเอกสารและ query ผ่านการทำให้เป็นมาตรฐานแบบเดียวกัน ดังนั้น “running” ในการค้นหาจึง match กับ “ran” ในข้อความ

การจับคู่ด้วย operator @@

match operator @@ คืนค่า true เมื่อ tsvector ตอบสนองต่อ tsquery นี่คือหัวใจของทุก full-text query

psql

SELECT title
FROM articles
WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'running shoes');

Node.js

const res = await pool.query(
  `SELECT title
   FROM articles
   WHERE to_tsvector('english', body) @@ plainto_tsquery('english', $1)`,
  ['running shoes'],
);
console.log(res.rows);

Python

cur.execute(
    """
    SELECT title
    FROM articles
    WHERE to_tsvector('english', body) @@ plainto_tsquery('english', %s)
    """,
    ("running shoes",),
)
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx, `
    SELECT title
    FROM articles
    WHERE to_tsvector('english', body) @@ plainto_tsquery('english', $1)`,
    "running shoes")

Rust

let rows = sqlx::query(
    "SELECT title FROM articles \
     WHERE to_tsvector('english', body) @@ plainto_tsquery('english', $1)",
)
.bind("running shoes")
.fetch_all(&pool)
.await?;

        title
----------------------
 Choosing trail shoes
(1 row)

สำหรับ query ที่มีโครงสร้างให้ใช้ to_tsquery ซึ่งเข้าใจ boolean operator ตัวอย่าง to_tsquery('english', 'shoe & !leather') จะ match เอกสารเกี่ยวกับรองเท้าที่ไม่กล่าวถึงหนัง

การจัดอันดับผลลัพธ์

การ match เป็นแค่ใช่หรือไม่ใช่ แต่ผลการค้นหาต้องมีลำดับ ts_rank ให้คะแนนว่า tsvector match กับ tsquery ได้ดีแค่ไหน โดยคืนค่า real ที่คุณนำมาเรียงจากมากไปน้อยได้

psql

SELECT title,
       ts_rank(to_tsvector('english', body),
               plainto_tsquery('english', 'running shoes')) AS rank
FROM articles
WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'running shoes')
ORDER BY rank DESC;

Node.js

const res = await pool.query(
  `SELECT title,
          ts_rank(to_tsvector('english', body),
                  plainto_tsquery('english', $1)) AS rank
   FROM articles
   WHERE to_tsvector('english', body) @@ plainto_tsquery('english', $1)
   ORDER BY rank DESC`,
  ['running shoes'],
);

Python

cur.execute(
    """
    SELECT title,
           ts_rank(to_tsvector('english', body),
                   plainto_tsquery('english', %(q)s)) AS rank
    FROM articles
    WHERE to_tsvector('english', body) @@ plainto_tsquery('english', %(q)s)
    ORDER BY rank DESC
    """,
    {"q": "running shoes"},
)
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx, `
    SELECT title,
           ts_rank(to_tsvector('english', body),
                   plainto_tsquery('english', $1)) AS rank
    FROM articles
    WHERE to_tsvector('english', body) @@ plainto_tsquery('english', $1)
    ORDER BY rank DESC`,
    "running shoes")

Rust

let rows = sqlx::query(
    "SELECT title, \
            ts_rank(to_tsvector('english', body), plainto_tsquery('english', $1)) AS rank \
     FROM articles \
     WHERE to_tsvector('english', body) @@ plainto_tsquery('english', $1) \
     ORDER BY rank DESC",
)
.bind("running shoes")
.fetch_all(&pool)
.await?;

        title         |   rank
----------------------+-----------
 Choosing trail shoes | 0.0607927
 Marathon training    | 0.0303964
(2 rows)

การทำ index ให้การค้นหา

query ข้างต้นคำนวณ to_tsvector(body) ใหม่สำหรับทุก row ในทุกการค้นหา ซึ่งจะช้าลงเมื่อ table โตขึ้น มีสองแพตเทิร์นที่แก้ปัญหานี้ และใช้ร่วมกันได้ดีด้วย

ข้อแรก เก็บ tsvector ไว้ครั้งเดียวใน generated column เพื่อให้คำนวณตอนเขียน ไม่ใช่ตอนอ่าน:

ALTER TABLE articles
  ADD COLUMN search tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

จากนั้นใส่ GIN index บน column นั้นเพื่อให้การจับคู่ไม่ต้อง scan ทุก row:

CREATE INDEX idx_articles_search ON articles USING gin (search);

CREATE INDEX

ตอนนี้การค้นหาจะอ่าน column ที่คำนวณไว้ล่วงหน้าและใช้ index:

SELECT title, ts_rank(search, plainto_tsquery('english', 'running shoes')) AS rank
FROM articles
WHERE search @@ plainto_tsquery('english', 'running shoes')
ORDER BY rank DESC;

ใน pgAdmin: column search แบบ generated จะปรากฏใน table เหมือน column อื่น ๆ แต่เป็นแบบอ่านอย่างเดียว คุณแก้ค่าตรง ๆ ไม่ได้เพราะ Postgres ดึงค่ามาจาก title และ body

เคล็ดลับและข้อควรระวัง

• ส่ง language configuration อย่าง 'english' เสมอ configuration นี้ควบคุม stemming และ stop word การใช้ผิด (หรือใช้ค่า default simple ซึ่งไม่ทำทั้งสองอย่าง) จะเปลี่ยนว่า row ใดจะ match
• ใช้ configuration เดียวกันทั้งสำหรับ tsvector ที่เก็บไว้และสำหรับ query เอกสารที่สร้างด้วย english จะไม่ match กับ query ที่ parse ด้วย configuration อื่น
• คำนวณ tsvector ล่วงหน้าใน generated column สำหรับ table ใด ๆ ที่คุณค้นหาซ้ำ ๆ วิธีนี้ย้ายต้นทุนไปอยู่ตอนเขียน และปล่อยให้ GIN index ทำงานได้เต็มที่
• เลือกฟังก์ชัน query ให้ถูก: plainto_tsquery สำหรับ input ดิบจากผู้ใช้ (จะ and คำทั้งหมดเข้าด้วยกัน), to_tsquery เมื่อคุณต้องการ operator &, | และ ! ที่ระบุชัดเจน และ websearch_to_tsquery สำหรับวลีในเครื่องหมายคำพูดแบบ Google

🐘 Search engine ในกล่อง

สำหรับหลายแอปพลิเคชัน full-text search ของ PostgreSQL ตัดความจำเป็นในการมี search service แยกต่างหากออกไป คุณได้ stemming, stop word, boolean query และการจัดอันดับใน SQL ธรรมดา โดยทำ index ด้วย GIN หยิบ search engine เฉพาะทางมาใช้ก็ต่อเมื่อคุณโตจนเกินขีดของ PostgreSQL แล้ว ไม่ใช่ใช้เป็นค่าตั้งต้น

ข้อแลกเปลี่ยน

ตัวเลือก	Benefit	Cost
PostgreSQL full-text search	ไม่ต้องดูแล service เพิ่ม ข้อมูลกับ index อยู่ที่เดียวกัน การจัดอันดับ “ดีพอ” สำหรับหลายกรณี	tuning เรื่อง relevance และ faceted search ทำได้จำกัดกว่า engine เฉพาะทาง
Elasticsearch (หรือ search engine แยก)	relevance tuning ละเอียด รองรับ facet และ scale การค้นหาได้ดีกว่ามาก	ต้องรัน service เพิ่ม ต้องดูแลการ sync ข้อมูลระหว่างสอง system

ข้อผิดพลาดที่พบบ่อย

• หยิบ Elasticsearch มาใช้เป็นค่าเริ่มต้นโดยไม่เช็คก่อน — หลายระบบมี traffic และความต้องการด้าน relevance ที่ full-text search ของ PostgreSQL รองรับได้สบาย ๆ อยู่แล้ว ลองของที่มีอยู่แล้วก่อนเพิ่ม infrastructure ใหม่
• ลืมทำ GIN index บน column tsvector — ไม่มี index แปลว่าทุกการค้นหาต้อง scan ทุก row และคำนวณ to_tsvector ใหม่ทุกครั้ง ซึ่งช้าลงเรื่อย ๆ เมื่อ table โต
• ไม่อัปเดต search vector เมื่อข้อความต้นทางเปลี่ยน — ถ้าไม่ใช้ generated column หรือ trigger ที่คอยอัปเดต tsvector ให้ตรงกับ title/body ล่าสุด การค้นหาจะอ้างอิงข้อความเก่าที่ไม่ตรงกับสิ่งที่ผู้ใช้เห็นจริง

💡 ตัวอย่างจากของจริง

Hasura และ API ที่สร้างบน PostgREST — มักเปิดใช้ full-text search ของ PostgreSQL ตรง ๆ ผ่าน tsvector/tsquery แทนที่จะต่อ search service แยก เพราะ query layer เชื่อมกับฐานข้อมูลอยู่แล้ว

SaaS ขนาดเล็กถึงกลางจำนวนมาก — ย้ายออกจาก Elasticsearch cluster แยกต่างหากมาใช้ full-text search ของ PostgreSQL เมื่อพบว่าปริมาณการค้นหาจริงไม่ได้ต้องการ infrastructure ขนาดนั้น ลดทั้งต้นทุนและความซับซ้อนในการดูแล

to_tsvector ทำอะไรกับวลีอย่าง "The cats were running"?

เก็บข้อความไว้ตามตัวอักษรเพื่อจับคู่ substring แบบเป๊ะ ๆ ทำให้เป็นมาตรฐานเป็น lexeme ตัด stop word ทิ้งและทำ stemming คำ แปลข้อความเป็นอีกภาษาหนึ่ง เข้ารหัสข้อความเพื่อเก็บอย่างปลอดภัย

operator ใดทดสอบว่า tsvector ตอบสนองต่อ tsquery หรือไม่?

@> @@ ->> &&

วิธีที่แนะนำในการทำให้การค้นหา full-text ซ้ำ ๆ เร็วขึ้นคืออะไร?

รัน VACUUM ก่อนการค้นหาแต่ละครั้ง เก็บ tsvector ใน generated column แล้วเพิ่ม GIN index ทับ column นั้น ใช้ LIKE แทน เพิ่ม B-tree index บน column body

Submit

Mark as complete