從Postgres95到PostgreSQL9.5：新版亮眼特性

Postgres95 介紹

“在 PostgreSQL 中使用 JSON 除了可以更好地處理移動互聯網數據外，對于傳統業務的由于業務形態可能隨時變化，而導致數據庫中“寬表”設計也有很大的幫助。” —— 蕭少聰

現在被稱為 PostgreSQL 的對象-關系型數據庫管理系統（有一段時間被稱為 Postgres95）是從伯克利寫的 POSTGRES 軟件包發展而來的。PostgreSQL 被譽為是 世界上可以獲得的最先進的開放源碼的數據庫系統 ， 支持幾乎所有 SQL 語法（包括子查詢，事務和用戶定義類型和函數）。

提供多版本并行控制， 并且支持多程開發語言，包括 Java、.Net、PHP、C、C++、node.js、perl、tcl 和 Python 等。

PostgreSQL 原于 Ingres，由 2014 年圖靈獎得主 Michael Stonebraker 主導開發。早在 1970 年代前期，Michael Stonebraker 就在 Edgar Codd 的關系數據庫論文啟發下，組織伯克利的師生，開始開發最早的兩個關系數據庫之一 Ingres（另一個是 IBM System R）。

Ingres 的基礎上后來發展出 Sybase 和 SQL Server 兩大主流數據庫。Ingres 在關系數據庫的查詢語言設計、查詢處理、存取方法、并發控制和查詢重寫等技術上都有重大貢獻。

1980 年代他又開發了 POSTGRES 項目，目的是在關系數據庫之上增加對更復雜的數據類型的支持，包括對象、地理數據、時間序列數據等。后來這個系統演變為開源的 PostgreSQL，Greenplum、Aster Data、Netezza 和 Stonebraker自己創辦的 Ilustra（后被Informix收購）等多個商業公司和開源的產品都是基于 PostgreSQL 開發的。

在 1994 年，Andrew Yu 和 Jolly Chen 兩位華人 向 POSTGRES 中增加了 SQL 語言的解釋器，命名為 Postgres95，后重新命名為 PostgreSQL。

PostgresSQL 版本發展歷史

1997 年，正式改名為 PostgreSQL 6.x，主要功能發展：unique indexes、Multi-column indexes、sequences、money data type (當前美國多家銀行使用)、GEQO (GEnetic Query Optimizer 基因查詢優化算法)、支持 JDBC、支持觸發器、支持存儲過程語言 PL/pgSQL、支持視圖、實現 MVCC 多版本控制、臨時表

我們可以年到在上世紀 90 年代，PostgreSQL 已經有十分完善的現代關系型數據庫功能。反觀 MySQL，到 2005 年才比較完善地提供以上功能，當然，也由于借助 LAMP 架構 MySQL 成為了開源數據庫占有率第一的數據庫。但在很多核心系統中，PostgreSQL 上世紀 90 年代到 20 世紀初企業級，甚至軍方核心系統中幾乎唯一使用的開源數據庫。比較重點的系統包括：NASA、美國海空軍、銀行等。

2000年，PostgreSQL 7.x，主要功能發展：對 Foreign keys 外鍵的支持、支持多表 JOIN、實現 WAL 日志系統（類似 redo log）、Outer JOINS、支持國際化語言、支持用戶 Schema 隔離。

這一版本主要對數據庫功能進行增強，主要表現在對多表處理，及容錯性方面。

2005年，PostgreSQL 8.x，主要功能發展：支持 Windows 平臺、Savepoints、表空間管理、基于任意時間點的恢復、2 階段提交、表分區、全文檢索、XML、窗口函數、遞歸查詢。

是的，您沒有看錯，2005年PostgreSQL才支持Windows平臺！！所以大家不要再問題我為什么PostgreSQL被我說得特備NB，但在中國沒有火。都想想自己10年前在用什么系統吧！

2010 年，PostgreSQL 9.x，這是一個讓中國用戶直正了解并使用 PostgreSQL 的開始。而實際上，同年 Uber、Instagram、Skype 等國外知名互聯網公司大量使用 PostgreSQL，特別是 Uber 通過 PostGIS 的地理信息功能，在后續幾年中橫掃了 O2O 打車市場。

PostgreSQL 9.0：支持64位Windows系統、異步流數據復制、Hot Standby（相當于Active DataGuard）。

PostgreSQL 9.1：支持同步數據復制、unlogged tabels、serializable snapshot isolation、FDW 外部表。

此版本后，PostgreSQL 開始得到中國多個行業用戶的關注，開始有應用于電信、保險、制造業等邊緣系統。

