最受開發者歡迎的HTAP資料庫PostgreSQL10特性

德哥發表於2017-11-12

背景

作為一款HTAP資料庫（同時支援 “OLTP高併發線上事務處理” 與 “OLAP線上分析” 業務場景），PostgreSQL 10的哪些特性是開發人員、DBA、架構師都喜歡的呢？

多核並行增強

9.6的版本支援如下並行：

Sequential scans  
Aggregates  
Hash and loop joins

10 並行增強：

1、通過 max_parallel_workers 控制最大並行度，  
  
可以避免分析型SQL把資源全部耗光。從而支援HTAP混合型業務。  
  
2、新增的並行索引掃描  
  
Regular index scans (btree)  
  
Index Only scans (btree)  
  
Bitmap Heap Scan  
  
Index still scanned serially  
  
3、並行的合併JOIN  
  
Merge joins  
  
4、多表掃描(append scan)支援並行排序  
  
Merge Sort

fdw 聚合下推

FDW聚合下推，使得PostgreSQL 10可以作為一個任意資料庫的sharding管理節點，用於分發路由SQL，支援where, sort, join, select-clause, agg等下推。

邏輯訂閱

邏輯訂閱有很多用途:

1、遷移、大版本升級

2、數倉聚合

3、拆庫

4、多個業務系統共享、分發資料

5、多master架構

分割槽

內建的分割槽功能，不需要依賴外掛。

《PostgreSQL 10 內建分割槽 vs pg_pathman perf profiling》

《PostgreSQL 10.0 preview 功能增強 – 內建分割槽表》

libpq支援failover和load balance

libpq支援配置多個連線地址，同時支援failover和負載均衡。

《PostgreSQL 10.0 preview 功能增強 – libpq支援多主機連線(failover,LB)讓資料庫HA和應用配合更緊密》

事務狀態可查詢

使用者可以根據事務號，查詢事務的狀態。實現業務層的控制，包括閃回等功能。

《PostgreSQL flashback(閃回) 功能實現與介紹》

《PostgreSQL 10.0 preview 功能增強 – 更強可靠性, 過去式事務狀態可查（杜絕unknown事務）》

任意多副本同步 – 金融級可靠性

使用者可以根據事務的重要性，選擇需要落幾個副本(WAL)，從而實現多副本持久化。

《PG多節點(quorum based), 0丟失 HA(failover,switchover)方案》

自定義多列混合統計資訊

《PostgreSQL 10 黑科技 – 自定義統計資訊》

統計資訊可以用於評估SQL的執行成本、檢視資料分佈、估算唯一值、估算記錄數、檢視資料儲存的相關性等。預設情況下，資料庫的柱狀圖是單列的，當輸入多列條件時，使用多列統計資訊，可以估算更加精確的成本。實現更精準的執行計劃。

hash index增強(持久化、效能)

PostgreSQL 10 hash index支援持久化（寫WAL），同時在vacuum, 查詢效能等方面都有巨大的優化。如果是等值查詢，或者超長列，使用hash index可以獲得比btree更好的效能。

重新命名列舉值

是的，列舉值可以rename了。

JSON,JSONB全文檢索

JSON, JSONB型別，支援全文檢索。

The functions ts_headline() and to_tsvector() can now be used on these data types.

file_fdw支援命令列返回值作為源

file_fdw是一個檔案外部表介面，用於將檔案作為外部表資料來源來使用，現在，file_fdw支援command的輸出結果作為外部表的輸入源了。

CREATE FOREIGN TABLE  
test(a int, b text)  
SERVER csv  
OPTIONS (program `gunzip -c /tmp/data.csv.gz`);

不依賴OS層的通用全球化支援 – ICU庫

編譯時，使用ICU

–with-icu
Build with support for the ICU library.
This requires the ICU4C package to be installed.
The minimum required version of ICU4C is currently 4.2.

By default, pkg-config will be used to find the required compilation options.
This is supported for ICU4C version 4.6 and later.
For older versions, or if pkg-config is not available,
the variables ICU_CFLAGS and ICU_LIBS can be specified to configure, like in this example:

./configure … –with-icu ICU_CFLAGS=`-I/some/where/include` ICU_LIBS=`-L/some/where/lib -licui18n -licuuc -licudata`

