Postgres全文搜索功能

当构建一个Web应用时，经常被要求加上搜索功能。其实有时候我们也不知道我要搜索个啥，反正就是要有这个功能。搜索确实很重要的特性，这也是为什么像Elasticsearch和Solr这样基于Lucene的数据库越来越流行。这两个是好用，但是在构建Web应用时，有时候感觉像是杀鸡用牛刀。所以我们需要选择轻量好用的东西来搜索。

如何定义足够好用？ 足够好用的搜索引擎需要有以下几点特征：

• Stemming 关键词，词干
• Ranking/Boost 排名和权重分配
• 多语言支持
• 错误纠正/模糊查询
• 口音支持 类似我们的一二三四声

非常幸运，PostgreSql支持以上所有特征。
这篇文章旨在帮助以下的人群：

• 用了PG数据库，不想再加一个搜索引擎
• 用了其他数据库，想有一个更好的全文检索体验

在这片文章，我们将基于以下表和数据逐步的阐述PG中的全文搜索功能。

CREATE TABLE author(
   id SERIAL PRIMARY KEY,
   name TEXT NOT NULL
);

CREATE TABLE post(
   id SERIAL PRIMARY KEY,
   title TEXT NOT NULL,
   content TEXT NOT NULL,
   author_id INT NOT NULL references author(id) 
);

CREATE TABLE tag(
   id SERIAL PRIMARY KEY,
   name TEXT NOT NULL 
);

CREATE TABLE posts_tags(
   post_id INT NOT NULL references post(id),
   tag_id INT NOT NULL references tag(id)
 );

INSERT INTO author (id, name) 
VALUES (1, 'Pete Graham'), 
       (2, 'Rachid Belaid'), 
       (3, 'Robert Berry');

INSERT INTO tag (id, name) 
VALUES (1, 'scifi'), 
       (2, 'politics'), 
       (3, 'science');

INSERT INTO post (id, title, content, author_id) 
VALUES (1, 'Endangered species', 
        'Pandas are an endangered species', 1 ), 
       (2, 'Freedom of Speech', 
        'Freedom of speech is a necessary right', 2), 
       (3, 'Star Wars vs Star Trek', 
        'Few words from a big fan', 3);


INSERT INTO posts_tags (post_id, tag_id) 
VALUES (1, 3), 
       (2, 2), 
       (3, 1);

以上是一个比较典型的Blog类型的数据库。有post, post有title和content。post通过外键与author建立关系。post也有自己的多个tag。

什么是Full-Text Search全文检索

首先让我们看看定义

In text retrieval, full-text search refers to techniques for searching a single computer-stored document or a collection in a full-text database. The full-text search is distinguished from searches based on metadata or on parts of the original texts represented in databases.
-- Wikipedia

这段定义指出了一个重要的概念-document。当你执行一个查询的时候，你实际上是为了寻找一些具有实际含义的实体。那些就是documents。PostgreSQL文档解释的有点耐人寻味

A document is the unit of searching in a full-text search system; for example, a magazine article or email message.
-- Postgres documentation

document 能够跨越多个表，它代表的是一个逻辑上的独立个体。

建立我们的document

在上一节我们介绍了document的概念，document和我们的表结构没关系，它只和数据有关系。以我们的数据库为例，一个document可以有以下几个field组成：

• post.title
• post.content
• 该post的author.name
• 所有与该post相关的tag.name
  通过以下查询，我们可以建立一个document的二维表数据

SELECT post.title || ' ' || 
       post.content || ' ' ||
       author.name || ' ' ||
       coalesce((string_agg(tag.name, ' ')), '') as document
FROM post
JOIN author ON author.id = post.author_id
JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
JOIN tag ON tag.id = posts_tags.tag_id
GROUP BY post.id, author.id;

               document
 --------------------------------------------------
