共计 3193 个字符，预计需要花费 8 分钟才能阅读完成。
本篇文章为大家展示了 Instagram 中怎么提升 PostgreSQL 性能，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。
1. 局部索引
如果我们经常需要按某个固定的特征过滤数据，而且这个特征只存在于一小部分行里，在这种情况下，局部索引非常有效。
比方说，Instagram 搜索标签的时候，我们需要找出有许多照片的标签。我们一般会用 ElasticSearch 之类的技术来进行高级搜索，不过这里只靠数据库的查询能力就完全够了。先来看一下，按标签查询，并按照片数排序，Postgres 是怎么做的:
EXPLAIN ANALYZE SELECT id from tags WHERE name LIKE snow% ORDER BY media_count DESC LIMIT 10;
QUERY PLAN
---------
Limit (cost=1780.73..1780.75 rows=10 width=32) (actual time=215.211..215.228 rows=10 loops=1)
- Sort (cost=1780.73..1819.36 rows=15455 width=32) (actual time=215.209..215.215 rows=10 loops=1)
Sort Key: media_count
Sort Method: top-N heapsort Memory: 25kB
- Index Scan using tags_search on tags_tag (cost=0.00..1446.75 rows=15455 width=32) (actual time=0.020..162.708 rows=64572 loops=1)
Index Cond: (((name)::text ~ =~ snow ::text) AND ((name)::text ~ ~ snox ::text))
Filter: ((name)::text ~~ snow% ::text)
Total runtime: 215.275 ms
(8 rows)有没有看到，为了得到结果，Postgres 不得不对 15000 行数据进行排序。由于标签的分布满足长尾模式 (译者注: 根据百度百科，「我们常用的汉字实际上不多，但因出现频次高，所以这些为数不多的汉字占据了上图广大的红区；绝大部分的汉字难得一用，它们就属于那长长的黄尾。」)，我们可以改为查询超过 100 张照片的标签，先建局部索引:
CREATE INDEX CONCURRENTLY on tags (name text_pattern_ops) WHERE media_count = 100
然后查询，看一下新的查询计划:
EXPLAIN ANALYZE SELECT * from tags WHERE name LIKE snow% AND media_count = 100 ORDER BY media_count DESC LIMIT 10;
QUERY PLAN
Limit (cost=224.73..224.75 rows=10 width=32) (actual time=3.088..3.105 rows=10 loops=1)
- Sort (cost=224.73..225.15 rows=169 width=32) (actual time=3.086..3.090 rows=10 loops=1)
Sort Key: media_count
Sort Method: top-N heapsort Memory: 25kB
- Index Scan using tags_tag_name_idx on tags_tag (cost=0.00..221.07 rows=169 width=32) (actual time=0.021..2.360 rows=924 loops=1)
Index Cond: (((name)::text ~ =~ snow ::text) AND ((name)::text ~ ~ snox ::text))
Filter: ((name)::text ~~ snow% ::text)
Total runtime: 3.137 ms
(8 rows)可以看到，Postgres 只需要访问 169 行，所以速度快得多。Postgres 的查询计划器对约束的评估也很有效。如果以后想要查询超过 500 张照片的标签，由于这个结果集是上面集合的子集，所以仍然会使用这个局部索引。
2. 函数索引
在某些表上，我们需要对一些很长的字符串建立索引，比如说，64 个字符的 base64 记号。如果直接建索引的话，会造成大量的数据重复，这种情况下，可以用 Postgres 的函数索引:
CREATE INDEX CONCURRENTLY on tokens (substr(token), 0, 8)虽然这样会造成许多行匹配相同的前缀，但我们可以在匹配的基础上再用过滤，速度很快。而且索引很小，只有大概原来的十分之一。
3. 用 pg_reorg 来让数据更紧凑
随着时间的流逝，Postgres 的表会变得越来越零碎(由 MVCC 并发模型等原因引起)。而且，数据行插入的顺序往往也不是我们希望返回的顺序。比如说，如果我们经常要按用户来查询照片等，那么最好是在磁盘上把这些东西放在一起，这样就可以减少磁盘寻道的时间。
我们用 pg_reorg 来解决这个问题，它用三个步骤来让“压紧”一个表:
取得表的独占锁
建一个记录变更的临时表，在原始表上加一个触发器，把对原始表的变更复制到临时表上
用 CREATE TABLE…SELECT FROM…ORDER BY 建表，新表拥有原始表的全部数据，而且是按索引顺序排序的
将 CREATE TABLE 执行时间点以后发生的变更从临时表同步过来
业务切换到新表
每一步都会有很多细节，不过大体上就是像上面这个样子。我们先对这个工具进行了一些审查，运行了若干测试，然后再把它用到生产环境上。现在，我们已经在几百台机器的环境上跑过几十次 pg_reorg，没出现过任何问题。
4. 用 WAL- E 进行 WAL(写前日志)的归档和备份
我们用 WAL- E 来归档 WAL 日志，它是 Heroku 写的一个工具，我们也向它贡献了一部分代码。WAL- E 大大简化了数据备份和复制库创建的过程。
WAL- E 是利用 Progres 的 archive_command，将 PG 产生的每个 WAL 文件都归档到 Amazon 的 S3。利用这些 WAL 文件和数据库的基准备份，我们可以将数据库恢复到基准备份后任何一个时间点的状态。利用这个手段，我们也可以快速创建只读的复制库或故障备用库。
我们为 WAL- E 写了一个简单的封装脚本，可以监控归档时的重复故障，见 GitHub。
5. psycopg2 中的自动提交模式和异步模式
我们也开始用 psycopg2 中的一些高级功能(psycopg2 是 Postgres 的 Python 驱动)。
一个是自动提交模式。在这个模式里，psycopg2 不会发出 BEGIN/COMMIT，每个查询跑在自己的单语句事务里。这对不需要事务的只读查询特别有用。开启很简单:
connection.autocommit = True开启自动提交后，我们的应用服务器和数据库之间的对话大减，数据库服务器的 CPU 用量也大减。而且，我们是用 PGBouncer 作为连接池，开启自动提交后，连接的归还也更快了。
与 Django 的交互细节可以看这里。
上述内容就是 Instagram 中怎么提升 PostgreSQL 性能，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。