(If ICU4C is in the default search path for the compiler,
then you still need to specify a nonempty string in order to avoid use of pkg-config,
for example, ICU_CFLAGS=` `.)

例子

SELECT * FROM t ORDER BY a COLLATE "sv-SE-x-icu";  
valle  
vera  
walle  
wera  
  
SELECT * FROM t ORDER BY a COLLATE "sv-SE-u-co-standard-x-icu";  
valle  
walle  
vera  
wera

scram-sha-256 安全認證

《PostgreSQL 10.0 preview 安全增強 – SASL認證方法之 scram-sha-256 安全認證機制》

監控角色

新增了一些內建的監控角色，不需要使用SUPERUSER，只要給了這些角色，就可以獲得讀一些系統表的許可權，或者獲得殺會話的許可權。

便於管理，提高了安全性。

postgres=# select * from pg_roles ;  
       rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid    
----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------  
 pg_signal_backend    | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4200  
 pg_read_all_stats    | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3375  
 pg_monitor           | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3373  
 pg_read_all_settings | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3374  
 pg_stat_scan_tables  | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3377

動態檢視增強 – 新增系統程式的狀態監控

postgres=# select pid,backend_type,wait_event_type,wait_event from pg_stat_activity ;  
  pid  |    backend_type     | wait_event_type |     wait_event        
-------+---------------------+-----------------+---------------------  
  2077 | background worker   | Activity        | LogicalLauncherMain  
  2074 | autovacuum launcher | Activity        | AutoVacuumMain  
 15397 | client backend      |                 |   
  2072 | background writer   | Activity        | BgWriterMain  
  2071 | checkpointer        | Activity        | CheckpointerMain  
  2073 | walwriter           | Activity        | WalWriterMain  
(6 rows)

等待事件

https://www.postgresql.org/docs/10/static/monitoring-stats.html#monitoring-stats-views

新增了一些等待事件。

Latches  
Extensions  
Client/socket  
Timeout  
...  
  
I/O events  
Reads  
Writes  
Individually identified

IDENTITY列

實際上就是自增列，相容SQL Server。PG原有的serial, default sequence都可以實現類似的功能。

《PostgreSQL 10 新特性 – identity column (serial, 自增)》

XMLTABLE

https://www.postgresql.org/docs/current/static/functions-xml.html#functions-xml-processing-xmltable

xml的支援更加強大了。xmltable可以將XML解析為一張表輸出。

CREATE TABLE xmldata AS SELECT  
xml $$  
<ROWS>  
  <ROW id="1">  
    <COUNTRY_ID>AU</COUNTRY_ID>  
    <COUNTRY_NAME>Australia</COUNTRY_NAME>  
  </ROW>  
  <ROW id="5">  
    <COUNTRY_ID>JP</COUNTRY_ID>  
    <COUNTRY_NAME>Japan</COUNTRY_NAME>  
    <PREMIER_NAME>Shinzo Abe</PREMIER_NAME>  
    <SIZE unit="sq_mi">145935</SIZE>  
  </ROW>  
  <ROW id="6">  
    <COUNTRY_ID>SG</COUNTRY_ID>  
    <COUNTRY_NAME>Singapore</COUNTRY_NAME>  
    <SIZE unit="sq_km">697</SIZE>  
  </ROW>  
</ROWS>  
$$ AS data;  
  
SELECT xmltable.*  
  FROM xmldata,  
       XMLTABLE(`//ROWS/ROW`  
                PASSING data  
                COLUMNS id int PATH `@id`,  
                        ordinality FOR ORDINALITY,  
                        "COUNTRY_NAME" text,  
                        country_id text PATH `COUNTRY_ID`,  
                        size_sq_km float PATH `SIZE[@unit = "sq_km"]`,  
                        size_other text PATH  
                             `concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)`,  
                        premier_name text PATH `PREMIER_NAME` DEFAULT `not specified`) ;  
  
 id | ordinality | COUNTRY_NAME | country_id | size_sq_km |  size_other  | premier_name    
----+------------+--------------+------------+------------+--------------+---------------  
  1 |          1 | Australia    | AU         |            |              | not specified  
  5 |          2 | Japan        | JP         |            | 145935 sq_mi | Shinzo Abe  
  6 |          3 | Singapore    | SG         |        697 |              | not specified