PostgreSQL vs. MS SQL Server

oschina發表於2014-12-01
原文網址 : http://www.admin10000.com/document/5549.html
SQLServer

　　從一個資料分析師的視角來對比兩個關係型資料庫。

　0.本文是關於什麼的？

　　我在一個全球專業服務公司做資料分析師（你肯定聽說過的）。我幹了大概有10年。10中我處理資料、資料庫軟體、資料庫硬體、資料庫使用者、資料庫程式設計師以及資料分析方法，所以我對這些東西瞭解的比較多。我經常遇到對相關內容瞭解很少的人，雖然他們中的一部分並沒有意識到這件事。

　　這些年裡，我已經太多太多次的討論了 PostgreSQL 和 MS SQL 的問題。IT 行業中一個知名的原則說：如果你準備不只一次的做同一件事，那就讓它自動化。本文是我的自動化方法的談話。

　　除非另有說明，我指的是PostgreSQL 9.3和MS SQL Server 2014，即使我的經驗是在MS SQL Server 2008 R2和2012版。為了公平起見，我將比較最新版的MS SQL Server和PostgreSQL。由於微軟的糟糕的文件，我不得不大量的依賴於Google、Stack Overflow以及網路上的使用者。因為我對兩個資料庫的經驗不相等，所以我知道像這樣的比較不夠科學嚴謹。不過這不是一個學者的練習題，這是現實中的比較。我儘可能讓我對於MS SQL Server的瞭解正確，因為我們都知道要糊弄整個網際網路是不可能的。如果我發現我弄錯了什麼事情，我會修正的。

　　我將以一個資料分析師的角度來比較兩個資料庫。MS SQL Server可能會因為QLTP後臺而踢PostgreSQL的屁股（雖然我比較懷疑），不過那些不是我這裡要關注的，因為我不是一個OLTP開發者/DBA/系統管理員。

　　最後，右上角有一個email地址。如果你願意的話你會用到的，我會盡可能回覆的。

　　免責宣告：本文所有觀點僅代表我個人。

　1. 為什麼說 PostgreSQL 比 MS SQL Server 強的多

　　額，劇透了。本節從資料分析的角度對比這兩種資料庫。

　　1.1. 支援 CSV

　　CSV 其實是轉移結構化資料(如: 表)的一種標準方式。不論是哪一種資料庫，都能用自己專有的格式，把資料匯出來。以這種格式儲存的資料，其他軟體無法讀取. 用來做備份或者複製資料還行。如果想從 X 系統, 把資料移植到 Y 系統，那問題就大了。

　　一個資料分析平臺, 既要能讀取不同系統的資料, 也要能生成其他系統能讀取的分析結果.  也就是說, 要能快速, 穩定, 可重複的, 而且毫無痛苦的讀寫 CSV.  我再說一次：一個不能很好的處理 CSV 的資料分析平臺，就是沒用的累贅。

　　PostgresSQL對CSV的支援在業內是頂尖的。  COPY TO和 COPY FROM命令支援RFC4180（最接近官方標準的文件）中列出的所有規格，也支援很多常見的和不常見的變種和方言。 這些命令執行速度很快而且很強大。 發生一個錯誤時，它們會給出有幫助性的錯誤資訊。 更重要的是，它們不會默默地損壞、誤解、修改資料。

　　而MS SQL Server既不支援匯入也不支援匯出CSV檔案。 很多人不相信當我告訴他們這一點時。 然後，某一次，他們自己驗證了這一點。通常他們的觀察是這樣的：

  • MS SQL Server默默地清除（truncate）了一個文字欄位的資料

  • MS SQL Server對文字進行編碼時發生錯誤

  • MS SQL Server丟擲一個錯誤資訊因為它不理解引用或轉義（出乎人們的意料，對CSV來說引用和轉義不是特殊的擴充套件。從字面上看，引用和轉義是每一個人類可讀的資料序列化規範的基本概念。不要相信那些不懂這些東西的人。）

  • MS SQL Server匯出損壞的、不可用的CSV檔案

  • 微軟有一篇驚人的文件。他們怎麼能把CSV這麼簡單的東西如此複雜化的呢？

　　如果你不相信，下載這個格式正確的、符合標準的UTF-8編碼的CSV檔案，用MS SQL Server計算檔案中最後一列（共有50列）字串的平均長度（或者是字元的數量，等等）。繼續，試一下。

　　（你得到的答案將是 183.895。）

　　當然，事實上，對 PostgreSQL 來說，確定這麼做非常簡單。最耗費時間的地方是建立儲存這些資料的且具有50個欄位的資料表。微軟本身似乎就很難理解CSV檔案；而且開啟這樣的檔案還會引起Access和Excel中斷。

　　痛苦但卻是事實的情況是：我瞭解到近期一些資料庫程式設計人員花費大量的時間和精力編寫Python程式碼，以實現對CSV檔案的“清理”，從而讓MS SQL伺服器可以把這些檔案的內容匯入到資料庫裡。但是，這種處理方法不可避免的要更改實際的資料。這就像花費大量金錢購買了Photoshop，然後不得不編寫一些定製的程式碼來讓Photoshop開啟JPEG，到頭來僅僅發現只是稍稍修改了圖片那樣讓人抓狂。

　　1.2.人機工程

　　值得一提的是每個資料分析平臺都是圖靈完備的，這大概意味著任何一個資料分析平臺可以做其他資料分析平臺做的任何事情。也就不存在“你可以在A軟體中做X這件事而不可以在B軟體中做X這件事”。即你可以在任何軟體裡做任何事情-所不同是難易程度。好的工具讓你要做的事情做起來非常簡單；差的工具就會讓你要做的事情做起來很難。說到底就是這麼回事。

　　（理論上來講這一切都是正確的，然而現實中卻不是這樣的-例如，我瞭解到沒有關係型資料庫管理系統(RDBMS)使用3D圖形。不過，任意一個關係型資料庫管理系統都可以模擬GPU執行任何圖形計算。）

　　很顯然，PostgreSQL 是由實際關心如何對資料進行處理的人編寫的。而MS SQL伺服器則是由那些根本就不需要使用MS SQL伺服器來實現某件事情的人編寫的。下面的幾個例子就可以說明：

  • PostgreSQL支援DROP TABLE IF EXISTS，這是一個非常聰明且顯而易見的處理方式，它表明：“如果這個表不存在，就什麼也不做，如果存在，就刪除它”。例如：

    DROP TABLE IF EXISTS my_table;

    在MS SQL伺服器中，你卻需要這麼做：

    IF OBJECT_ID (N'dbo.my_table', N'U') IS NOT NULL
DROP TABLE dbo.my_table;

    是的，只是多了一行程式碼，不過要留意OBJECT_ID函式中令人奇怪的第二個引數。刪除檢視時你需要把它替換為N'V'。刪除儲存過程時替換為N'P'。我不知道所有不同型別的資料庫物件對應的各個字元（為什麼我必須要知道呢？）還要注意到另一點：不必要地重複了表名。你稍不留意，就容易做出以下事情：

    IF OBJECT_ID (N'dbo.some_table', N'U') IS NOT NULL
DROP TABLE dbo.some_other_table;

    看看此時會發生什麼？這確實是令人懊惱、浪費時間的錯誤。

  • PostgreSQL支援DROP SCHEMA CASCADE，它會刪除模式以及該模式下的所有資料庫物件。對一個強壯的分析方法來說，做到這一點非常、非常重要，因為此時分割和重建是進行可重複的、可審計的協作分析工作的基本操作方法。而MS SQL伺服器卻不是這樣的。你不得不手工刪除該模式下的所有物件，而且要按照正確的順序刪除，因為在你試圖刪除一個其他物件依賴的物件時，MS SQL伺服器只會丟擲一個錯誤。這使得整個處理過程非常笨拙。

  • PostgreSQL 支援 CREATE TABLE AS。一個簡單的例子如下：

    CREATE TABLE good_films AS
SELECT
  *
FROM
  all_films
WHERE
  imdb_rating >= 8;

    這就意味著你可以使用除第一行以外的其他行，並執行，在開發SQL程式碼時，這是一個常見的且非常有用的處理方式。在MS SQL伺服器裡，你要採用如下程式碼才能以上面的方式建立表：

    SELECT
  *
INTO
  good_films
FROM
  all_films