PostgreSQL 9.2：級聯數據復制、index-only scans、JSON 數據類型、空間分區 GiST 索引（SP-GiST）

PostgreSQL 9.3：數據校對 checksums、豐富 JSON 函數及操作符、并行 pg_dump 備份、物化視圖

PostgreSQL 9.4：JSONB 數據類型（高性能可索引）、可在線刷新物化視圖、支持Linux大頁操作、支持數據預熱

經過 9.x 版本多年的持續更新，我們可以看到，PostgreSQL 在企業功能上已經與商業數據庫沒有太大差距。同時 JSON 的加入，為很多傳統企業抹平了進入移動互聯網業務的道路，同時在很多特殊場景下無需再通過“寬表”進行數據處理。物化視圖、Linux 大頁面操作、數據預熱等功能為進一步實現 OLAP 功能奠定了基礎。

PostgresSQL 9.5 的亮眼特性

UPSERT

INSERT … ON CONFLICT, also known as “UPSERT”

如果你有用過 Oracle 的 Megre 功能，我相信不用我多說你都知道這是有多么的方便。以下是一些 DEMO：

可以看到由于x是主鍵因此數第 2 次 INSER INTO 無法插入成功。我們再看：

可以看到 INSERT 失敗后，進行 UPDATE。我們可以將例子寫得更加復雜一些：

以上 generate_eries(1,5) 在 PostgreSQL 的意思是生成 1 到 5 的序列，由于1、3、5 數據已經存在，因此無法寫入。而通過以下方式即可寫入數據

還想做更深入的操作？我們再看一個

以上例子我們可以方便地實現數據庫中對于錯誤中已存在數據的靈活處理，在復雜業務場景十分實用。

如：在進行 SQL 語句編寫時，我們經常會遇到大量的同時進行 Insert/Update 的語句 ，也就是說當存在記錄時，就更新 (Update)，不存在數據時，就插入 (Insert)。

Block-Range Indexes (BRIN)

通過以下圖例我們可以更好地理解這個新的 Index

此圖感謝李元佳

BRIN(Block Range Index)：保存數據塊的值的摘要信息，如存儲某一組塊里面所有記錄中的最大最小值，與 Exadata 的 Storage Index 相似。

通過 BRIN 我們可以讓 Index 大小指數級縮小，當然不恰當的使用也會影響性能，以下是一個 DEMO：

我們可以看到 BRIN 比 Btree 小很多。全表掃描之前，先從范圍索引過濾掉不滿足條件的數據塊，可大大提高全表掃描的性能。這一點對于按順序排列的表效果尤為明顯。以下的 DEMO 將說明 BRIN 對空間的節省情況：

但此時你會發現，系統 SELECT 性能相比 B-Tree 要低不少。

那 BRIN 有什么用處呢？請見以下另一個 DEMO 測試 Insert 性能：

我們可以明確看到，B-Tree 下 Insert 性能比 BRIN 慢了 1 倍，因此對于只要進行少量“等于”或“范圍查詢”操作，但要求高速數據寫入的場景這是十分適用的。如：按日期存放的日志表。

另外方面如同 Oracle Exadata 的 Storage Index，在一個類索引結構中存儲一定范圍的數據塊中某個列的最小和最大值。

當查詢語句中包含該列的過濾條件時，就會自動忽略那些肯定不包含符合條件的列值的數據塊，從而減少 IO 讀取量，提升查詢速度，當然是會比 btree 慢一些。

OLAP 數據分析操作支持 array_agg, GROUPING SETS, CUBE, and ROLLUP（略過，有用到這些功能的都懂）

這是 9.5 的新功能

Row-Level Security (RLS)

如果你是 Oracle 的粉絲，VPD 是不會陌生的。這一功能在“ PCI DSS – 支付卡行業（PCI）數據安全標準”是十分重要的一個實現手段，可以確保任何用戶都不會讀取到其它用戶的信息，所有用戶實現最核心的隔離。

在 PostgreSQL 9.5 中我們叫它做 RLS，通過以下操作我們就可以啟動 RLS：

接下來我們可以進行一些測試操作：

我們建立了兩個用戶，通過“SET SESSION AUTHORIZATION”操作，相當于是用不同的用戶進行登陸后再執行 INSERT 操作。

轉換為 postgres 超級用戶身份后，我們可以看到所有數據，但當前我使用 emp1 和 emp2 用戶身份操作時，我們將只能得到當前用戶寫入的數據。

WAL 日志壓縮

