資料庫選型思索 - 緻 架構師、開發者

postgresql , 資料庫選型

資料庫選型是一件很大的事情，也是一件很頭疼的事情。

很多企業并沒有資料庫的選型标準，或者并不了解業務需要什麼樣的資料庫。

很多企業的資料庫是開發說了算，熟悉什麼就用什麼，很多選型失誤，導緻後期非常尴尬的局面。

那麼資料庫選型要注意什麼呢？

列舉一些例子，取自如下文檔

<a href="https://github.com/digoal/blog/blob/master/201701/20170125_01_pdf_002.pdf">《資料庫選型的關鍵名額》</a>

1. 遞歸查詢, connect by, 樹形查詢

postgresql 通過(with 或 tablefunc支援)支援例子

<a href="https://yq.aliyun.com/articles/240">https://yq.aliyun.com/articles/240</a>

<a href="http://www.postgresql.org/docs/9.5/static/tablefunc.html">http://www.postgresql.org/docs/9.5/static/tablefunc.html</a>

2. 視窗查詢, window over

postgresql 支援例子

<a href="http://blog.163.com/digoal@126/blog/static/16387704020137154137930">http://blog.163.com/digoal@126/blog/static/16387704020137154137930</a>

<a href="http://blog.163.com/digoal@126/blog/static/16387704020121024102312302/">http://blog.163.com/digoal@126/blog/static/16387704020121024102312302/</a>

<a href="http://blog.163.com/digoal@126/blog/static/16387704020124239390354/">http://blog.163.com/digoal@126/blog/static/16387704020124239390354/</a>

<a href="http://www.postgresql.org/docs/9.5/static/functions-window.html">http://www.postgresql.org/docs/9.5/static/functions-window.html</a>

3. rollup, grouping sets, cube

<a href="http://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#queries-grouping-sets">http://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#queries-grouping-sets</a>

<a href="http://blog.163.com/digoal@126/blog/static/16387704020154269591874/">http://blog.163.com/digoal@126/blog/static/16387704020154269591874/</a>

4. 進階聚合(json,數組,相關性,标準差(采樣,全局),截距,斜率,方差(采樣,全局),mode,percentile_cont,distc,rank,dense_rank,percent_rank,cume_dist,grouping)

<a href="http://www.postgresql.org/docs/9.5/static/functions-aggregate.html">http://www.postgresql.org/docs/9.5/static/functions-aggregate.html</a>

<a href="http://blog.163.com/digoal@126/blog/static/1638770402015224124337/">http://blog.163.com/digoal@126/blog/static/1638770402015224124337/</a>

<a href="http://blog.163.com/digoal@126/blog/static/1638770402015379286873/">http://blog.163.com/digoal@126/blog/static/1638770402015379286873/</a>

<a href="http://blog.163.com/digoal@126/blog/static/16387704020153713222764">http://blog.163.com/digoal@126/blog/static/16387704020153713222764</a>

4. hash join, merge join, nestloop join

<a href="http://www.postgresql.org/docs/9.5/static/xoper-optimization.html">http://www.postgresql.org/docs/9.5/static/xoper-optimization.html</a>

<a href="http://www.postgresql.org/docs/9.5/static/planner-optimizer.html">http://www.postgresql.org/docs/9.5/static/planner-optimizer.html</a>

5. 哈希聚合

<a href="http://www.postgresql.org/docs/9.5/static/runtime-config-query.html#runtime-config-query-enable">http://www.postgresql.org/docs/9.5/static/runtime-config-query.html#runtime-config-query-enable</a>

6. 事務間共享事務快照

<a href="http://www.postgresql.org/docs/9.5/static/functions-admin.html#functions-snapshot-synchronization">http://www.postgresql.org/docs/9.5/static/functions-admin.html#functions-snapshot-synchronization</a>

<a href="http://www.postgresql.org/docs/9.5/static/sql-set-transaction.html">http://www.postgresql.org/docs/9.5/static/sql-set-transaction.html</a>

<a href="http://www.postgresql.org/docs/9.5/static/app-pgdump.html">http://www.postgresql.org/docs/9.5/static/app-pgdump.html</a>

<a href="http://blog.163.com/digoal@126/blog/static/163877040201326829943/">http://blog.163.com/digoal@126/blog/static/163877040201326829943/</a>

<a href="http://blog.163.com/digoal@126/blog/static/163877040201241134721101/">http://blog.163.com/digoal@126/blog/static/163877040201241134721101/</a>

7. 展開式索引(支援多列任意組合查詢)

<a href="https://github.com/digoal/blog/blob/master/201702/20170205_01.md">《寶劍贈英雄 - 任意組合字段等效查詢, 探探postgresql多列展開式b樹》</a>

1. foreign key

<a href="http://www.postgresql.org/docs/9.5/static/ddl-constraints.html">http://www.postgresql.org/docs/9.5/static/ddl-constraints.html</a>

2. for no key update, for key share 粒度鎖

<a href="http://www.postgresql.org/docs/9.5/static/explicit-locking.html#locking-rows">http://www.postgresql.org/docs/9.5/static/explicit-locking.html#locking-rows</a>