WHERE
  imdb_rating >= 8;

    此時，要執行普通的SELECT語句的話，你需要註釋或者刪除INTO部分。是的，註釋兩行非常簡單；不過這不是我們關注的地方。我們關注的是在PostgreSQL裡，你不需要修改程式碼就可以執行這個簡單的任務，而在MS SQL伺服器上，你無法做到這一點，而且你要做到這一點還會帶來另一個潛藏的漏洞和令人討厭的東西。

  • 在PostgreSQL裡，你可以在一次批處理裡執行你願意執行數量級的SQL語句；只要每個語句都以分號結束，你就可以執行你所想到的任何語句組合。對於哪些要執行自動批處理、構建重複資料或者進行輸出的程式來說，這是一個非常重要的功能。在MS SQL伺服器裡，在一個批處理的SQL語句中間不能出現CREATE PROCEDURE語句。這麼做沒有任何好的理由，僅僅是隨意加的一個限制。此時就意味著需要額外的手工操作來執行大量的SQL批處理。手工操作會增加風險，降低效能。

  • PostgreSQL 支援 RETURNING 子句，允許 UPDATE,INSERT 和 DELETE 語句返回已更改行上的資料值。這麼做非常簡潔有益。MS SQL 伺服器有個 OUTPUT 子句可滿足這方面需求，不過它需要單獨定義表變數來實現此功能。這麼做很笨拙而且不方便，還迫使程式開發人員建立並維護不怎麼需要的程式碼。

  • PostgreSQL 支援用 $$ 將字串括起來, 像這樣:

    SELECT $$Hello, World$$ AS greeting;
    這樣寫， 對動態生成 SQL 語句很有用， 因為 (a) 當嵌入字串時, 能避免既繁瑣, 又容易出錯的手工引用和對特殊字元的轉義.  (b) 由於文字編輯器和 IDE 一般不把 $$ 當作字串分隔符, 動態生成的 SQL 語句依然根據語法高亮顯示。

  • PostgreSQL 允許你向資料庫引擎提交程式導向的程式語言程式碼; 你可以使用, 像 Python , Perl , R 或 JavaScript, 或其他已被支援的語言(具體看下面), 在同一個指令碼檔案的 SQL 語句旁邊, 加上程式導向的程式碼. 這樣做簡潔方便, 易於維護. 同時也方便檢視程式碼, 重複使用, 等等各種好處.  

    而 MS SQL Server, 你可以使用笨拙, 緩慢, 還有點尷尬的 T-SQL, 或者用 .NET  生成元件(Assembly) 然後載入到資料庫中. 。也就是說，你的程式碼存在兩個不同的地方。你得在各種圖形介面之間切來換去的修改這些程式碼。想要將這些東西統一打包放在一起，困難重重。而且也容易出錯。

　　諸如此類例子還有很多.  這些問題, 分來開看, 好像沒什麼. 可是放到一起, 問題就大了. 想要在 MS SQL Server 上面做好一件事, 要比在 PostgreSQL 做的難度大的多. 資料分析師把許多寶貴的時間和精力都花在, 解決各種問題, 手工處理的過程中, 而不是解決真正需要解決的問題.  

　　更正: 有人跟我說,  MS SQL Server 有個優勢, 是 PostgreSQL 不具備的. 那就是在 SQL 指令碼中宣告變數.  如:

DECLARE @thing INT = 1;

SELECT @thing + 6;   --returns 7

　　PostgreSQL 確實不能宣告變數. 真心希望它能加上這個實用的功能.

　　你能夠在Linux,BSD等平臺上執行PostgreSQL（當然，在Windows上也可以）

　　IT行業的開發人員都清楚跨平臺是當今一個關注點。支援跨平臺可以說是Java殺手級的特性，其實Java是一門尚顯粗糙、醜陋的程式語言，但它依然獲得了巨大的成功，廣泛的影響及普及。Linux與蘋果的崛起使微軟在桌面領域無法再保持壟斷地位。雲服務的靈活性和高效能虛擬化技術的易訪問性，使IT基礎設施越來越多樣化。跨平臺軟體能夠提供給使用者控制他們的基礎設施。（工作中，我目前管理著好幾個PostgreSQL 資料庫，一些執行在Windows平臺上，一些在Ubuntu Linux上。我和同事自由地在這些平臺之間移動程式碼和資料庫資料。我們使用Python和PHP，因為他們在兩種作業系統上都能執行。它們全部執行得很好。）

　　微軟的政策一直都是供應商鎖定。 他們不開放自己的程式碼；他們不提供跨平臺版本的軟體；他們甚至自己創造一個完整的生態系統，.NET設計用於為微軟使用者和非微軟使用者搭建了一座橋樑。 這對他們是有利的，因為這種方式保證了他們的利潤。 這對你（使用者）是不利的，因為微軟限制了你的選擇，而且為你建立了一些不必要的工作。

　　這不是一篇對比 Linux和 Windows的文件，儘管我確定我最後會提到幾點。  可以肯定地說，對於真正的 IT工作， Linux（和類 UNIX作業系統家族： Solaris， BSD等）把 Windows甩出幾條街。  類 UNIX作業系統主宰著伺服器市場、雲服務、超級計算（在這個領域它近乎壟斷）和科學計算，一個原因就是 - 這些系統是技術人員為技術人員設計的。 最終，他們以巨大的力量和靈活性換得了使用者友好性。 一個合格的類 UNIX OS 不僅僅是一個漂亮的命令列集合 – 它是一個包含各種程式、實用工具、功能的生態系統，並且提供支援使完成工作變得高效和有趣。 一個合格的 Linux黑客可以用一行被拋棄的 Bash指令碼達到目的，但是這個任務在 Windows中是艱鉅且耗時的。

　　（例如，某一天，我在檢視朋友影片收集情況，他對我說，他認為他的檔案系統中檔案的總數量太多，他想知道究竟有多少影片檔案，還想知道他是否可還以把一個大型的資料夾結構複製到影片資料夾下。我使用下面語句對每個資料夾及其子資料夾所包含檔案數進行了計算：

find . -type f | awk 'BEGIN {FS="/";} {print $2;}' | sort | uniq -c | sort -rn | less

　　整件事情做下來編寫花了一分鐘，執行花了一秒鐘。同時還證實某些資料夾有問題，並告訴他具體哪個資料夾有問題。Windows下怎麼能做到這些呢？）

　　在做資料分析時，關係型資料管理系統(RDBMS)不可能處在真空裡；它是整套工具中一部分。因此它所處的環境就非常重要。MS SQL伺服器只可在Windows系統上使用，而Windows是一個很差勁的可用於分析的環境。

　　1.4程式語言特性

　　這可是一個大問題。

　　一個“純”字可以概括SQL，因為它只專注於它被設計的初衷，那就是關係型資料的操作和查詢。如果你嘗試用它做更多的分析處理的話，比如複雜的利息計算、時間序列分析以及通用演算法設計，你將很快達到它的極限。SQL資料庫的提供者對這些比較瞭解，所以幾乎所有的SQL資料庫都實現了某種程式語言。這就是使得資料庫使用者可以寫命令式風格的程式碼以用於更復雜或繁瑣的任務。

　　PostgreSQL的程式語言支援比較好。對它來說在一個小範圍內是不可能做到公正的，不過這只是一個樣本。這些程式語言的任何一個都可以用來寫儲存過程和函式或直接轉儲到一個內聯執行的程式碼塊。

  • PL/PGSQL： 它是PostgresSQL 原生的程式語言。 它與Oracle的PL/SQL類似，但是它比後者更現代、功能更完善。

  • PL/V8：來自Google Chrome的V8指令碼引擎在PostgresSQL中可以使用。 這個引擎穩定、功能豐富、速度不可思議地快 – 經常接近於經過優化和編譯的C語言程式碼的執行速度。把它和PostgresSQL對JSON資料型別的原生支援結合起來（見下文），在一個單獨的包中你會有終極的力量和靈活性。甚至更好，PL/V8支援全域性（跨函式呼叫，等等）狀態，允許使用者選擇性地快速隨機訪問RAM中快取記憶體資料。 假設表A有100,000行資料，表B有1,000,000行資料，對於表B中的每行資料都需要與表A中所有資料進行關聯。 使用傳統的SQL，你要麼需要關聯這兩張表（在中間表中會有10^11行資料，這會累死所有計算機，除最強大計算機外）或者進行一個類似於標量的子查詢（或者使用基於遊標的巢狀迴圈，這更糟），如果查詢解析器沒有正確地讀懂你的意圖，這些方法會帶來沉重的I/O負擔。在PL/V8中，你只需在記憶體中快取表A的資料，遍歷表B的資料時呼叫一個函式 – 訪問這個有100,000行的表A時，它會給你RAM級別的訪問速度（微不足道的延遲和隨機訪問懲罰；沒有非易失性I/O載入）。 最近我在我的一個專案中使用了這種方法- 我的PostgreSQL/PLV8程式碼執行速度比微軟T-SQL解決方法快80倍，並且程式碼更簡潔、更易維護。 由於執行耗時23秒而不是30分鐘，我因此能夠在1個小時內完成20個測試周期（執行-測試-修改），從而寫出功能完善、經過測試、無bug的程式碼。 關於這一點，在這裡可以看到更多細節。（由於使用 DROP SCHEMA CASCADE，並且在批量宣告中間可以隨意執行CREATE FUNCTION語句，所有那些測試周期才是可能完成的，正如上面解釋的那樣。 看它們在一起是多麼搭配啊！）

  • PL/Python:你可以在PostgreSQL裡隨意使用Python。Python2或者Python3，隨你選，都可以，你可以使用大量的Python庫，Python正是因為它們才出名的。在SQL查詢語句中間想呼叫scikit-learn提供的SVM或者gmpy2提供的某些任意精度的演算法，可以嗎？沒問題！

  • PL/Perl：Perl已經不流行一段時間了，不過因其功能多而贏得了程式語言瑞士軍刀的美譽。在PostgreSQL裡，你完全可以把Perl用做編寫儲存過程語言。

  • PL/R：R是學術和資料科學領域統計程式設計的事實標準，而且還有很好的理由使用它-它免費、強大、功能全面，同時有大量高質量的外掛和附件庫支援。因此PostgreSQL允許你可以使用R做為編寫儲存過程的語言。

  • 在PostgreSQL裡，還可以用Java、Lua、sh、Tcl、Ruby和PHP做為編寫儲存過程的語言。

  • C:它確實不屬於儲存過程語言列表，因為你需要單獨對它進行編譯，不過在這兒值得一提。在PostgreSQL中，可以非常容易地建立在資料庫後臺程式裡進行C（或者C++或者彙編) 的編譯、優化的函式。這是一個非常強大的使用者功能，它可以讓哪些對效能有特別要求的任務執行速度最快、記憶體管理和資源利用得到精確地控制。我已經用它實現了複雜的支付狀態處理演算法，可在一秒鐘操作一百萬行資料-而且是在桌面PC上執行的。