Endangered species Pandas are an endangered species Pete Graham politics
Freedom of Speech Freedom of speech is a necessary right Rachid Belaid politics
Star Wars vs Star Trek Few words from a big fan Robert Berry politics

在查询语句中，我们通过post和author进行group， 通过string_agg对tag.name进行聚合。同时我们用了coalesce来防止聚合之后tag出现null。
此时，我们的document是一个简单的长字符串，当然对我们起不到什么作用。我们需要通过to_tsvector()对这个长字符串操作一顿。

SELECT to_tsvector(post.title) || 
       to_tsvector(post.content) ||
       to_tsvector(author.name) ||
       to_tsvector(coalesce((string_agg(tag.name, ' ')), '')) as document
FROM post
JOIN author ON author.id = post.author_id
JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
JOIN tag ON tag.id = posts_tags.tag_id
GROUP BY post.id, author.id;
           
               document
 --------------------------------------------------
 'endang':1,6 'graham':9 'panda':3 'pete':8 'polit':10 'speci':2,7
 'belaid':12 'freedom':1,4  'necessari':9 'polit':13 'rachid':11 'right':10 'speech':3,6
 'berri':13 'big':10 'fan':11 'polit':14 'robert':12 'star':1,4 'trek':5 'vs':3 'war':2 'word':7
(3 rows)

这个查询以tsvector的形式回传我们的document，这种格式特别适合来做full-text search。让我们在试一下将一个简单的字符串转化成tsvector类型

SELECT to_tsvector('Try not to become a man of success, but rather try to become a man of value');

query will return the following result:

                             to_tsvector
----------------------------------------------------------------------
'becom':4,13 'man':6,15 'rather':10 'success':8 'tri':1,11 'valu':17
(1 row)

见证奇迹的时刻到啦。 首先有一些单词是来自于原句子的，但是有一些单词变化了(try变成了tri)。同时所有单词后面都带了数字，为啥子?

一个tsvector是由一组不同的词元组成。词元是分词之后的变种，就是被操作过了。操作的专业名词叫做normalization。这波操作主包含大小写转化，后缀移除等等。搜索意味着要面对多重选项，减少一些选项便于引擎去搜搜。单词后面带的数字表示词元原来在的位置。比如man就是在6和15。您可以自个儿数数对不对。

默认情况下，Postgres在to_tsvector中用的是english的配置。它会直接忽略非英文的单词。

Querying

现在，我们知道如何构建一个文档。但是我们的目的是为了搜索。我们用@@来对tsvector的数据进行搜索。官方文档走一波。让我们看看一些例子：

> select to_tsvector('If you can dream it, you can do it') @@ 'dream';
 ?column?
----------
 f
(1 row)

> select to_tsvector('It''s kind of fun to do the impossible') @@ 'impossible';

 ?column?
----------
 t
(1 row)

上面主要是简单的查一下是否存在这个关键词。接下去的例子可以展示强制类型转化和to_tsquery这个函数的区别。

SELECT 'impossible'::tsquery, to_tsquery('impossible');
   tsquery    | to_tsquery
--------------+------------
 'impossible' | 'imposs'
(1 row)

to_tsquery函数用于写一些简单的搜索语句。支持布尔操作符&(AND), |(OR), !(NOT)。在to_tsquery也可以用进行表达式优先级的调整。

