| 编辑推荐: |
本文主要分享在阿里巴巴内部使用 PostgreSQL 的一些场景以及如何解决这些问题，希望对您的学习有所帮助。
本文来自于高效运维，由火龙果软件Alice编辑、推荐。 |
|
前言 PostgreSQL 这几年的发展非常迅猛，在国内掀起了一波
PostgreSQL 的热潮，但运维人才还是比较紧缺，所以在一些公司没有大面积铺开，不过不用担心，很多云厂商都提供了
PostgreSQL 的数据库服务。
阿里云的 RDS PostgreSQL 除了提供公有云服务，同时也对阿里巴巴集团提供内部的服务。接下来我会分享几个在阿里巴巴内部使用
PostgreSQL 的一些场景。大家可以想想思考一下，如果用其他数据库和技术手段怎么解决这些问题。
1.海量导购文实时去重
2.精准广告投放
3.TOB 实时画像
4.任意字段组合
5.任意字段模糊匹配
1. 海量导购文实时去重
1.1 导购业务介绍
首先是海量导购文实时去重。在日常生活中特别是妹子很喜欢看导购的推送消息（比如每日白菜价）；特别是家庭主妇，在家里没事就浏览白菜价，如果家里有小孩的，小孩才一两个月，已经买到十几岁的衣服了，这和导购推送有密切关系。
这么多的导购文章，每一篇文章都会推很多的商品，如果你是一个用户每天翻看这些文章都是一样的商品，是很令人讨厌的。
比如说每日白菜精选的文章里可能会涉及到几十个商品，整个导购平台可能会沉积上亿的文章，如果平均五十个商品的话，就会有五十亿个商品。当然里面有重叠，一篇文章里跟另外一篇文章可能有一两个重叠，这是没有关系的，但是你不能80%以上都重叠，这个重叠比例是需要可以设定的。
1.2 导购文审核发展历程
因为店家会去做推广，就会有佣金，网络写手会为了佣金去写导购文章。但是为了防止出现盗文现象，就需要审核导购文章，最原始的做法是什么样的？比如我刚新建了这样一个导购平台，用户数也不是特别多，这时候请一些较为廉价的劳动力来帮你解决审核的问题，最早期的为劳动力密集型。
发展到第二代，用计算机帮你做这个事情，新提交一个文章的时候，要在上亿的文章里去辨别跟我刚刚提交的商品的重复率是什么样的，涉及的运算量非常大，因此并不能做到实时的审核，通常是隔天的。
对于导购文章的编辑来说，这个效率是低下的，网络写手提交文章后，第二天才能告知有没有通过，才能发到网站上面，这样可能就错过了商家的营销时机。
1.2.1 数据结构问题
回到原始的数据去看，每一篇导购文章里面涉及到50个商品，按平均数来，商品使用一个数组来存储，这里面的每个数值对应的都是商品的
ID，每个记录会涉及到大概 50 个这样的值。
当用户提交一个新的导购文章来的时候，我们看到又有一堆的值进来，怎么做呢？
我们需要去比对库里的每一条记录，看他们重叠的元素有多少个，比如说新上的文章推荐了十个商品，与历史导购文章中某记录重叠的有八个商品
ID，意味着你新上传的文章有 80% 跟其中的某一篇文章的商品是重叠的，审核结果是拒绝。
1.2.2 PostgreSQL GIN索引应用
在 PostgreSQL 里面有一个什么技术能够帮你高效实现应用场景呢？我们用了 GIN 索引，它就是一个倒排索引，在你的一条记录里，可以对数组去建索引，一个数组有50个商品，第一列是一个行号，解开之后一个行号对应这么多值，倒转一下会变成这个值对应的行号是什么，就是帮你做了翻转。
有什么用呢？做完了翻转，用户上传了一篇新的导购文章，里面涉及到假设商品的 ID，我们看最左边的一列，1，3，101，198，上传这些商品
ID 上来，在索引上怎么搜索，比如说对1这个 ID，因为它有行号，里面对应的是数据块的 ID 加上这条记录在数据块的偏移，对应的号拿出来之后，在第一个数据块出现过，通过索引搜索出来，在第101个数据块里也出现过，数据库会帮你把每个数据块里的命中商品总数给记录下来，就变成了最下面的一行记录，213
422，什么意思呢？代表是在101这个数据块里有命中四个商品ID。
1.2.3 多重过滤提高检索效率
比如设置了重复数>4，通过索引可以直接拒绝发布。如果设置为>=3那么只要提取出第49号数据块和第101的数据块的数据。进入第二道工序。
刚才讲的是做的第一层过滤，第二层过滤是帮你定位到两个数据块了，然后你去每一条检查，因为每个数据块涉及的记录也就是几百条，整个下来效率就非常高。
1.2.4 仿真数据测试结果
根据真实数据的特征，构建一批仿真数据。
被推荐的商品数总量：超过1千万，每一篇导购文章平均下来涉及的商品，从历史数据来看是11到50个商品，一篇文章会推11到50个商品给你。
热点商品：是说非常大的店铺给的佣金非常高，很多人愿意去写文章推荐这种商品，这是热点商品。
1.2.5 PostgreSQL优化时效测试
这些测试对应的就是实际的应用场景，一篇新的文章上来之后，多快的时间能够告诉你有没有人跟你重叠，如果你是普通商品的话，过滤39个跟你重复的，我就把文章替掉，不让你发布了。
如果一篇文章里推的都是热点商品，因为被推荐的次数多，记录所涉及的数据块更多，因此一级过滤出来的数据块比较多，二级过滤做的就比较多，但是也能够达到15个毫秒。
吞吐量达到1万，相比以往隔天要告诉你能够审核通过，现在可以做到实时响应。
这是 PostgreSQL 在阿里的导购平台的应用，因为使用其他的技术根本没法解决这个问题，因为我们在阿里有一个
ATA 的技术论坛发帖子，刚好他们看到这个技术，找到我们团队，去给他们做了这个方案并上线。
2、精准广告投放
第二个应用场景是精准的广告投放，这个对应的是广告营销的产品，数量级也是庞大的。我们看这个场景的介绍，在你使用产品的时候一些浏览行为，比如你浏览了哪些店铺、购买了哪些商品，这些在数据平台里都是有跟踪记录的，比如说每个人浏览了哪些店铺，浏览了多少次，浏览了哪些商品，这些数据就可以用来做精准的广告投放。
2.1 业务介绍
什么意思呢？比如说最近经常浏览化妆品或者所关注的商品，你在购买前可能会一直浏览，根据这些行为，可以圈出一些最近都在关注化妆品的人群，卖化妆品的商家就可以定向推送运营的活动，把消息告诉这些人，因为这些人可能最近就要买了。
对于一个营销系统来说，它的非常重要的指标，一个是精准性，另外是实时性，如果你今天浏览完之后可能下单了，第二天再告诉你没有任何意义，就不会再给你重复推荐了。因此这两个重要的因素一结合，我们看PostgreSQL在里面怎么帮助平台达到效果。
首先看数据结构，一个是用户ID，然后是店铺轨迹，你浏览这个店铺多少次，浏览这个商品多少次，最后你买了这个商品多少数量，会有一些这样的轨迹，然后会有地理位置，基于地理位置的推荐，也会存位置信息。根据这些，我们可以根据时间区间、位置、浏览的店铺、浏览的商品等条件，圈选人群。
再看数据量，整个量级是百亿级别，商铺是亿级别，单个用户轨迹平均浏览一千个用户，还有浏览的商品量级以及购买的商品量级，基本上是在千这个级别，当然这个级别估得比较高，设计时需要考虑未来的体量。
2.2 阶梯化举例
业务需求刚才已经讲过，根据某个商品，比如浏览某个商品超过多少次的人群，某个区域浏览某些商品超过多少次的人群，相当于是精准圈人的意思。我们怎么对这个场景做设计？
首先是数字，浏览了多少次商品，是个精准的数字，如果说这些完完全全精准的存在里面的话，不利于后期的优化。
所以我们首先会做一个阶梯化，这是汽车的变速箱，比如说10AT的汽车，对于精准营销来说可能十档不够，要做得精准，我就要投放超过浏览次数100次的。
这是档位阶梯化，对行为轨迹精准次数做范围，根据不同范围定阶梯，定完阶梯，我的每个用户 ID 加上对应的轨迹，相当于是我给你拨一个档位一样，比如六档，说明你这个用户是落在六档这个范围，这样做之后就可以把左上角的这个值，这是原来的值，代表这个用户浏览了这个用户98次，我把它阶梯化之后就变成
711 767 740，表示它是1档，拼成一个值，把这个东西加上去。
图中所示公式为转换公式，怎么把老的值转成新的值，原来我用两个新的元素表示的数字，变成一个数字来表示。然后我就可以对轨迹建倒排索引，这个索引建完之后，就可以实现定向圈人。比如说浏览的商品包含什么，比如包含某一个商品的
ID 加档位数，这是对数组的操作。
浏览了十个店家任意一家超过多少次的，我就用 overlap 的做法做。
2.3 实现定向圈人
当用户量是 3.2 亿加上 64 个分区数，标签总量是 400 万，个人平均标签数是4千个，在一毫秒就可以挖出一万多的人群出来，实现了精准的实时营销。从量级来算，如果不用数组类型的话要存多少条记录？
每个人都有四千个标签，如果不用数组的话，每一个就是一条记录，这样乘3.2亿，相当于是1.2万亿的数字，而使用一台主机就可以解决。使用
PostgreSQL 的数组和GIN索引，巧妙的解决了业务的问题。
3、TOB 实时画像
我们再看 TOB 实时画像，这是阿里云对外的一个项目，TOB 的实时画像的业务。
3.1业务介绍
跟前面一个例子项目非常类似，差别就在于 TAG 数不一样。前面讲的例子TAG数有400万个，在这个系统里面是1万个
TAG 来描述 TOB 的用户，就好像我给你看相一样，贴一万个 TAG，基本上把特征描述得清楚了。
当初设计1万个 TAG，基本上一千多个列已经是极限了，因为一条记录是不能跨数据块的，所以对于这种超过两千个字段的表，要么就是拆表，根据ID去联合起来。
因此一张表肯定是搞不定的，搜索的条件是按照包含哪些 TAG 并且不包含哪些 TAG，这种比较类似于挖掘型的操作，原来使用了8台物理机，1亿个用户，1万台
TAG 去解决这个问题。
3.2 圈人-业务指标
思考一下将来的设计，这个用户如果只是换一个平台可能没什么兴趣，他是说将来要用户量级再涨一倍，同时希望压缩成本，因为用了很多成本。
TAG 数有1万，每个 TAG 一列，如果存一张表肯定是搞不定的，用户规模加 TAG 是一万亿 user
tags，贴标签、删除标签、更新标签都要求分钟级的延迟。
由于是 TOB 的系统，查询的并发要求200到300，用户根据 TAG 字段查出包含、不包含、或者多个字段的组合。最后就是响应时间的要求，这种查询响应时间要毫秒级。
3.3 方案介绍
我们来想怎么设计这个表结构，因为一张表是存不下一万个字段的，如果每个 TAG 一个字段的话，你要写很多的
and 跟 or，基本上做不到毫秒，80台机器也做不到，这就是问题。是不是每个字段都要建索引，第二是超宽表怎么解决，这都是场景要面临的问题。
我们来看解决方案的优缺点。首先是用数组存，一个数组最大长度是1个G，如果我用 int4 做标签的话可以存
2.6 亿个 TAG，然后还可以进行分区。
query 的写法也简单，原来要包含某一些 TAG 的数组出来，包含两个的就可以。指定 TAG 之一的话就是看有没有相交，最后是不包含，是没有办法支持索引的，这是方案一。
再看方案二，把 TAG 变成 BTI，但是有个问题是我不能对每个 BTI 建索引，比如说要求第10个
BTI 等于1的，把你这个用户捞出来，最大的缺点就是没法建索引，但是数据量下降很多，因为一个 BTI
和一个数组字节差了几十倍。这个方案要么通过 CPU 多核并行测算，一个32核的机器在这11条记录里搜一遍也要花十几秒。
方案三使用了阿里云 RDS PostgreSQL 提供的 varbitx 扩展。针对方案2做一次翻转，一个
TAG 一条记录，每个用户一个 BTI，它的空间跟第二方案是一样的，比第一个缩小差不多80倍的样子。
我们在使用这个方案之后要去捞一批用户出来，是记录与记录之间的运算，因为我只有1万条记录，当然建索引是最好的，你只要在1万条记录上建索引。
最终我们的方案是选择了方案三，这是我们给他们设计的数据合并过程，最终支持了他的应用场景，通过一台机器就解决了原来八台机器无法解决的问题。
3.4 方案空间评估
这是方案的对比，首先是空间上的对比。如果使用方案1，需要8个T的空间，如果是方案2或者3只要100个G空间就可以。
除了对比空间还要对比查询效率，第一个方案对空间要求很多，但是产生的效率也不低，包括插入更新、查询响应都是满足客户需求的，只是占用空间比较多。
对于方案2，我得用 BIT 运行。
方案3，每个指标都是满足需求，包括数据的合并写入，查询并发是超过了用户最初预计的要求。
3.5 实施前后对比
优化前用了8台物理机，用户体量翻了一个量级之后，原来的更新是天级别，现在是分钟级别。查询并发也是翻了一倍，响应也从原来的分钟级降到毫秒级。
未来在内核层的优化，如果只更新了一些用户的 TAG，将来更新某一小块数据的时候，不用更新现在整个大的一个
BIT，将来是可以做到快捷更新。
4、任意字段组合
再分享两个场景，一个是任意字段的组合查询，也是一个比较常见的场景。
4.1 业务介绍
在浏览一些页面的时候有很多选项，你可以勾是不是赠送退货运费险或者也可以勾选是否要二手或者天猫，对用户是有很多选择的，而对于设计人员来说就得考虑，你的每一个选择对应数据库里都是一个字段，是不是每个字段都要建索引，如果我给用户60个选择，是不是都要建索引。
每个字段都建索引会带来一定的影响，在更新、插入的时候，索引的变更会引入 RT，比如每更新一次索引，增加
0.1 毫秒的 RT，建十个索引的时候就变成一个毫秒，这是非常严重的问题。但是业务又要求每个字段都要勾选，怎么快速响应给用户，这个问题是很矛盾的。
解决方案：GIN组合索引-任意组合查询
该在哪个字段建索引，还是用倒排索引，除了倒排作用，还可以为每个字段上面都建符合倒排索引，达到什么效果呢？比如说有6个列是给用户可以选择的，我原来可能要建6个字段，一个阶层的任意组合的索引才能达到效果。
4.2 实施后的性能指标
但是现在建一个索引，看任意六个列，任意列做 OR 或者 AND 的查询达到什么效果？可以看响应时间，任意
AND 都可以达到零点几个毫秒，也就是在这样的场景下，可以通过一个这样的索引解决你原来根本就不知道建多少个索引才能解决的问题。如果大家想了解GIN索引的内部原理，可以参考我的
GITHUB (https://github.com/digoal/blog/blob/master/README.md)。。
5、任意字段模糊匹配
5.1 业务介绍
再引入下面一个应用场景，是我们的客户关系系统，我们在这个系统里是为用户提供了类似于你觉得搜索引擎在能干的事情。
比如用户提供一些关键字来搜索，而且是任意阶段的，只要匹配这个关键字就反馈给你，原来我根本不知道怎么建索引。比如是
URL 地址，如果建全文检索根本就没用，因为全文检索里面是得分词的，得有字典，而URL是没有什么意义的，可能取的名字根本没法分词分出来，所以检索解决不了。
还有一些公司名称，公司的名称可能不是一个常见词，往往全文检索词库中没有，没法进行分词。因此全文检索无法解决模糊查询的问题。搜索引擎能不能这个问题呢？
可以解决，但是它得跟搜索引擎同步数据，还有考虑数据库和搜索引起的一致性问题。额外的产生的费用还有维护成本的问题，因为你还得维护一个搜索引擎，包括数据的同步，还有更新，包括数据的过期等等。
5.2 pg_trgm 解决方案
既然这两个都解决不了，有什么方法能解决这个问题？在 PostgreSQL 里面有一个叫做 PGTRGM
的小组件，可以帮你的词拆成连续一个一个的字段，去做匹配，可以达到非常高的查询效率。最终达到的效果，数据量是亿级别，用户任意的模糊搜索是可以达到毫秒级别的响应。 |