　　MS SQL Server 內建的程式導向程式語言 (T-SQL 對 SQL 擴充的一部分) 既笨拙， 又緩慢， 各種缺點。 就如 Microsoft 自己的文件說的那樣， 它有時會容易產生一些奇怪的錯誤和 Bug。  我還沒見過哪個程式設計師說他喜歡 T-SQL 的。

　　那放到 MS SQL Server 上面用的 .NET 元件呢 ?  這種不算程式導向語言支援， 因為你不能直接向資料庫引擎提交程式碼。要知道， 可管理性和人類工程學（ergonomics ）都很重要。 直接將 Python 程式碼嵌入資料庫查詢語句中， 既簡單又方便； 啟動 Visual Studio， 然後管理一堆專案，複製一堆 DLL 檔案 (都是在圖形使用者介面中處理的，不能很好的指令碼化，版本控制， 自動化， 以及審查 )其實挺尷尬的，而且容易出錯，擴充套件性又不好。總之， 這種機制在很大程度上受限於.NET 語言。

　　1.5支援原生正規表示式

　　正規表示式（regexen或者regexes）對於分析工作來說就像會算術一樣的基礎，對於大量的文字處理任務來說它們是首選（經常是唯一選擇）。不支援正規表示式的資料分析工具就像一個沒有座的自行車一樣，你仍然可以用它，但是充滿痛苦（菊花都殘了當然痛—譯者加）。

　　PostgreSQL有開箱即用的正規表示式支援。看幾個例子：

　　取得所有以重複數字並且緊跟母音字母開頭的行：

SELECT * FROM my_table WHERE my_field ~ E'^([0-9])\\1+[aeiou]';

　　取得某一個欄位中第一個出現的單獨的十六進位制字串：

SELECT SUBSTRING(my_field FROM E'\\y[A-Fa-f0-9]+\\y') FROM my_table;

　　將一個 字串以空白字元分割，並且以單行的形式返回每一部分：

SELECT REGEXP_SPLIT_TO_TABLE('The quick brown fox', E'\\s+');
-- Returns this:

-- | column |
-- -----------
-- | The    |
-- | quick  |
-- | brown  |
-- | fox    |

　　查詢一個字串中最少有10個字母的單詞（不區分大小寫）：

SELECT REGEXP_MATCHES(my_string, E'\\y[a-z]{10,}\\y', 'gi') FROM my_table;

　　MS SQL Server有 LIKE ，SUBSTRING，PATINDEX 等等，不過它們與恰當的正規表示式支援不具可比性（如果你對此懷疑，你可以嘗試使用它們來實現上面的例子）。有第三方的庫可用於MS SQL Server，它們不像PostgreSQL的支援那樣好，並且獲取和安裝它們會增進管理開銷。

　　還要注意到PostgreSQL的支援擴充套件程式語言特性也讓你有好幾個其他的正規表示式引擎可用，當然也包括它們的各種特性。比如Python的正則庫提供的對正向和負向後行斷言的支援。這正符合PostgreSQL的一貫作風，給你幹好工作的所有你需要的工具。

　　1.6自定義聚合函式

　　這是一個PostgreSQL和MS SQL Server兩者都提供的一個技術上的特性。不過，在實現上卻有巨大的不同。

　　在PostgreSQL中，自定義聚合很方便並且使用簡單，產生了可以快速解決問題和可維護的程式碼：

CREATE FUNCTION interest_sfunc(state JSON, movement FLOAT, rate FLOAT, dt DATE) RETURNS JSON AS
$$
state.balance += movement;  //payments into/withdrawals from account
if (0 === dt.getUTCDate()) //compound interest on 1st of every month
{
  state.balance += state.accrual;
  state.accrual = 0;
}
state.accrual += state.balance * rate;
return state;
$$ LANGUAGE plv8;

CREATE AGGREGATE interest(FLOAT, FLOAT, DATE)
(
  SFUNC=interest_sfunc,
  STYPE=JSON,
  INITCOND='{"balance": 0, "accrual": 0}'
);

--assume accounts table has customer ID, date, interest rate and account movement for each day
CREATE TABLE cust_balances AS
SELECT
  cust_id,
  (interest(movement, rate, dt ORDER BY dt)->>'balance')::FLOAT AS balance
FROM
  accounts
GROUP BY
  cust_id;

　　簡單吧？自定義的聚集函式主要關注的是內部的狀態和我們輸入新值給這個聚集函式時修改這個狀態的方法。在這個例子裡，我們假設一開始每個客戶的餘額為零，而且累計利息也為零，接著我們每天適當地進行利息累計，並對每天的支付和撤消記賬。在每個月的1號，我們進行利息複合。注意：這個聚集函式接納ORDER BY子句（因為它與SUM、MAX和MIN不同，它是排序相關的），另外PostgreSQL有操作符可以從JSON物件裡提取對應的數值。因此，我們在28行程式碼裡建立了每月對銀行賬戶進行利息複合的框架，使用這個框架就可以計算出最終的賬戶餘額。如果打算給這個方法增添功能（比如根據借/貸額度進行利率修改，異常檢測），這一切都可以在轉換函式裡實現，只要採用適合於實現複雜邏輯的語言編寫即可。（不幸的是：我看到許多組織機構使用較差的工具花費數萬英鎊經過數週的努力試圖完成同樣的任務。）

　　而MS SQL 伺服器要實現這樣的任務就異常困難。

　　順帶說明一下，上面的第二個連結裡的例子實現了簡單的字串連線聚集。注意：實現如此簡單的功能需要大量的程式碼和技巧（而PostgreSQL內部提供了此功能，隨拿隨用。這可能是因為這個功能有用！）MS SQL伺服器還禁止在聚集函式裡指定排序，使用這樣的函式無法完成我現在要完成的任務-在MS SQL伺服器裡，字串連線的順序是隨機的，因此使用這個函式查詢的結果就是無法確定的（即每次執行結果都可能不同），而且這樣的程式碼也不會通過質量審查的。

　　缺乏排序支援還可能使得以前編寫的程式碼無法執行，比如上面計算利息的例子。正如我所說，你無法通過使用MS SQL伺服器自定義的聚集函式完成當前的任務。

　　（實際上，可以讓MS SQL伺服器使用純SQL語句實現結果可以確定的字串連線聚集，不過，你需要多次使用RECURSIVE查詢功能來實現。雖然可以把它當作一次有趣的學術性練習，但是這會生成執行速度慢、無法閱讀且無法維護的程式碼，因此它不是一個可以使用在現實世界的解決方案。）

　　1.7Unicode 支援

　　ASCII一統江湖的日子已經一去不復返了，“字元（character）”和“位元組（byte）”是可互換的以及“外國（foreign）”（從Anglocentric觀點來看）文字是具有異國情調的異常。合適的國際語言支援不再是可選項。

　　所有問題的解決方案是Unicode。外面有許多對Unicode的誤解。它不是一個字符集，不是一個內碼表，不是一個檔案格式，並且它和加密也沒有任何關係。探索Unicode是如何工作的很讓人著迷的事，但是這個超出了本文的範圍，我打心底裡推薦你Google它並且試試幾個例子。

　　Unicode對應的重要的資料庫功能如下：

  • Unicode編碼文字（對於我們的目的而言，這意味著只能使用UTF-8或者UTF-16）是一個可變長度的編碼。 在UTF-8中，一個字元可以佔1、2、3或4個位元組。 在UTF-16中，它可以佔2或4個位元組。 這意味著進行獲取子字串、計算字串長度等操作時，需要知道它們是Unicode編碼的，這樣操作才能正常進行。

  • 並非所有位元組序列都是合法的Unicode。 操作合法的Unicode編碼的文字而不知道它是Unicode編碼，就像是生成一些不合法的Unicode文字。

  • UTF-8和UTF-16互不相容。 如果你每一個檔案使用一種型別，然後把它們合併起來，你（可能）會得到一個既不是UTF-8格式也不是UTF-16格式的檔案。

  • 對於大部分適合ASCII的文字而言，UTF-8格式的空間效率是UTF-16格式的2倍。

