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那么先进。。。
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,839评论 6赞 482
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,543评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 153,116评论 0赞 344
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,371评论 1赞 279
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,384评论 5赞 374
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,111评论 1赞 285
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,416评论 3赞 400
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,053评论 0赞 259
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,558评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,007评论 2赞 325
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,117评论 1赞 334
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,756评论 4赞 324
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,324评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,315评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,539评论 1赞 262
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,578评论 2赞 355
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,877评论 2赞 345