> SELECT to_tsvector('If the facts don't fit the theory, change the facts') @@ to_tsquery('! fact');

 ?column?
----------
 f
(1 row)

> SELECT to_tsvector('If the facts don''t fit the theory, change the facts') @@ to_tsquery('theory & !fact');

 ?column?
----------
 f
(1 row)

> SELECT to_tsvector('If the facts don''t fit the theory, change the facts.') @@ to_tsquery('fiction | theory');

 ?column?
----------
 t
(1 row)

同时可以用:*进行startwith搜索。

> SELECT to_tsvector('If the facts don''t fit the theory, change the facts.') @@ to_tsquery('theo:*');

 ?column?
----------
 t
(1 row)

现在我们知道如何做一个full-text search。我们回到我们的数据库尝试对我们文档进行查询。

SELECT pid, p_title
FROM (SELECT post.id as pid,
             post.title as p_title,
             to_tsvector(post.title) || 
             to_tsvector(post.content) ||
             to_tsvector(author.name) ||
             to_tsvector(coalesce(string_agg(tag.name, ' '))) as document
      FROM post
      JOIN author ON author.id = post.author_id
      JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
      JOIN tag ON tag.id = posts_tags.tag_id
      GROUP BY post.id, author.id) p_search
WHERE p_search.document @@ to_tsquery('Endangered & Species');

 pid |      p_title
-----+--------------------
   1 | Endangered species

语言支持

Posgres内置了一些语言: Danish, Dutch, English, Finnish, French, German, Hungarian, Italian, Norwegian, Portuguese, Romanian, Russian, Spanish, Swedish, Turkish.（没看到中文，这段就不说了）

Accented Character

音调的不同导致意思也不同，（没有内置中文，这段就不说了）

Ranking

构建一个搜索引擎，您会希望你的查询结果是根据相关度来排序的。与documents的排名相关的指标在下面这段引用中解释的很清楚。

Ranking attempts to measure how relevant documents are to a particular query, so that when there are many matches the most relevant ones can be shown first. PostgreSQL provides two predefined ranking functions, which take into account lexical, proximity, and structural information; that is, they consider how often the query terms appear in the document, how close together the terms are in the document, and how important is the part of the document where they occur.
-- PostgreSQL documentation

在PostgreSQL中提供了两个函数来进行相关度调整。分别是ts_rank() 和setweight()

函数setweight允许给予tsvector一个权重，权重的值可以是A,B,C,D。

SELECT pid, p_title
FROM (SELECT post.id as pid,
             post.title as p_title,
             setweight(to_tsvector(post.title), 'A') ||
             setweight(to_tsvector(post.content), 'B') ||
             setweight(to_tsvector('simple', author.name), 'C') ||
             setweight(to_tsvector('simple', coalesce(string_agg(tag.name, ' '))), 'B') as document
      FROM post
      JOIN author ON author.id = post.author_id
      JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
      JOIN tag ON tag.id = posts_tags.tag_id
      GROUP BY post.id, author.id) p_search
WHERE p_search.document @@ to_tsquery('english', 'Endangered & Species')
ORDER BY ts_rank(p_search.document, to_tsquery('english', 'Endangered & Species')) DESC;

在上面这个查询中，我们给予不同的tsvector不同的权重，A权重的post.title比B权重的post.content更加重要。依此类推。这意味着如果我们查一个词Alice。Adocument在标题中有Alice，Bdocument在内容中有Alice。那A文档会在前。
ts_rank函数会回传tsquery与tsvector的相关度，用一个小数来表示。

SELECT ts_rank(to_tsvector('This is an example of document'), 
               to_tsquery('example | document')) as relevancy;
 relevancy
-----------
 0.0607927
(1 row)


SELECT ts_rank(to_tsvector('This is an example of document'), 
               to_tsquery('example ')) as relevancy;
 relevancy
-----------
 0.0607927
(1 row)


SELECT ts_rank(to_tsvector('This is an example of document'), 
               to_tsquery('example | unkown')) as relevancy;
 relevancy
-----------
 0.0303964
(1 row)


SELECT ts_rank(to_tsvector('This is an example of document'),
               to_tsquery('example & document')) as relevancy;
 relevancy
-----------
 0.0985009
(1 row)


SELECT ts_rank(to_tsvector('This is an example of document'), 
               to_tsquery('example & unknown')) as relevancy;
 relevancy
-----------
 1e-20
(1 row)

但是，相关度这个概念实际上有点模糊且与实际APP需求紧密连接的。不同的APP需要不同的排名方式。你可以自己加上一些参数上去。比如你加一个根据作者年龄的排序，你可以ORDER BY ts_rank(p_search.document, to_tsquery('english', 'Endangered & Species')) * author.age DESC。最后怎么算还是由你自己决定。

(Optimization and Indexing)优化和索引

优化搜索非常简单明了。因为PostgreSQL支持基于Index的函数。所以你可以创建一个GIN index作用于tsvector函数。

CREATE INDEX idx_fts_post ON post 
USING gin((setweight(to_tsvector(language::regconfig, title),'A') || 
       setweight(to_tsvector(language::regconfig, content), 'B')));

-- If this throws an IMMUTABLE error then you can use this workaround

CREATE OR REPLACE FUNCTION gin_fts_fct(title text, content text, language text) 
  RETURNS tsvector
AS
$BODY$
    SELECT setweight(to_tsvector($3::regconfig, $1), 'A') || setweight(to_tsvector($3::regconfig, $1), 'B');
$BODY$
LANGUAGE sql
IMMUTABLE;

CREATE INDEX idx_fts_post ON post  USING gin(gin_fts_fct(title, content, language));

选择GIN还是GiST索引？这要根据你的读写量以及数据量来决定

As a rule of thumb, GIN indexes are best for static data because lookups are faster. For dynamic data, GiST indexes are faster to update. Specifically, GiST indexes are very good for dynamic data and fast if the number of unique words (lexemes) is under 100,000 while GIN indexes will handle 100,000+ lexemes better but are slower to update.
-- Postgres doc : Chap 12 Full Text Search

在这个例子中，我们将采用GIN。但是你可以根据你的情况决定使用什么数据。在document中，我们还有一个问题。document是跨表的，且各表字段拥有不同权重。为了得到更好的性能，我们通过triggers或者materialized view的方式进行数据重组。不过您不需要总是重组数据。在一些情况下，您可以加一些基于index的函数。还可以通过tsvector_update_trigger(...)或者tsvector_update_trigger_column(...)来重组数据。查看文档来获取更多细节。另外如果可以接受短暂延迟的话。用Materialized View是一个不错的选择。

CREATE MATERIALIZED VIEW search_index AS 
SELECT post.id,
       post.title,
       setweight(to_tsvector(post.language::regconfig, post.title), 'A') || 
       setweight(to_tsvector(post.language::regconfig, post.content), 'B') ||
       setweight(to_tsvector('simple', author.name), 'C') ||
       setweight(to_tsvector('simple', coalesce(string_agg(tag.name, ' '))), 'A') as document
FROM post
JOIN author ON author.id = post.author_id
JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
JOIN tag ON tag.id = posts_tags.tag_id
GROUP BY post.id, author.id

然后可以通过REFRESH MATERIALIZED VIEW search_index;的方式来重建索引。
首先我们建立索引

CREATE INDEX idx_fts_search ON search_index USING gin(document);

然后查询也会变得更加简单

SELECT id as post_id, title
FROM search_index
WHERE document @@ to_tsquery('english', 'Endangered & Species')
ORDER BY ts_rank(p_search.document, to_tsquery('english', 'Endangered & Species')) DESC;

Mispelling

英文没啥用， 中文的分词不知道实现了pg_trgm没有。。

总结

Posgres全文搜索功能还是相当棒和快的。有了它，你就不用装其他的搜索引擎了。Posgres像不像一颗幸福的子弹。。。也许不是，如果您的业务围着搜索转啊转的话。
有一些功能我没讲，但是您基本上也用不到。以我个人而言，我希望Posgres的全文搜索还有一些功能能够加进来。

• 更多的内置语言
• 和Lucene搞基
• 更好的排名和权重分配解决方案
• 模糊查询再叼一点
  总的来说，Posgres再全文搜索上肯定是没有ElasticSearch和SOLR那么先进。。。