　　PostgresSQL支援 UTF-8 。預設情況下，它的 CHAR 、 VARCHAR 、 TEXT 型別採用 UTF-8 格式，這意味著這些型別僅接受 UTF-8 資料和適用於 UTF-8 格式的所有轉換，從字串級聯到使用正規表示式搜尋，這些都是顯示的 UTF-8 格式。上面這些都是有效的。

　　MS SQL Server 2008 不支援 UTF-16；但它支援 UCS-2 --  UTF-16 的一個過時的子集(a deprecated subset)。多數情況下，不會有問題. 但是, 偶爾會損壞資料。因為，它把文字當成寬字元(如 2 個位元組)處理，所以遇到 UTF-16 的 4-位元組字元時，會毫不猶豫地切掉一半。輕者，資料損壞。嚴重的時候，會導致你的工具鏈中某些東西出現問題，後果非常嚴重。那些替微軟辯護的人會立刻反駁：這是不可能的。因為，在 Unicode 的基本多文平面(basic multilingual plane)外面，還包含其他東西。這種說法實在是荒謬。資料庫就是用來儲存，讀取和修改資料而已。 一個資料庫，如果因為存入錯誤的資料而出問題，那不是跟下載錯誤的檔案會導致路由器崩潰一樣荒唐？

　　2012年後MS SQL Server版本都支援UTF-16，如果你確定你為資料庫設定了相容UTF-16的校驗規則。 這在2012年後的版本中是一個莫名其妙的功能。不過，遲到總比不到好。

　　1.8. 可以正常使用的資料型別

　　一個普遍誤解的概念是所有的資料庫有相同的資料型別 – INT、 CHAR、 DATE等等。 不是這樣的。 PostgresSQL的資料型別系統非常有用、直觀、沒有 bug或效率低下的煩惱，顯然資料型別的設計使用生產效率為核心。

　　相比之下，MS SQL伺服器的型別識別系統就像是Beta版的測試軟體。它不具有PostgreSQL型別識別系統所具有的功能集，而且由於諸多陷阱使得使用者異常困惑，從而讓粗心的使用者掉入缺陷網中。我們仔細對比一下：

  • CHAR、VARCHAR和其他字元型別

    • PostgreSQL：它的文件積極鼓勵你只使用TEXT型別。它是一個高效能的、UTF-8編碼的文字儲存型別，可儲存的字串高達1GB。它還支援PostgreSQL具有的所有文字操作：簡單地字串連線和字串提取；正則搜尋、正則匹配和分割；全文搜尋；強制型別轉換；字元轉換等等。如果你有文字資料，那麼就可以定義為TEXT欄位，並儲存。再者，由於TEXT欄位(或者是CHAR或者VARCHAR欄位)的內容必須採用UTF-8編碼，因此就不存在編碼相容的問題。由於UTF-8是事實上的通用的文字編碼。因此把文字轉換成此種編碼就非常容易和可靠。再者因為UTF-8是ASCII編碼的超集，所以此種轉換通常非常簡單或者說不需要轉換。正因為這樣，它才執行的非常好。

    • MS SQL伺服器：這是一次非常痛苦的經歷。TEXT和NTEXT兩個型別都有，而且可儲存的大小達2GB。然而，令人沮喪的是它們都不支援型別轉換。另外，微軟建議不要使用它們 -在將來的MS SQL伺服器版本里，這兩個型別都將被刪除。你應當取代性地使用CHAR、VARCHAR以及它們的N字首版本。很不幸的是：VARCHAR(MAX)具有很差的效能，而且VARCHAR(8000)(即緊鄰的最大儲存量，由於某些原因)最大隻能儲存8000位元組。（NVARCHAR最大隻能儲存4000字元。）想一下PostgreSQL怎麼樣在一個每個資料庫上使用單一的文字編碼，從而讓一切平穩地執行的？ MS卻沒有做到這樣：

      就像早期版本的SQL伺服器那樣，在編碼轉換期間發生資料丟失是不會彙報的。[ 連結]

      換句話說，MS SQL伺服器可能會損壞資料，而且只有遇到其他非資料錯誤時你才知道。很簡單，這就是個麻煩製造者。可對資料進行沉默性更改、損壞或者丟失的資料分析平臺就是一個巨大的缺陷。想想不使用伺服器而使用昂貴的ECC RAM做為防止因宇宙射線而造成的資料損壞荒謬行為，然後在其上執行軟體，那麼無論如何都可能造成資料損壞。

  • 日期和時間型別

    • PostgreSQL:你可以使用DATE、TIME、TIMESTAMP和TIMESTAMP WITH TIME ZONE型別，所有這些型別都會做到你期望那樣。它們還具有極為出色的範圍和精度，支援從公元前５世紀到未來30萬年的毫秒級精度。它們也接受多種格式的輸入，最後一個是它們還提供對時區的完整支援。它們還可以與UNIX時間互轉，這一點在與其他系統的互操作方面就非常重要。它們還可以取特殊值infinity和-infinity。這不是一個形而上的、神學的、哲學方面的語句，而是一個非常有用的語義結構。例如，設定使用者密碼的過期日期為infinity，表示他們不需要修改密碼。處理這種事情的標準方式是使用NULL或者遙遠的未來的某個日期，不過，他們是一群愚蠢的黑客－他們不但把不精確的資訊儲存到資料庫，而且還需要編寫應用邏輯來彌補。當開發人員看到NULL或者3499-12-31的時候，會怎麼樣呢？如果你幸運，那麼他會認識到要進行金鑰握手，而不會因此產生任何混亂。如果不幸運，那麼他會假設這個日期是未知的，或者這個日期確實指的是第四個千年，那麼你就會遇到問題。像這樣的黑客、工作場景和湊合的程式設計的累積結果就會生成一個不可靠的系統、不幸福的程式開發人員，從而增加商務上的風險。像infinity和-infinity這樣非常有用的語義結構就允許你說出你所想，然後寫出一致的、可讀性好的應用邏輯。它們還支援INTERVAL型別，它非常有用，在這一節之後有專門的一節介紹。日期和時間型別的強制轉換為其他型別和它們之間的轉換非常簡單和直觀－你可以強制轉換任意一日期和時間型別為TEXT，to_char和to_timestamp函式給你提供最大的靈活性，可以讓你使用格式字串實現兩個方向的互轉。例如：

      SELECT to_char('2001-02-03'::DATE, 'FMDay DD Mon YYYY');--這條語句會生成字串"Saturday 03 Feb 2001"

      另一方向的轉換如下：

      SELECT to_timestamp('Saturday 03 Feb 2001', 'FMDay DD Mon YYYY'); --這條語句會生成時間戳2001-02-03 00:00:00+00

      像往常一樣，這一切都執行的非常好！作為一名資料分析師，我非常關心資料庫對日期的處理能力，因為日期和時間是以許多不同的格式來表現的，而且對分析來說它們通常都非常重要。

    • MS SQL伺服器: 日期型別只有由正的四位數字組成的年份，因此日期侷限在公元0001到公元9999之間。它們也不支援infinity和-infinity。它們也不支援interval型別，因此日期的演算法乏味笨拙。你可以在它們與UNIX時間間互相轉換，不過處理的技巧是在UNIX新紀元1970-01-01T00:00:00Z上增加秒數，因此你需要知道UNIX新紀元，而且要把它硬編碼到應用裡。日期轉換在這兒特別值得一提，是因為即便遵循MS SQL伺服器粗略的標準，日期轉換仍然讓人感覺到吐血般的恐怖。使用CONVERT函式替代了PostgreSQL的to_char和to_timestamp，不過它執行方式如下：

      SELECT CONVERT(datetime, '2001-02-03T12:34:56.789', 126); --這條語句會生成datetime值: 2001-02-03 12:34:56:789

      就是這樣-你只要知道“126”是把字串轉換為某種格式的datetime的編碼即可。MSDN給出與這些奇奇怪怪數字對應的表。我沒有給出與PostgreSQL相同的例子，這是因為我沒有找到與格式"Saturday 03 Feb 200"相對應的奇怪的數字。如果某人給出的資料含有這個日期型別，那麼我猜你將不得不做一些字串處理的工作了（很遺憾，在MS SQL伺服器裡幾乎沒有字串處理功能。） 

