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

德哥發表於2017-07-13
OracleSQL

標籤

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、業務使用了bitmaptree索引

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 應用案例 – 目錄》

《資料庫選型之 – 大象十八摸 – 致 架構師、開發者》

《資料庫選型思考》


相關文章