851 技術社區[雲棲]

Oracle業務適合用PostgreSQL去O的一些評判標準

標簽

PostgreSQL , Oracle

背景

Oracle業務適合用PG去O的一些評判標準：

功能指標

如果評估出來的業務中具備這些特性，非常適合使用PostgreSQL。

1、業務使用的數據類型中出現

IP地址、GIS、數組、範圍、全文檢索、大對象、字節流、比特流、枚舉、幾何、自定義複合、UUID、XML、JSON、貨幣、字符串、數值、時間、加密數據類型

2、業務需求中出現

全文檢索、模煳查詢、相似查詢

3、業務使用的SQL中出現

connect by、多維分析(grouping, grouping sets, rollup, cube)、多表JOIN、窗口查詢(over partition by ())、聚合函數

4、業務使用的SQL中出現如下HINT

parallel   
  
hash hint  
  
left join  
  
right join  
  
outer join  
  
merge join  
  
hash agg  
  
group agg  
  
merge sort  
  
skip scan

5、業務使用了存儲過程

6、表的數據量

單表過億

7、業務使用了dblink，外部表功能

8、業務使用了bitmap\btree索引

PostgreSQL 內置多種索引接口(hash, btree, gin, gist, sp-gist, brin, bloom)

9、業務中使用了約束

primary key, unique key, check, not null, default value

10、業務中使用了全局序列、局部序列

sequence

11、業務使用了翻轉索引

12、業務使用了分區表

13、業務使用了觸發器、規則功能

14、業務使用了混合負載

小事務和分析型事務並存。

PostgreSQL通過多核並行、JIT、算子複用等技術，加速分析事務。

15、業務使用了upsert（不存在則插入，存在則更新）

16、業務大量使用了GIS地理位置數據

17、業務有大量數據透視需求（BI分析）

18、業務大量使用了ORACLE的內置函數，（分析函數、聚合函數、窗口函數、數據處理函數等）

19、業務有任意列，任意條件篩選需求

20、業務有倒排索引需求

21、業務有空間數據檢索需求

22、業務使用了物化視圖

23、業務有多master需求

24、業務有流式計算需求

25、業務有圖式搜索需求

26、業務有讀寫分離需求

27、業務有並行查詢的需求

28、業務有加密數據類型需求

29、業務有數據采樣需求

30、業務有鏈路加密需求

性能指標

單機(32 CORE, SSD, 512GB內存)

1、TPC-H性能

SF=100，100GB 裸數據。