<a href="http://blog.163.com/digoal@126/blog/static/16387704020130249109133/">http://blog.163.com/digoal@126/blog/static/16387704020130249109133/</a>

<a href="http://blog.163.com/digoal@126/blog/static/16387704020130305109687/">http://blog.163.com/digoal@126/blog/static/16387704020130305109687/</a>

3. check 限制

4. exclusion 限制

1. 表空間

2. alter 列值轉表達式

(alter table alter column c1 type newtype using (expression(...)) )

<a href="http://www.postgresql.org/docs/9.5/static/sql-altertable.html">http://www.postgresql.org/docs/9.5/static/sql-altertable.html</a>

<a href="https://yq.aliyun.com/articles/30470">https://yq.aliyun.com/articles/30470</a>

3. alter table 需要重組表的操作

postgresql少量操作需要重組

vacuum full, cluster, 修改字段資料類型, (修改長度不需要重組表)

4. 分區表

<a href="https://github.com/digoal/blog/blob/master/201610/20161024_01.md">《postgresql 9.5+ 高效分區表實作 - pg_pathman》</a>

<a href="https://github.com/digoal/blog/blob/master/201612/20161215_01.md">《postgresql 10.0 内置分區表》</a>

5. 物化視圖

<a href="http://www.postgresql.org/docs/9.5/static/sql-creatematerializedview.html">http://www.postgresql.org/docs/9.5/static/sql-creatematerializedview.html</a>

6. 物化視圖增量重新整理

<a href="http://www.postgresql.org/docs/9.5/static/sql-refreshmaterializedview.html">http://www.postgresql.org/docs/9.5/static/sql-refreshmaterializedview.html</a>

7. 表繼承關系

<a href="http://www.postgresql.org/docs/9.5/static/tutorial-inheritance.html">http://www.postgresql.org/docs/9.5/static/tutorial-inheritance.html</a>

8. 使用 like 建結構類似的表

<a href="http://www.postgresql.org/docs/9.5/static/sql-createtable.html">http://www.postgresql.org/docs/9.5/static/sql-createtable.html</a>

1. 用戶端開發語言支援

c, java, python, ...

2. 函數

傳回 void, 單行，srf，事件觸發器(mysql 不支援)，觸發器

例子

<a href="http://blog.163.com/digoal@126/blog/static/16387704020132131361949/">http://blog.163.com/digoal@126/blog/static/16387704020132131361949/</a>

<a href="http://www.postgresql.org/docs/9.5/static/event-triggers.html">http://www.postgresql.org/docs/9.5/static/event-triggers.html</a>

3. 2pc

4. 服務端綁定變量

<a href="http://www.postgresql.org/docs/9.5/static/sql-prepare.html">http://www.postgresql.org/docs/9.5/static/sql-prepare.html</a>

5. savepoint

6. 異步消息

<a href="http://www.postgresql.org/docs/9.5/static/sql-notify.html">http://www.postgresql.org/docs/9.5/static/sql-notify.html</a>

<a href="http://www.postgresql.org/docs/9.5/static/sql-listen.html">http://www.postgresql.org/docs/9.5/static/sql-listen.html</a>

7. 遊标

數組for循環，query for循環，遊标for循環

postgresql 全面支援例子

<a href="http://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html">http://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html</a>

<a href="http://www.postgresql.org/docs/9.5/static/plpgsql-cursors.html">http://www.postgresql.org/docs/9.5/static/plpgsql-cursors.html</a>

(mysql 暫不支援數組)

1. 資料類型

1.1 postgresql

高精度numeric, 浮點, 自增序列，貨币，位元組流，時間，日期，時間戳，布爾，枚舉，平面幾何，立體幾何，多元幾何，地球，postgis，網絡，比特流，全文檢索，uuid，xml，json，數組，複合類型，域類型，範圍，樹類型，化學類型，基因序列，fdw, 大對象, 圖像

1.2 mysql

數字，時間，字元串，簡單的gis，json

2. 支援索引的資料類型

2.1 postgresql

高精度numeric, 浮點, 自增序列，貨币，位元組流，時間，日期，時間戳，布爾，枚舉，平面幾何，立體幾何，多元幾何，地球，postgis，網絡，比特流，全文檢索，uuid，xml，json，數組，複合類型，域類型，範圍，樹類型，化學，基因序列

2.2 mysql

數字，字元串，比特流，時間，全文檢索，gis

1. 索引方法

postgresql 支援 btree, hash, gist, sp-gist, gin, brin , bloom , rum 索引

mysql 支援 btree, gis類型索引

2. 規則表達式、前後模糊查詢，支援索引檢索

postgresql 支援

3. 數組支援索引

4. 全文檢索支援索引

5. 索引擴充功能

5.1 postgresql 支援 表達式索引，部分索引，聯合索引

5.2 postgresql 支援圖像相似度、文本相似度搜尋

5.3 自定義索引通路方法(bloom, rum, ......)

postgresql支援

