在很長一段時間內，PostgreSQL都被認為是MySQL的替代品。但是，在那段時間里，根本沒有辦法使他達到MySQL所能達到的水平。最近幾年里，這些問題都無法解決，并且產生了許多有趣的工具來彌補PG。我們在Disqus中使用了兩個Slony和pgbouncer。Slony讓我們可以復制數(shù)據(jù)（有時候也可以分區(qū)），而pgbouncer為我們解決了保持鏈接和連接池的問題。

另外，讓我們看看他們的語言：我這個星期很高興能夠學會如何在PGSQL8.4中使用遞歸查詢，他們實在太強大了。這就是我這篇文章所真正想要和大家討論的東西。MySQL讓我們可以工作，并且工作的很好，但你只能在引擎的結構內完成。雖然在PG中依然如此，但你有了更多的選擇。因此，我想講講樹的線索化的問題。

大家都知道Disqus不僅僅是最大的Django網(wǎng)站（我們每個月有近100萬的訪問量），同時，他也是他也是一個最大的網(wǎng)上評論系統(tǒng)。我們?yōu)樯锨€網(wǎng)站提供了許多功能，最基本的就是評論作為樹狀結構的線索化。

PostgreSQL提供了許多個關于線索化的解決方案。最常用的（也是最高效的）方法就是改良版的前序遍歷。簡單的說，他增加了一個左序，一個右序，他們會在你添加評論時被更新。我們還有另一個標準的方法（Reddit使用的很歡樂），那就是“取出所有的東西，然后在內存中完成操作”。實際上，不僅僅只有Reddit這樣做。

繼續(xù)看看PGSQL為我們提供的東西，我們還可以找到兩個選項（最低在8.4版本）。其中一個是使用PG的內建模塊稱為ltree。他允許你將一個節(jié)點的完整路徑（所有父結點）存儲下來，同時允許你通過標準的sql語句查詢他們。當你需要按照“最早發(fā)布”排序的時候，它會非常有用，因為這樣以來，就變?yōu)榱撕唵蔚陌凑铡發(fā)tree——column”排序。然而，和大部分時候一樣，Disqus的情況沒有這么簡單。

我們的第二個解決方案就是遞歸查詢。他花了我很長一段時間來理解他是怎么工作的，但是當我理解后，我被他的能力深深的吸引了。Postgre提供了許多MySQL所沒有的特性，比如over()修飾符。他們真的表現(xiàn)的非常好。

讓我們繼續(xù)深入我們的問題，這會是一個大問題?，F(xiàn)在，Disqus和Reddit處理多線程的方法一樣，都是和網(wǎng)上其他的解決方案一樣，非常的簡陋。我說的是簡陋不是說代碼寫的不好，而是他的優(yōu)化沒有做到他應該做到的。直到某些人（就是你，Obama同學）開始使用這個程序，并且所有人都想回復他的話，我們才發(fā)現(xiàn)出問題了。我們再一次想到了Django（即使他們越來越大）并且通過業(yè)務邏輯將他們分組。

自從8.4開始，我們就可以使用遞歸查詢來解決這個問題（在許多情況下我們已經(jīng)自己開始這么做了，雖然會有點復雜）這個相當?shù)暮唵巍?/p>

因此，讓我們一個基本的例子。我們有一個的評論模型，它看起來有點像這樣：
 

create table comments (
  id SERIAL PRIMARY KEY,
  message VARCHAR,
  author VARCHAR,
  parent_id INTEGER REFERENCES comments(id)
);
insert into comments (message, author, parent_id)
  values ('This thread is really cool!', 'David', NULL), ('Ya David, we love it!', 'Jason', 1), ('I agree David!', 'Daniel', 1), ('gift Jason', 'Anton', 2),
  ('Very interesting post!', 'thedz', NULL), ('You sir, are wrong', 'Chris', 5), ('Agreed', 'G', 5), ('Fo sho, Yall', 'Mac', 5);

我們現(xiàn)在所做的，是建立一個基本的評價模型。我們的消息，筆者父評論（這是可選的）。現(xiàn)在，讓我們來學習如何使用遞歸查詢可以輕松地重新訂購本datd中，由id升序排序。

WITH RECURSIVE cte (id, message, author, path, parent_id, depth) AS (
  SELECT id,
    message,
    author,
    array[id] AS path,
    parent_id,
    1 AS depth
  FROM  comments
  WHERE  parent_id IS NULL
 
  UNION ALL
 
  SELECT comments.id,
    comments.message,
    comments.author,
    cte.path || comments.id,
    comments.parent_id,
    cte.depth + 1 AS depth
  FROM  comments
  JOIN cte ON comments.parent_id = cte.id
  )
  SELECT id, message, author, path, depth FROM cte
ORDER BY path;

很甜蜜吧？哦，等等，有困惑？所以我一直在尋找的查詢更復雜的是一大堆驚人的bug.
pgexperts為我們指向正確的道路。

現(xiàn)在，我不會鉆到太多，因為有更好的教程，在此模式中處理遞歸查詢，但我們完成了我們的結果。

我們要處理一個巨大信息集，并且有些評論有將近幾千個回復。如果99%的評論都只有100個回復，那么將他們放入內存中并不是什么問題，但當他們開始增加時，我們最終會浪費很多時間。PGSQL中的遞歸查詢可以讓我們很簡單的把這項工作交給數(shù)據(jù)庫（有時候他們處理的比我們快的多），并且給我們節(jié)省了很多花費在網(wǎng)絡傳播和web處理的時間和資源。

有一個例子可以讓你更直觀的理解他是多么的高效，我們曾經(jīng)見過僅在大型數(shù)據(jù)庫的SQL處理時間這一項上（返回25個結果，而不是1000個）就將近節(jié)省了500%的時間。這甚至沒有包括我們在程序級上的花費。是的，沒錯，這些SQL語句僅在數(shù)據(jù)庫層上就比其他數(shù)據(jù)庫快5倍

總而言之，作為一個MySQL的擁護者，我對Disqus使用PostgreSQL所達到的性能，規(guī)模，以及靈活性表示十分震驚。我十分期待去發(fā)現(xiàn)通過這個平臺我們還能做什么，去尋找還在等待我們的挑戰(zhàn)。

最新評論