2017-07-13 20:04:29 [1499947469] : running queries defined in TPC-H benchmark
2017-07-13 20:04:29 [1499947469] :   running query 1
2017-07-13 20:04:29 [1499947469] : run explain
2017-07-13 20:04:29 [1499947469] : run the query on background
2017-07-13 20:04:47 [1499947487] :     query 1 finished OK (17 seconds)
2017-07-13 20:04:47 [1499947487] :   running query 2
2017-07-13 20:04:47 [1499947487] : run explain
2017-07-13 20:04:47 [1499947487] : run the query on background
2017-07-13 20:08:13 [1499947693] :     query 2 finished OK (206 seconds)
2017-07-13 20:08:13 [1499947693] :   running query 3
2017-07-13 20:08:13 [1499947693] : run explain
2017-07-13 20:08:14 [1499947694] : run the query on background
2017-07-13 20:08:55 [1499947735] :     query 3 finished OK (41 seconds)
2017-07-13 20:08:55 [1499947735] :   running query 4
2017-07-13 20:08:55 [1499947735] : run explain
2017-07-13 20:08:55 [1499947735] : run the query on background
2017-07-13 20:09:02 [1499947742] :     query 4 finished OK (6 seconds)
2017-07-13 20:09:02 [1499947742] :   running query 5
2017-07-13 20:09:02 [1499947742] : run explain
2017-07-13 20:09:02 [1499947742] : run the query on background
2017-07-13 20:09:16 [1499947756] :     query 5 finished OK (14 seconds)
2017-07-13 20:09:16 [1499947756] :   running query 6
2017-07-13 20:09:16 [1499947756] : run explain
2017-07-13 20:09:16 [1499947756] : run the query on background
2017-07-13 20:09:21 [1499947761] :     query 6 finished OK (4 seconds)
2017-07-13 20:09:21 [1499947761] :   running query 7
2017-07-13 20:09:21 [1499947761] : run explain
2017-07-13 20:09:21 [1499947761] : run the query on background
2017-07-13 20:10:06 [1499947806] :     query 7 finished OK (35 seconds)
2017-07-13 20:10:06 [1499947806] :   running query 8
2017-07-13 20:10:06 [1499947806] : run explain
2017-07-13 20:10:06 [1499947806] : run the query on background
2017-07-13 20:10:38 [1499947838] :     query 8 finished OK (31 seconds)
2017-07-13 20:10:38 [1499947838] :   running query 9
2017-07-13 20:10:38 [1499947838] : run explain
2017-07-13 20:10:38 [1499947838] : run the query on background
2017-07-13 20:11:32 [1499947892] :     query 9 finished OK (54 seconds)
2017-07-13 20:11:32 [1499947892] :   running query 10
2017-07-13 20:11:32 [1499947892] : run explain
2017-07-13 20:11:32 [1499947892] : run the query on background
2017-07-13 20:11:49 [1499947909] :     query 10 finished OK (16 seconds)
2017-07-13 20:11:49 [1499947909] :   running query 11
2017-07-13 20:11:49 [1499947909] : run explain
2017-07-13 20:11:49 [1499947909] : run the query on background
2017-07-13 20:11:56 [1499947916] :     query 11 finished OK (7 seconds)
2017-07-13 20:11:56 [1499947916] :   running query 12
2017-07-13 20:11:56 [1499947916] : run explain
2017-07-13 20:11:56 [1499947916] : run the query on background
2017-07-13 20:13:37 [1499948017] :     query 12 finished OK (100 seconds)
2017-07-13 20:13:37 [1499948017] :   running query 13
2017-07-13 20:13:37 [1499948017] : run explain
2017-07-13 20:13:37 [1499948017] : run the query on background
2017-07-13 20:17:11 [1499948231] :     query 13 finished OK (213 seconds)
2017-07-13 20:17:11 [1499948231] :   running query 14
2017-07-13 20:17:11 [1499948231] : run explain
2017-07-13 20:17:11 [1499948231] : run the query on background
2017-07-13 20:17:15 [1499948235] :     query 14 finished OK (4 seconds)
2017-07-13 20:17:15 [1499948235] :   running query 15
2017-07-13 20:17:15 [1499948235] : run explain
2017-07-13 20:17:15 [1499948235] : run the query on background
2017-07-13 20:17:40 [1499948260] :     query 15 finished OK (25 seconds)
2017-07-13 20:17:40 [1499948260] :   running query 16
2017-07-13 20:17:40 [1499948260] : run explain
2017-07-13 20:17:40 [1499948260] : run the query on background
2017-07-13 20:18:41 [1499948321] :     query 16 finished OK (60 seconds)
2017-07-13 20:18:41 [1499948321] :   running query 17
2017-07-13 20:18:41 [1499948321] : run explain
2017-07-13 20:18:41 [1499948321] : run the query on background
2017-07-13 20:27:55 [1499948875] :     query 17 finished OK (552 seconds)
2017-07-13 20:27:55 [1499948875] :   running query 18
2017-07-13 20:27:55 [1499948875] : run explain
2017-07-13 20:27:55 [1499948875] : run the query on background
2017-07-13 20:49:57 [1499950197] :     query 18 finished OK (1317 seconds)
2017-07-13 20:49:57 [1499950197] :   running query 19
2017-07-13 20:49:57 [1499950197] : run explain
2017-07-13 20:49:57 [1499950197] : run the query on background
2017-07-13 20:50:09 [1499950209] :     query 19 finished OK (11 seconds)
2017-07-13 20:50:09 [1499950209] :   running query 20
2017-07-13 20:50:09 [1499950209] : run explain
2017-07-13 20:50:09 [1499950209] : run the query on background
2017-07-13 20:56:43 [1499950603] :     query 20 finished OK (393 seconds)
2017-07-13 20:56:43 [1499950603] :   running query 21
2017-07-13 20:56:43 [1499950603] : run explain
2017-07-13 20:56:43 [1499950603] : run the query on background
2017-07-13 20:58:19 [1499950699] :     query 21 finished OK (95 seconds)
2017-07-13 20:58:19 [1499950699] :   running query 22
2017-07-13 20:58:19 [1499950699] : run explain
2017-07-13 20:58:19 [1499950699] : run the query on background
2017-07-13 21:00:43 [1499950843] :     query 22 finished OK (143 seconds)
2017-07-13 21:00:43 [1499950843] : finished TPC-H benchmark