　　INTERVAL

  • PostgreSQL:INTERVAL型別表示一個時間段，如“30微秒”或者“50年”。它也可以是負數，這看起來有些不合常理，如果你知道一個單詞“以前（ago）”存在的話就不會了。PostgreSQL也知道“ago”，事實上它也接受類似‘一天前（1 day ago）’字串作為interval值（這個值在內部用-1天的時間段表示）。interva讓你有一個直觀的日期計算，並且作為第一類的資料型別來儲存持續時間。它們像你期望的那樣準確，並且可以自由的改造和轉換成任何有意義的資料，也可以由任何有意義的資料轉換而來。

  • MS SQL Server：不支援interval資料型別。

  • 陣列

    • PostgreSQL：以一等資料型別的方式支援陣列，這就意味著資料表的欄位、PL/PGSQL中的變數、函式的引數等等都可以是陣列。陣列可以包含你喜歡的任意資料型別，包括其他陣列資料型別。這一點非常，非常有用。你可以使用陣列完成以下事情：

      • 儲存呼叫具有任意數量返回值函式的所返回的結果，比如正則匹配函式；

      • 把字串表示為多個整型字表示的ID，可用在快速文字匹配演算法裡。

      • 對屬於不同分組的多個資料值進行聚集計算，對跨表計算尤其有用

      • 在不需要使用昂貴的連線的情況下，對多個資料值執行行操作。

      • 能夠精確、確切地表示工具套件中其他應用中的陣列資料。

      • 給你工具套件中的其他應用傳送陣列資料。

      我認為沒有程式語言不支援陣列的，除非像   和   那樣瘋狂的語言。   陣列非常有用 ，因此非常普遍。任何不支援陣列的系統就是個殘缺不全的系統，尤其是不支援陣列的資料分析平臺更是如此。 
    • MS SQL伺服器:不支援陣列。 

  • JSON

    • PostgresSQL: 完全支援JSON，包括很多實現JSON型別和表欄位型別相互轉化的工具函式。 解析（json物件轉化為字串）和反解析（字串轉化為json物件）通過簡單的元件進行處理，這是PostgresSQL中一個智慧的、健壯的規則。 JSON也可以在PL/V8過程化語言中以你期望的方式進行使用 – 實際上，一個JSON型別的內部狀態在一個自定義的聚合（詳見這裡，它的轉化函式使用PL/V8編寫）中提供了一種非必要的/必要的兩全齊美的效果，這個 功能是如此強大、方便，感覺像是騙人的。JSON（以及它的變種，如JSONB）毫無疑問是web和其它一些資料平臺（如MongoDB、 ElasticSearch，實際上包括使用RESTful風格介面的任意系統）上進行資料傳輸的標準格式。 有理想的分析即服務供應商（或開發人員）可以留意下。

    • MS SQL Server： 不支援JSON。

  • HSTORE

    • PostgreSQL:HSTORE是PostgreSQL的一種擴充套件，它用一種資料型別實現了對鍵值對的快速儲存。與陣列相似，這一種資料型別非常有用，因為幾乎每一種程式語言都有這一設計理念（也可以這麼說，因為這一設計理念非常有用，所以幾乎每一種程式語言都採納它）。JavaScrip中的物件，PHP的關聯陣列，Python中的字典，C++中的有序對映(std::map)和無序對映(std::unordered_map)，Go中的map等等。鍵值對儲存這一設計思想如此重要和有用，以致於把它做為一種NoSQL資料庫主要的儲存模型。好吧，我們就稱這樣的儲存模型為鍵值儲存。這一資料型別還有我們意想不到的一些非常有趣的用法。近期，有位同事問我是否有一種好的方法能把文字陣列中重複的項刪除。我採用以下語句解決這個問題：

      SELECT akeys(hstore(my_array, my_array)) FROM my_table;

      即把陣列放入到HSTORE的鍵值對裡，這樣就會強制刪除重複的項（因為不允許鍵重複），然後再從HSTORE中提取鍵就可以了。這再一次體現了PostgreSQL的功能多樣性。

    • MS SQL伺服器：不支援鍵值對儲存。

  • 範圍型別

    • PostgreSQL:範圍型別能夠很好的體現範圍這一概念。每一個資料庫開發人員都層遇到過start_date和end_date這樣的欄位，而且他們中的大多數人還不得不編寫邏輯程式碼來檢測是否出現重疊的現象。一些人甚至會發現在範圍上採用BETWEEN進行連線這一處理方法由於很多原因而深陷錯誤的泥沼之中。PostgreSQL的處理方法是把時間範圍定義為一級資料型別。你不僅僅可以把時間（INT或者NUMERIC或者其他型別）範圍放入單獨的資料值中，而且還可以使用大量的內建操作符來安全快速地對範圍進行維護和查詢。你甚至還可以使用為範圍特別開發的索引，這樣就可以大大地提高使用操作符進行查詢的速度了。簡言之，PostgreSQL非常重視範圍，而且還提供了高效處理範圍的各種工具。我不想把這篇文章寫成一系列PostgreSQL文件的連結，但是我還想提一下，我建議你親自去閱讀一下（，如果預定義的型別不能滿足你的要求，那麼你可以自己定義自己需要的型別。你不需要更改原始碼，PostgreSQL資料庫提供了許多方法做這些事情）。

    • MS SQL伺服器:不支援範圍型別。

  • NUMERIC和DECIMAL

    • PostgreSQL:NUMERIC(以及DECIMAL-它們兩個同義）幾乎可以達到任意精度：在小數點之前它可以支援131，072位十進位制數，在小數點後可支援16,383位十進位制數。如果你正在管理一家銀行，進行技術性計算，在彗星上實現飛船著陸或者做一些無法容忍四捨五入所帶來的誤差，那麼你就可以使用這種型別。

    • MS SQL伺服器：NUMERIC(以及DECIMAL-它們兩個同義）總體上支援的精度是38個十進位制有效位。

  • XML(很慚愧，XML已經有些過時了...)

    • PostgreSQL:支援XML資料型別，PostgreSQL資料庫有大量的函式進行XML處理。支援Xpath查詢

    • MS SQL伺服器：終於，見到好的訊息了！MS SQL伺服器也支援XML資料型別，同時提供大量對XML處理的方法。

　　1.9. 指令碼化

　　PostgreSQL 完全可以由命令來操作. 因為它在作業系統中就是這麼用的 (Windows 作業系統是個例外). 這種方式既高效, 又安全. 如果有需要, 你甚至可以在手機上, 通過 SSH 登入伺服器配置 PostgreSQL(這種事, 本人幹過幾次).  通過指令碼, 能執行諸如: 自動部署, 效能調節, 安全, 管理 和分析任務等操作.  跟圖形使用者介面不一樣, 指令碼能被複制, 版本控制, 文件化, 自動化, 審查化(Reviewed), 批量化, 以及差異化(Diffed). 對於重要的工作來說, 文字編輯器和命令列才是王道.

　　MS SQL Server 通過圖形使用者介面(GUI)操作.  就算加上 Powershell 的輔助, 我也不知道它能在多大程度上實現自動化; 如果你在 Google 上搜 MS SQL Server 的用法, 得到的答案會基本上是 "在資料庫上點右鍵, 然後選 任務...(Tasks...)". 在速度慢或者高延時的網路連線中, 使用文字命令列的效果, 遠比用圖形使用者介面好的多.  在寫這篇文章的時候,  我正準備通過 VPN,  登入一臺位於 3,500 英里外的伺服器, 執行管理操作. 這裡的 WiFi, 訊號相當不穩定. 謝天謝地, 我用的是 Ubuntu/PostgreSQL.

　　(真的有地球人通過圖形使用者介面管理伺服器嗎?)

　　1.10.很好地整合了外部語言

　　由程式設計環境連線並使用PostgreSQL非常、非常簡單，這是因為libpq,即PostgreSQL的外部應用程式介面(API)，設計的非常完美而且文件也非常完備。這就意味著編寫嵌入到PostgreSQL裡的應用將非常簡單方便，這也使得PostgreSQL功能更多，更適合於應用在資料分析上。我曾多次用C或者C++編寫了簡短的程式，它可進行PostgreSQL連線，獲取資料以及對獲取到的資料進行大量計算，其中用到了多執行緒或者特殊的CPU指令-不過不適合對資料庫本身進行資料填入。我還寫過這樣的C程式：它使用了setuid，從而允許在PostgreSQL裡以普通使用者執行某些管理員才能完成的任務。快速而且簡練地做到這些真的很方便。

　　MS SQL伺服器整合的外部語言的方法各有不同。有時候你需要安裝其他驅動。有時候你需要建立類來儲存你所查詢的資料，這種情況下就意味著你在編譯時就要知道資料是什麼樣子。最為重要的是，它的文件讓人困惑、一團糟，要做成一件事就要

　　耗費不必要的時間，而且非常痛苦。

　　1.11 文件

　　資料分析基本上意味著你得是一個“萬事通”。我們使用各種廣泛的程式語言和工具。（在我的腦子裡，我工作中使用的程式設計/指令碼語言有PHP、JavaScript、Python、R、C、C++、Go、三種SQL方言、PL/PGSQL和Bash。）不要期望你可以預先學習所有你需要的東西。幹好工作經常依賴於閱讀文件。一個有良好文件的工具更有用，它使得分析師更高產並且完成高質量的工作。

　　PostgreSQL的文件非常優秀。它涵蓋了所欲的東西，但是又不只是一個參考手冊，其中有很多例子、提示、有用的建議和指南。如果你是一個高階程式設計師並且想要真正的深入，你可以很容易的閱讀PostgreSQL的原始碼，所有這些都是開放的並且可以自由獲取。這個文件也很有幽默感：

　　第一個世紀開始於公元0001-01-01 00:00:00 ，雖然當時他們並不知道。這個定義在所有使用公曆的國家使用。沒有世紀編號為0的，直接從-1世紀跳到了1世紀。如果你對此不贊同的話，請寫下你的抱怨到這個地址：羅馬教廷聖彼得大教堂教皇收。

　　MS SQL伺服器的文件都在MSDN上，而且很不友好、龐大、煩亂。由於微軟是個大企業，而且其客戶基本很保守、缺乏幽默感，因此這樣的文件就很“適合商務”-即囉囉嗦嗦、令人厭煩和枯燥。它不但在開發日期演算法的時候沒有參考天主教的歷史，而且還異常乏味，藏匿在不必要的分類以及炫耀式的官方術語之下，難以理解。試試這個：進入MS SQL伺服器2012的 產品文件頁面，試試能從這裡獲取一些有用的資訊。或者閱讀下面摘錄的部分（我承諾，絕不是精心挑選的）：

部分report定義就是XML格式的report定義檔案的一部分。在可以通過建立report定義來建立部分report，然後在這個report裡選擇report項，把它們做為多個部分report單獨進行釋出。

　　是不是一開始就不知道“report"是究竟是什麼？

　　1.12.日誌記錄確實有用

　　MS SQL伺服器的日誌分散在幾個地方-錯誤日誌、Windows事件日誌、分析器日誌、代理日誌和安裝日誌。要實現對這些日誌的訪問，你還需要各種級別的許可權，而且還需要使用多個工具，其中一些工具是隻有圖形使用者介面下才可以執行的。也許像Splunk這樣的軟體能幫助你對這些日誌進行自動收集和解析。我沒有試過，我也不知道別人有沒有試過。有關這個問題的谷歌搜尋結果得到的資訊少的讓人吃驚，幾乎沒有什麼用處。

　　預設情況下PostgreSQL的所有日誌是儲存在一個地方。你可以通過更改文字檔案的幾個設定，讓資料庫記錄日誌記錄為CSV檔案（由於我們現在正在討論的是PostgreSQL，因此這兒的CSV是正常的，而不是非正常的）。你很容易設定日誌級別為任何級別：由“不記錄任何日誌”到“全面記錄分析和除錯的輸出”。日誌文件甚至包含CSV格式的日誌檔案匯入的目的表的DDL語句。你還可以把日誌記錄到標準錯誤或者系統日誌或者Windows事件日誌裡（當然，假設你正在Windows系統上執行PostgreSQL)。

　　日誌本身即是人可閱讀的，也是機器可閱讀的，而且包含的資料可能是系統管理員非常關注的。誰在什麼地點，什麼時間登入和退出？正在執行哪一條查詢語句，是由誰來執行的？他們使用了多長時間？每一次批處理裡提交了多少個查詢？由於資料是以非常完善的CSV格式儲存的，因此在R或者PostgreSQL自身或者Python的matplotlib或者其他你喜歡的軟體裡，對其進行視覺化和分析都非常容易。像top,iotop和iostat這些Linux應用所提供的豐富的資訊遠超過日誌提供的資訊，因此你可以非常容易、可靠地使用你可能需要的所有伺服器遠端測控技術。

　　1.13支援

　　PostgreSQL該如何贏得這一局呢？每個人都知道大型商業公司為昂貴的旗艦企業級產品提供難以置信的技術支援，而同時免費軟體則一點也沒有。

　　當然，這都是廢話。商業產品當然為支援它們的人民提供支援，因為它們花了錢的。在滿足SLA條款時他們總是做到最少。在我敲這篇文章時，我知道有幾個IT專家正在等待一個主要硬體供應商來幫助它們解決一個 £40,000伺服器的效能問題。它們已經和供應商談了數週時間。應供應商的要求，他們花費時間和精力來執行擴充套件測試和基準測試。目前，供應商的臉上混合著無能、低效和淡漠。而那個£40,000伺服器正坐在那非常非常慢的執行著，它的使用者每週工作70小時以試圖保持進度。

　　數年來，我見過昂貴商業軟體的許多許多問題，包括從bug到效能問題、相容性問題、不完備的文件等一切問題。有時這些問題導致使用者熬至深夜或週末加班，有時又導致錯過最後期限並激怒客戶，有時又冒法律和聲譽的風險。

　　每一次都發生同樣的事情：終端使用者混合著血、汗水、淚水、Google和熬夜將問題解決。我從未見過供應商趕來救急並將一切都搞定。

　　那麼PostgreSQL的支援是什麼樣的呢？有兩次我向PostgreSQL郵件列表請求幫助，在24小時內我從 Tom Lane那收到了回覆。花了點時間點開連結並閱讀wiki，那哥們不只是PostgreSQL的首席開發者，還是一個知名得計算機程式設計師。沒啥說的，他的建議就是好的建議。其中一次，我問了一個關於實現跨函式呼叫持久記憶體分配的最好方法的問題，Lane用我應該學習的PostgreSQL的特性回覆了我，並且給我的問題提出解決方案，另外他就我的試驗性解決方案（一個C靜態變數）為什麼是垃圾這個問題，給我列出了一份非常好的原因清單。這種支援你是買不到的，但是你可以從熱情的開源開發者社群取得支援。(⊙o⊙)哦，我有沒有提到那個資料庫軟體，還有那些來自於廣受好評的程式設計師的充滿幫助的忠告和建議總共花費了 £0.00？

　　我說的支援, 是指技術上真正解決問題的支援. 有些人(一般都是那些沒有過這些產品的) 一提到技術支援合同, 想到更多的是法律方面的問題 – 他們並不關心是不是會真的獲得幫助. 純粹就是想找人臭罵幾句, 發洩一下.  我在這裡有討論.

　　(如果你真的想花錢購買技術支援, PostgreSQL 有許多提供專業技術支援的機構可以選擇.  跟那些以銷售商業軟體為主, 提供技術支援為輔的供應商不同.  這些機構提供技術支援的質量決定了他們的生死存亡. 所以這方面沒什麼好擔心的.)

　　1.14靈活的，可指令碼化的資料庫轉儲

　　我已經談論過可指令碼化，不過資料庫轉儲是如此重要，所以他們在本文中佔有一席之地。PostgreSQL的資料庫轉儲特別靈活，採用命令列驅動方式（使得它很容易實現自動化和指令碼化）並且記錄良好（與PostgreSQL其他部分一樣）。這使得資料庫遷移、複製和備份這三種重要和嚇人的任務可控、可靠和可配置。而且，備份可以以空間壓縮的格式或者純文字的SQL來處理完整的資料，兩者都是人類可讀並且可執行。可以備份單個表或整個資料庫叢集。使用者可以按他喜歡的方式去做。只需要一點點工作和小心的選擇幾個選項，甚至可以生成一個僅有DDL純文字的SQL語言的PostgreSQL備份，該備份可在不同的RDBMS中執行。

　　而MS SQL Server的備份則是以一個專有的、無記錄的、不透明的二進位制格式進行。

　　1.15可靠性

　　無論是 PostgreSQL 還是 MS SQL Server 都不希望發生意外，不過MS SQL Server有一個奇怪的失敗模式，我遇到過不止一次：它的事物日誌變的巨大並且阻止了資料庫正常工作。理論上日誌可以被截斷或刪除，但是文件中對此類動作又充滿了可怕的警告。

　　而PostgreSQL只是坐在那裡並且將工作做好。在日常使用中我從來沒有遇到過一次PostgreSQL資料庫意外。

　　1.16 易於安裝和更新

　　這個有什麼關係嗎？當然有。基礎設施的靈活性比以往任何時候都要重要，並且這個趨勢只會繼續下去。多年穩坐不動的胖伺服器安裝的日子已經一去不復返。現在全部都是關於快速、可靠、靈活的配置和跟上尖端技術。常言道，時間就是金錢。

　　我裝過MS SQL Server幾次。我已經記不清自己到底安裝PostgreSQL多少次了，很可能最少有50次了。

　　安裝MS SQL Server非常的慢。它需要下載大量資料（現在還有誰使用物理媒介進行安裝？）而且臭長，只有死板的進度條說明這貨還活著。如果你沒有安裝正確版本的.NET或者正確的Windows服務包的話，你有可能安裝失敗。這些都需要你的系統管理員找一整塊時間來做。

　　安裝PostgreSQL的canonical 方法是簡單至只敲一條命令（從一個Linux倉庫），就像這樣：

sudo apt-get install postgresql

　　這需要花費多長時間？我只是通過在雲中執行一個廉價的虛擬機器，然後用上面的命令安裝PostgreSQL來進行測試。只花了 16秒。這是下載和安裝的全部時間。

　　至於更新，任何軟體支援的Linux repo是非常容易從repo更新補丁。因為repo很聰明，PostgreSQL不太臃腫，下載的更新和應用，都是小而快的，並且是有效的。

　　我不知道怎麼樣容易地讓MS SQL Server更新。我知道，很多在生產中的MS SQL Server在某些組織的版本還是2008 R2...

　　1.17.構建軟體捐贈模組

　　貌似PostgreSQL這一大堆的設定還不夠，它又弄了一堆叫做構建軟體捐贈模組的擴充套件。裡面有一些函式，型別以及對伺服器核心設定元素不那麼重要的的實用功能的庫。有模糊串匹配，快速整形陣列處理，外部資料庫連結，密碼，UUIO生成，樹形資料型別和負載的庫。還有一些模組甚至什麼也不做，只是為了讓開發者和高階使用者開發自己的擴充套件和功能。

　　當然，這些擴充套件安裝十分瑣碎。比如安裝fuzzystrmatch的擴充套件，需要這麼做：

CREATE EXTENSION fuzzystrmatch;

　　1.18. 自由與免費

　　PostgreSQL 既自由又免費， 這兩樣都很重要。 

　　首先，PostgreSQL 是個開源軟體， 在授權方面很自由。也就是說，你可以隨心所欲地使用和修改它。包括髮布包含或基於它的軟體。 不論何時， 何地， 想怎麼用就怎麼用。 

　　免費包含兩個重要方面。 首先， 如果你也像我一樣，在大機構中就職，花公家的錢，就會牽扯到許多繁文縟節。各種繁雜手續的拖延， 耗盡精力和激情； 抵制了創新。其次， 由於 PostgreSQL 是免費的， 許多開發人員， 實驗人員，黑客，學生， 發明家， 科學家等等 (基本上都是窮書生)都在使用。慢慢地發展成為一個強大的社群。來自這群精英們的支援和貢獻不斷增長。 結果越做越好， 創新能力越來越強， 各種問題的解決方案也越來越多。更多的時間和精力用來解決更重要的問題。 

　2. 反駁

　　讓我不解的是, 大家經常無視上面的論點和論據， 用一些錯誤的， 奇怪的， 甚至荒唐透頂的觀點來反駁 PostgreSQL 。 比如：

　　2.1. 不是說大牌廠商更可靠麼!

　　絕對不是。這就好比說 "沒人會因為購買 IBM 而被炒魷魚"。  搞笑的是, 如果你谷歌下， 就會發現，排在第一的是 Wikipedia 的這篇文章，  恐懼，不確定，懷疑 。 最搞的是, 裡面第一個提到的就是微軟。我發誓我沒動過 Wikipedia 那篇文章，我看到的時候， 它就是這麼寫的。 

　　在給客戶提供服務的資料分析領域(client-serving data analytics)，容不下半點差錯。如果你因為搞砸了一份工作而損害了自己的名聲，別指望軟體供應商能幫你挽回。如果被起訴， 倒是有可能從他們那裡得到一些賠償， 前提是， 他們確實有錯。 MS SQL Server 在技術上倒是沒有什麼錯。他們不過是釋出了一件垃圾產品，然後坦白的在文件裡告訴你，這件產品有多爛。它執行起來就像最初設計時期望的那樣。 問題是它的設計本來就很爛。不能因為你挑選資料庫的時候， 不夠盡職盡責， 就去控告微軟。 

　　就算你成功地讓供應商背上黑鍋，已經搞砸的工作和憤怒的客戶， 是不可逆轉的。他們才不管是不是 MS SQL Server's 錯誤地把 UTF-16 當成 UCS-2 處理, 導致在進行子字串處理的時候，代理對(surrogate pair) 被截斷。結果無法識別引起錯誤的關鍵字(incriminating keyword)。輕者，他們依然想要分析結果(也許還要給他們些折扣)。嚴重的，他們說你做的東西不能用，直接不要了。當他們的文件沒有半點歉意的告訴你，你的資料可能會被悄無聲息的破壞掉，而你卻把全部的希望都寄託在他們的資料庫上。一旦出問題， 你覺得你能怪他們嗎？

　　降低風險的最好辦法就是把事做對。優秀的工具能幫你輕鬆做到這一點。優秀的工具， 我指的是 PostgreSQL 啦。 

　　2.2 不過，如果PostgreSQL的作者掛了怎麼辦？

　　假如MS SQL Server的作者同樣屎掉了會發生什麼——結果是一點事沒有。當然了，沒啥說的“PostgreSQL的作者”和“MS SQL Server作者”一樣毫無意義。根本就沒有這回事。

　　有個前輩，是一個IT基礎設施監管人到是問過我這個問題（是關於Hadoop，不是PostgreSQL）。大家好像對所有的開源軟體的認識都有誤區，就好像這些軟體是躲在媽媽的地下室的獨行俠寫的一樣。這顯然不是真的。大型開源專案如PostgreSQL和Hadoop是由擁有高階技能的開發者團隊所寫，他們經常受到商業贊助。在它們的核心，PostgreSQL的開發模式就像MS SQL Server的開發模式一樣：有某個組織向一個大型程式設計師團隊支付報酬。不會有單點失效的問題。

　　但是兩者至少有一個重要的差異： PostgresSQL的原始碼是開放的，因此大批高階程式設計師能夠進行程式碼走查、程式碼優化、貢獻程式碼、提高程式碼的質量並理解程式碼的邏輯。 這是PostgresSQL為什麼比MS SQL Server更好的一個原因。

　　關鍵一點，由於開源軟體往往由那些非常關心程式碼質量的人編寫（經常是由於他們打賭他們可以保證軟體儘可能好用），因此軟體開發通常採用最高的標準（ PostgreSQL, Linux, MySQL, XBMC, Hadoop, Android, VLC, Neo4JS, Redis, 7Zip, FreeBSD, golang, PHP, Python, R, Nginx, Apache, node.js, Chrome, Firefox...）。 另一方面，商業軟體通常由某一委員會設計，在公司的格子間裡開發，並且編碼的時候通常沒有適當的指導和靈感（ Microsoft BOB, RealPlayer, Internet Explorer 6, iOS Maps, Lotus Notes, Windows ME, Windows Vista, QuickTime, SharePoint...）。

　　2.3 但是開源軟體不安全、不穩定、不值得信任、不適用於企業級生產等等！

　　這些東西都沒法說。說這些的人都是些很無知的人，你應該忽視他們，或者你自我認為很大方，你可以教育教育他們。好吧，我認為我是一個大方的人：

　　安全：一箇舊的誤解是閉源軟體更安全，我會簡要總結幾個好的原因（讀一讀這個連結吧，很棒）：保密不意味著安全；開源審查相比閉源來說更有可能找到弱點；正確的審查開源軟體很難或不可能在其中構建一個後門。如果你喜歡軼事、邏輯論證什麼的，想想Microsoft Internet Explorer 6，一個曾經的企業級閉源商業軟體，被廣泛的認為是有史以來最不安全的軟體。Rijndael演算法，作為AES背後的演算法，世界上的政府用來保護頂級祕密資訊，是一個開放標準。在任何案例中，關係型資料庫不是安全軟體。在IT世界裡，“安全”有點像美國的“支援我們的軍隊（support our troops）”，或紙牌中的王牌“想想孩子（think of the children）”，它可以否決其他所有，包括常識和證據。注意別上當。

  • 可靠性：Windows有一點比較有名，那就是它的不穩定性，雖然現在好了很多。（Windows 9x的以毫秒計的內部時間計數器達到32位無符號整型數的上限時，也就是  2 32毫秒以後或大概49.7天， 就會自動發生意外。我一直想試試來著。）在伺服器市場中Linux占主導地位，伺服器的穩定性是很重要的，Linux通常用年 計量時間。Internet Explorer一直以來與web標準不相容（現在還這麼幹），導致了網站碎片或功能不當；該領域的領頭羊是開源瀏覽器Chrome和Firefox。 Lotus Notes是一個片狀、經常發生問題、亂七八糟的玩意兒，也只有雷鳥還正常工作。我不止一次看到過MS SQL Server的事務日誌吹氣球一樣膨脹，然後把自個搞崩潰了，而PostgreSQL從來不會發生這樣的事。

  • 誠信：除非過去幾年裡你一直在石頭裡生活，否則你就知道愛德華斯諾登是誰。感謝他，我們確切的知道了誰不能相信：政府以及他們插手的大型組織。自從斯諾登公佈後，我們清楚的知道NSA的後門存在於大量的各種產品中，包括硬體和軟體，這些都是個人或組織用來保證資料安全的。
    唯一預防方法是開放程式碼審查。如果你使用有版權的閉源軟體，你沒有辦法知道隱藏在兜帽下面的它們真正在做什麼。感謝斯諾登，我們現在知道了放棄自己祕密的非常棒的方法。

  • 企業適用性：

    企業不用開源軟體的說法純粹是胡說八道。如果你在一家無視開源軟體的機構技術部門就職，那就盡情享受吧。 因為它離倒閉不遠了。

    • 在我寫本文的時候，全世界 500 臺超級計算機中， 有 485 用的是 Linux 。

    • 到 2014年7月份為止，Nginx 和 Apache, 這兩個開源伺服器，已經為超過70%的，問量最大的網站提供服務。  

    • 國際空間站(有史以來， 最昂貴的人造物)的電腦，已經在2013年，把 Windows 替換成 Linux。 為的是提高系統的穩定性和可靠性。 

    •  Skype 的後臺資料庫 (說起來好笑，這家公司現在已經被 Microsoft 收購了) 用的就是 PostgreSQL.

    • GCHQ 最近的報告指出，Ubuntu Linux 是常見的作業系統中，最安全的。

    • 大型強子對撞機是全世界最大的科學實驗裝置。支撐它的 IT 裝置,  Worldwide LHC Computing Grid, 是全世界最大的計算網格。 它能每年處理 30 PB 的資料，跨躍36個國家， 170 個計算中心。大部分用的都是 Linux 。

    • Hadoop， 許多想在大資料領域佔有一席之地的大型諮詢機構的新寵，也是開源的。 

    • 此外，還有： Red Hat Enterprise Linux; CEntOS (Community Enterprise OS); SUSE Linux Enterprise Server; Oracle Linux; IBM Enterprise Linux Server 等。

　　2.4. 可是 MS SQL Server 能利用多個 CPU 核心處理單條查詢!

　　當你的查詢語句執行時， 效能受限於 CPU 而不是 IO，那麼 MS SQL Server 就比較有優勢。但是在現實的資料分析中， 這種情況幾百年也難得見一回。當這種少見，又特殊的情況使得 CPU 變成效能瓶頸的時候。依靠資料庫系統是解決不了問題的。他們又不擅長數字密集運算。 

　　當伺服器需要同時處理多個任務的時候(伺服器基本上都這樣)  MS SQL Server 就沒有什麼優勢了。PostgreSQL 用的是多程式模式 -- 一個程式負責處理一個資料庫連結，也就是說，它也用了多個 CPU 核心。當然， 具體怎麼分配， 那是作業系統的事。 

　　我懷疑，MS SQL Server 的自定義聚集程式集(custom aggregate assemblies) 用的也是這種並行查詢機制。把聚集操作分配到多個執行緒中計算， 然後再將結果合併到一起，有點類似 MapReduce 的作風。 我還懷疑， 這就是為什麼MS SQL Server 的聚集語句不能使用 ORDER BY 子句的原因。所以， 恭喜，PostgreSQL 也是可以利用多個 CPU 核心的。只是你不能使用基本字串彙總(basic string roll-up)。

　　2.5. 我只會 MS SQL Server , 沒用過 PostgreSQL!

　　如果你寧願繼續使用那個臃腫, 笨拙, 不穩定的系統, 也不願意花點時間, 學一門稍微有些區別, 但是又比較簡單查的詢語言. 那就求神拜佛, 別在下次面試的時候遇到我.

　　2.6. 難道數以億計的 Microsoft 使用者都錯了?!

　　這是以前和我一起工作過的一名資深的資料分析師問的問題.  我的回答是 "這世上有 1.5 億 穆斯林教徒, 1.2 億 天主教徒. 不見得他們全都對". 所以, 就算一億個人都錯了, 也沒什麼奇怪的.  (這裡指的是 2.7 億個人都錯了.)

　　2.7. 真要這麼好， 怎麼可能會免費!

　　大家都喜歡這麼說。 真是替他們感到可惜， 因為他們無法說服別人不為錢做事。 就當他們不知道，這世上還有慈善機構， 自願者，和其他純粹就是做點貢獻的人存在好了。

　　這個觀點， 還建立在另外一個荒唐的假設上--開源軟體開發無利可圖。 大型企業開之所以開放原始碼，花錢請人開發和維護這些程式碼， 無非是能從中收益。設想下，如果你把程式碼開源給他人使用，別人就會給你軟體免費修改 Bug，新增新功能， 審查程式碼，測試， 甚至宣傳。如果你的產品做的足夠好， 用的人也就會越來越多，自然就會形成被業界廣為接受的標準。作為支援和釋出該軟體的供應商，你當然會在市場上佔據相當有利的地位。 就算你是個利己主義者， 開源也是個明智的選擇。比如說，我現在就自己花錢做網站，替 PostgreSQL 做宣傳。可能 Teradata 或 Oracle 也不錯。但是他們太貴了， 我用不起， 所以也就沒有給他們宣傳。 

　　2.8 但是你存在偏見！

　　好吧，我確實偏向於更好的資料庫。本文的目的是演示，用事實說話。偏見是有理由的，或者更確切的說，這根本不是偏見，而是偏好。在任何案例中，PostgreSQL都是免費軟體，所以我沒有經濟上的動機。我也沒有給它寫過什麼（除了我提交過一次bug修復），所以這也不是我個人的問題。並且和我關係近的人中也沒有一個參與PostgreSQL的開發與支援。我只是想用好的工具來把我的工作儘可能的幹好。為什麼會有如此多的疑問呢？？

　　2.9. 但是“PostgreSQL"是個愚蠢的名字！

　　這是可以證明的;它相當的尷尬。容易發錯音並且也經常被不正確的大寫書寫。人們選擇工業軟體時對”二逼名字“不格外顧慮倒不妨它是一份幹得漂亮的活。

　　儘管如此，對SQL伺服器來說，MS SQL Server是微軟選擇的所有可能名字中最沒勁的一個。無論哪個詞都有6到8個音節，這取決於你是否將"Microsoft"縮寫和你是否將它念做“sequel"或"ess queue ell"，一個產品的名字這也太長了點。雖然微軟有一個非常非常長名字的產品-或許這是它一直以來最偉大的成就來了

Microsoft® WinFX™ Software Development Kit for Microsoft® Pre-Release Windows Operating System Code-Named "Longhorn", Beta 1 Web Setup

　　我數了數有38個音節。哇靠。

　　2.10.但是SSMS比PGAdmin要好點！

　　它更流暢，必須的嘛。它也更好。它有程式碼自動完成功能—雖然我總是把這個功能關掉因為這個功能總是壞我事—而且每次它讓我走出段名或表名的困局的時候，至少有一件它做的好的事，例如自動-”糾正“一個普通的SQL關鍵詞”table"為一個微軟的奇怪的玩意“TABULATION_NONTRIVIAL_DISCOMBOBULATED_MACHIAVELLIAN_GANGLYON_ID”或別的什麼東西

　　事實上中止SQL並且看看GUI中的結果，PGAdmin很不錯。它只是不華麗而已。

　　還有另外一個東西：psql。它是PostgreSQL的命令列SQL介面。它是真的真的非常好。它有大量有用的目錄查詢功能。它可以聰明的顯示扁平化的資料。它還有tab鍵補齊功能，不像SSMS的程式碼補齊哪有，它確實有用，因為它是根據上下文感知的。打個比方，如果你鍵入    DROP SCHEMA t   並且按下tab鍵，它將會給你提供名稱以“t”開頭的schema（如果只有一個的話，就會自動補齊）。它也可以讓你在檔案系統中來回跳轉，也可以使用像內聯的vim那樣特別強大的文字編輯器。它自動保留了已執行命令的列表。它還提供了方便的有用的資料匯入匯出功能，包括"COPY TO PROGRAM"這樣的特性，利用管道和命令列提供了另一個級別的靈活性和資料控制能力。它靈活的使用螢幕空間。快速並且方便。你能夠通過一個SSH連結甚至是較慢的連結來使用它。

　　唯一嚴重的問題是，對於那些害怕命令列和敲鍵盤，還想成為資料分析師的人來說有點不合適。

　　2.11MS SQL Server可以直接從Excel匯入！

　　是的。那又怎樣？Excel可以輸出到CSV檔案（比較難得的是，微軟Excel的CSV匯出程式碼工作的挺好），PostgreSQL可以從CSV匯入。當然了，多了一步。在分析平臺中，能夠直接從Excel匯入是一個很重要的特性嗎？

　　2.12PostgreSQL比MS SQL Server要慢！

　　更確切的說應該是“MS SQL Server稍微更寬容，如果你不知道你在做什麼的話”。

　　對於某些操作，PostgreSQL確實要比MS SQL Server慢，最簡單的例子可能就是COUNT(*)了，在MS SQL Server中就是一瞬間的事（我是這麼想的），在PostgreSQL中就需要全表掃描（這是由於它們使用的是不同的併發模型）。PostgreSQL是一個慢開箱即用產品，因為它預設配置是使用少量系統資源，不過對於任一系統都已經被調整用於處理大量工作，所以天生的開箱即用效能不是一個值得爭論的事情。

　　我曾經看到過PostgreSQL因為慢而被批評，因為它在一個大型表中需要花很長時間來做一些巨大的、複雜的正規表示式操作。不過每個人都知道，正規表示式操作是一種非常昂貴的計算。在任一案例中，PostgreSQL的什麼被用來做比較呢？當然不是MS SQL Server了，因為它做不了正則操作。

　　PostgreSQL支援非常聰明的索引操作，比如範圍型索引和trigram索引，對於某一類操作來說比MS SQL Server有數量級的速度。不過只有你知道如何合適的使用這些特性才行。

　　你從最偉大的程式語言那裡得到了巨大的靈活性支援，並且聰明的資料型別允許面向基於PostgreSQL的解決方案，它勝過基於MS SQL Server的解決方案好幾個數量級。  檢視我更早的例子。

　　無論如何，關於速度的爭論不僅僅限於計算機的時間（執行時間）； 開發人員的時間也是一樣。  這就是為什麼像PHP或者Python這麼流行的原因， 儘管實際上C的執行速度比他們快。 雖然他們執行慢，但是他們開發更快速。  你要花一個小時編寫可維護的，優雅的SQL就被允許執行一個小時， 還是花費三天寫一個多bug的程式， 儘管它的工作時間只有45分鐘？

　　原文地址：http://www.pg-versus-ms.com/

相關文章