1. 多引擎支援

内置heap, 通過插件實作記憶體表, 列存儲, 壓縮存儲, 流式存儲, 非關系存儲等。

myisam, innodb, ...

2. 外部表

postgresql支援任意外部資料源, (例如jdbc, file, odbc, oracle, mysql, db2, redis, mongo, es, hadoop......)

<a href="https://wiki.postgresql.org/wiki/fdw">https://wiki.postgresql.org/wiki/fdw</a>

3. dblink

4. 記憶體表

postgresql 通過外部表支援，或者通過插件支援

5. es(彈性搜尋引擎) 引擎

1. 資料加密

postgresql 支援加密資料類型，可選gpg加密算法

2. 認證方法

postgresql 支援 密碼、ldap、ad、gssapi、sspi、ident、peer、radius、pam、簽名認證

3. 資料傳輸加密

4. 行安全政策

5. 資料庫内部支援libselinux接口, (美國國家安全局制定的安全加強标準)

<a href="http://www.postgresql.org/docs/9.5/static/runtime-config-query.html">http://www.postgresql.org/docs/9.5/static/runtime-config-query.html</a>

1. gpu 并行計算支援

2. 遺傳優化器算法

postgresql 支援cbo、cro、遺傳算法

3. hint plan

4. cpu 并行計算

postgresql 支援 (線性性能提升)

5. 自定義成本因子

1. 是否支援采樣查詢

2. 是否支援擴充采樣算法

3. 自定義資料類型

4. 自定義索引方法

5. 字元集自動轉換, c擴充接口

6. 自定義聚合

7. 自定義視窗

1. 類型擴充，操作符擴充，函數擴充，索引方法擴充，索引擴充，

2. c觸發器函數， c事件觸發器函數

3. 函數語言擴充

postgresql 支援擴充函數支援接口, erlang, ....

4. 機器學習庫

5. 流式計算

<a href="https://github.com/digoal/blog/blob/master/201701/20170125_01.md">《資料庫界的華山論劍 tpc.org》</a>

<a href="https://github.com/digoal/blog/blob/master/201609/20160911_01.md">《facebook linkbench 測試postgresql社交關系圖譜場景性能》</a>

<a href="https://github.com/digoal/blog/blob/master/201609/20160927_01.md">《postgresql 主機性能測試方法 - 單機多執行個體》</a>

<a href="https://github.com/digoal/blog/blob/master/201609/20160927_02.md">《postgresql 主機性能測試方法 - 單機單執行個體》</a>

<a href="https://github.com/digoal/blog/blob/master/201611/20161129_01.md">《postgresql 源碼性能診斷(perf profiling)指南》</a>

<a href="https://github.com/digoal/blog/blob/master/201512/20151215_01.md">《"物聯網"流式處理應用 - 用postgresql實時處理(萬億每天)》</a>

<a href="https://github.com/digoal/blog/blob/master/201601/20160119_01.md">《postgresql 百億地理位置資料 近鄰查詢性能》</a>

<a href="https://github.com/digoal/blog/blob/master/201603/20160302_01.md">《postgresql 百億資料 秒級響應 正則及模糊查詢》</a>

<a href="https://github.com/digoal/blog/blob/master/201603/20160307_01.md">《postgresql 1000億資料量 正則比對 速度與激情》</a>

<a href="https://github.com/digoal/blog/blob/master/201612/20161225_01.md">《恭迎萬億級營銷(圈人)潇灑的邁入毫秒時代 - 萬億user_tags級實時推薦系統資料庫設計》</a>

<a href="https://github.com/digoal/blog/blob/master/201612/20161231_01.md">《從難纏的模糊查詢聊開 - postgresql獨門絕招之一 gin , gist , sp-gist , rum 索引原理與技術背景》</a>

<a href="https://github.com/digoal/blog/blob/master/201612/20161216_01.md">《分析加速引擎黑科技 - llvm、列存、多核并行、算子複用 大聯姻 - 一起來開啟postgresql的百寶箱》</a>

1. 函數(過程)語言

postgresql支援幾乎所有的地球程式設計語言，你可以用他們在資料庫中編寫udf

plpgsql, sql, c, c++, java, javascript, r, python, perl, php, tcl, ruby, lua, ...

1. postgresql完全支援以下下推功能

支援聚合算子下推

支援where條件下推

支援join下推

支援sort下推

支援select 子句下推

支援跨資料源join

postgresql

資源管控能力強。

單執行個體可以充分發揮hpc的性能，有多少資源就能使用多少資源。

pirt，任意時間點恢複；fpw；

實體、邏輯級流式複制；金融級多副本；

社群狀态

單一開源分支，社群力量較集中。全球有1000名以上的核心研發人員。

社群研發由核心組員和committer組成，持續5年以上對社群版本有貢獻核心研發人員超過50位。

社群核心人員分别來自資料庫廠商，資料庫支援和服務公司，資料庫最終使用者的公司，形成了一個非常好的力量均衡。

社群活躍度

非常活躍(see git.postgresql.org, github.com/postgres)