PostgreSQL 以 16MB 為單位保存 WAL 日志文件，由于日志文件會保存數據寫入前及寫入后的信息，因此在大量 UPDATE 及 DELETE 操作后 WAL 會持續增大。

這將大量占用用戶的歸檔空間，如果用戶需要通過網絡將WAL存放到遠端網絡存儲或磁帶機中，就會導致網絡帶寬大量備占用。

PostgreSQL 9.5 提供了 WAL 日志壓縮功能解決此問題，寫日志時候對數據塊進行壓縮。

往磁盤寫更少的數據，復制的傳輸的量也會少，可以更新的性能提高，但 CPU的消耗量會上升。

此圖感謝李元佳

pg_rewind功能

自 9.0 開始 PostgreSQL 提供流式數據復制功能 Streaming Replication，以實現兩個數據庫商的數據同步。

但 9.5 版本以前，一旦數據庫的 Master 節點出現硬件故障導致系統宕機，在主節點維修完畢想要重新加入到數據庫集群時，我們往往需要對此數據庫進行重新的全量數據初始化。

如果數據量在 100GB 以下恢復時間還是可以接受的，然而一旦數據少為大一點，到達幾百 GB 甚至 TB 級別，全量數據初始化將是一個災難！

由于 PostgreSQL 基于文件系統進行數據存儲，因此我們也可以借助 rsync，但由于 rsync 無法做到 Block 塊級別的數據差異復制，時間依然很難達到用戶要求。

因此 PostgreSQL 9.5 提供了 pg_rewind，這是一個同步 PostgreSQL 數據目錄的工具，其結果等同于用 rsync 同步 data 數據目錄。

pg_rewind 的優點是，它用 WAL 來確定更改的數據塊，不需要在集群里讀取所有文件，當數據庫很大時，這樣的特性會讓它運行起來更快。

JSONB 數據操作增強

PostgreSQL 自從 9.2 開始提供 JSON 的支持，對于哪些只通過 node.js 開發應用系統的同學來說，JSON 數據最佳的數據庫當然是 MongoDB。

但是對于傳統就使用關系型數據庫的企業用戶及應用軟件開發商而言，SQL 是根本，系統要求嚴謹的 ACID 關聯，開發人員也不習慣使用 SQL 以外語法。

特別是很多系統已經持續開發集成 3 年甚至 5 年，但由于當前需要對接互聯網，而再構建一個新的 MongoDB 進行 JSON 數據存儲，開發端就顯得特別麻煩。

而在 PostgreSQL 中，你可以：

我們可以看到，通過 json_data 表中 data 字段的 name 屬性，我們混合 SQL及 JSON 實現了一次數據庫內部的跨表 JOIN。

如果你擔心性能問題，你還可以在data->>’name’這個屬性上建立GIN索引，操作如下：

但此處要注意，我們使用“？”問號作為數據比對的操作符而不是“=”等號。

在最新的 PostgreSQL 9.5 中增加的多個進行 JSON 數據內部操作的特性：

通過這些特性我們通過 SQL 函數對 JSON 對象內部的屬性進行動態的添加及修改，整個操作就如同在 SQL 中操作 Redis 一樣，十分方便。

在 PostgreSQL 中使用 JSON 除了可以更好地處理移動互聯網數據外，對于傳統業務的由于業務形態可能隨時變化，而導致數據庫中“寬表”設計也有很大的幫助。

在 PostgreSQL 中可以將所有“寬表”的列定義成一個 JSONB 字段即可，未來因應數據操作需求，可以再進行不同屬性的索引處理。對于以下多種業務都十分實用：

• 政務系統中資產管理的“卡片”數據
• 醫療行業用戶病歷管理
• 教育行業中學生考卷管理
• 零售行業中產品資料及標識的管理

PostgresSQL 還可以做什么

Q & A

1、布道者為什么要用 pg？對比 db2 oracle mysql 有什么優勢？

布道者為什么用 PG：

• 功能要求（如：JSON、GIS、分析函數、窗口函數）。
• 性能要求（多表 JOIN、復雜事務并發）。
• 基于線程體系穩定性。
• 基于 BSD 協議開放，沒有 GPL 的開源限制。
• 已經在此行業看到大量機會。
• 如果以上你就同意，那就是癖好、情結。

MySQL：PG 除地市場差點，PG 會給你更多的功能體驗，在復雜系統中大大節省開發人員時間；

Oracle：成本優勢，不要告訴我你公司都是正版，在功能不差的前提下 PG 絕對與 Oracle 諧美（除了一點 RAC 在最佳環境下數據庫故障 0 中斷，PG 用 HA 所以最少要 20 秒）；

DB2：只要你給我一小機，PG 也可以

