共計 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 行業資訊頻道。