2、TPC-C性能

3000倉庫、256客戶端。84.5萬 tpmC。

《數據庫界的華山論劍 tpc.org》

3、GIS(KNN檢索)

100億位置信息，近鄰查詢。

tps: 7.4萬/s

rt: 0.848毫秒

《PostgreSQL 百億地理位置數據近鄰查詢性能》

4、模煳查詢

前後模煳(like '%????%')

1億數據量，前後模煳，0.2毫秒。

《PostgreSQL 模煳查詢最佳實踐》

5、全文檢索

10億隨機值，返回2萬條匹配記錄，26毫秒。

《PostgreSQL 全文檢索加速快到沒有朋友 - RUM索引接口(潘多拉魔盒)》

6、多表JOIN

2張1億記錄，10張1000萬記錄，1張1000記錄的表進行JOIN，聚合查詢。

23毫秒。

c 1000萬
d 1000
e 1億

postgres=# explain (analyze,verbose,timing,costs,buffers) 
select count(t1.*) from 
e t1 join e t2 on (t1.id=t2.id and t1.id<=1000) 
join c t3 on (t1.id=t3.id) 
join c t4 on (t1.id=t4.id) 
join c t5 on (t1.id=t5.id) 
join c t6 on (t1.id=t6.id) 
join c t7 on (t1.id=t7.id) 
join c t8 on (t1.id=t8.id) 
join c t9 on (t1.id=t9.id) 
join c t10 on (t1.id=t10.id) 
join c t11 on (t1.id=t11.id) 
join c t12 on (t1.id=t12.id) 
join d t13 on (t1.id=t13.id) ;


 Aggregate  (cost=3234.08..3234.09 rows=1 width=8) (actual time=23.665..23.665 rows=1 loops=1)
   Output: count(t1.*)
   Buffers: shared hit=48059
   ->  Nested Loop  (cost=5.76..3234.08 rows=1 width=28) (actual time=0.083..23.553 rows=1000 loops=1)
         Output: t1.*
         Join Filter: (t1.id = t13.id)
         Buffers: shared hit=48059

............

 Planning time: 7.943 ms
 Execution time: 23.782 ms
(116 rows)

7、單表聚合性能

單表8億記錄，avg,count,sum,min,max維度聚合查詢。

32個並行度

5.3秒

postgres=# select count(*),sum(id),avg(id),min(id),max(id) from e;  
   count   |        sum        |          avg          | min |    max      
-----------+-------------------+-----------------------+-----+-----------  
 800000000 | 40000000400000000 | 50000000.500000000000 |   1 | 100000000  
(1 row)  
  
Time: 5316.490 ms (00:05.316)

8、數據導入速度

並行寫入，500萬條記錄/s 或每秒1.8GB/s。

《PostgreSQL 如何瀟灑的處理每天上百TB的數據增量》

小結

簡單來說，PostgreSQL是Oracle的最佳替代產品，而且還有額外驚喜，參考應用案例一文。

參考資料

《PostgreSQL 應用案例 - 目錄》

《數據庫選型之 - 大象十八摸 - 致架構師、開發者》

《數據庫選型思考》

最後更新：2017-07-14 09:04:08