2、PG 可以把堆表按照一個索引來組織行？把 Proxy 的路由功能給內置了？

是可以的，關于 Proxy，建議這個問題者直接找我討論，有好多方案。

3、JSONB 跟 MongoDB 的 Bson 差不多？

差別很大，JSONB 基于嚴格遵循 ACID 的關系型數據庫上，可以直接與 SQL 進行互動

4、和 MongoDB 比起來，PostgreSQL 在 JSON 支持方面有什么異同點？能否取代前者，適合什么樣子的情景？

不能取代，MongoDB 操作是 find/save，還可以很好地橫向擴展，但無法做到嚴格的數據原子性 ACID。PostgreSQL 的 JSON 更適合傳統企業進行移動互聯網改造時使用，同時與 SQL 進行直接操作，減少開發量，快速上線新系統。

5、PostgreSQL 9.5 新特性有 OLAP 支持，而列存引擎對 OLAP 的性能提升是非常重要的，請問 PostgreSQL9.5 之后的版本會增加原生列存引擎的支持嗎？尤其是在 Greenplum 開源之后。

術業有專攻，PG 會做 OLTP 支持 OLAT 操作，GP 會作為數據倉庫方案

6、PG 的 GIS 跟 MongoDB 的 Geo 是否類似？有啥區別？

地球是橢圓型的，而且還是不規則的橢圓型，在 PostGIS 中可以處理，PG 可以處理更為復雜的 GIS 版本，具體請看 PostGIS 的手冊。還有 PG 支持高度，所以可以做 3D。

7、都說 PG 的功能比 Mysql 強大，能否簡單說說強大的地方？

這個太多了，可見 http://yq.aliyun.com/articles/2727，這些很多 MySQL 都做不到，PG 是個功能型的數據庫，但我不認為誰更強大。如同，我要的是上下班的自行車，你給我個特斯拉，我還要花錢買停車位。但如果你有復雜查詢，多表 J OIN，那 PG 是你的救星。

8、PostgreSQL 在國內有哪些大型案例？

去哪兒網、平安科技、國家電網都在用并在 2015 年大會分享過，很多用 PG 都公司都沒有對外公布，希望大家一同 show 出來我們在計劃做“ PostgreSQL 黃頁”。同時很多國產數據庫都基于 PG，真要找大型案例，可能還會有在軍、國字頭

9、pg_shard 算是 postgreSQL 最好的水平擴展方案嗎？

這個可不一定要注意 pg_shard 不保證 ACID，水平擴展還有 Greenplum 及 Postgres-XC/XL/X2

10、如果要依賴 postgresql 的 transaction id 增長做增量改變的查詢，靠譜嗎？比如查詢某個 trabsaction id 之后修改過的記錄。

這個需要小心，transaction id 是有極限的，通過 vacuum 會回收，可以線下交流一下你想做什么，或許有其它方案。

11、因為服務器時間很難保證誤差，所以沒有基于時間去查詢增量數據，而是基于 pg 的 age 函數去判斷 transaction id 的先后，想問下這樣的方案可靠不，有沒有別人這么用過。

同上，線下討論，這個話題就大了，我猜你是想做分布式鎖管理，而且還想去中心化。

12、以前在 128G 內存的機器上使用過 PG，試驗過各種配置參數，但是似乎 PG 都很難充分利用內存，請問這是配置姿勢不對，還是 PG 固有的問題？

我們剛剛完成了極限測試：CPU、內存、IO 應該都用到盡頭了，當前性能比 O要高一些，正在請 IO 的高手來優化。

13、PG 使用的比較多的是游戲和數據分析，PG 在這兩個應用場景下有啥優勢嗎？

首先，并發事務處理方面 PG 天生是有性能優勢的，游戲中核心依然是交易系統所以不少都會用 PG。再者在數據分析方面，由于支持“窗口函數”，所以更能快速解決問題，還有就是多表 JOIN 性能也是 PG 會比較高。

14、請問 pg 的運維成本相比 mysql 或 oracle 如何？

如果你的系統不大，PG 比 Oracle 高（因為人少）、Oracle 比 MySQL高（因為工資高）。如果你的系統很龐大，都一樣！但是 Oracle 收費！

15、PostgreSQL 和 OceanBase 在阿里內部的關系？或者說相比 OceanBase，PostgreSQL 的優劣勢是什么？

首先，沒有直接關系。PostgreSQL 是在阿里云 RDS 中的一個數據庫引擎，主要提供云數據庫服務。兩者沒有太大的可比性，OB 有很多創新已經不是傳統關系型數據庫的模型了。