最受開發者歡迎的HTAP數據庫PostgreSQL 10特性

標簽

PostgreSQL , 10 , 特性 , 開發者

背景

作為一款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

新增了一些等待事件。

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

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

CREATE TABLE xmldata AS SELECT  
xml $$  
<ROWS>  
  <ROW >  
    <COUNTRY_ID>AU</COUNTRY_ID>  
    <COUNTRY_NAME>Australia</COUNTRY_NAME>  
  </ROW>  
  <ROW >  
    <COUNTRY_ID>JP</COUNTRY_ID>  
    <COUNTRY_NAME>Japan</COUNTRY_NAME>  
    <PREMIER_NAME>Shinzo Abe</PREMIER_NAME>  
    <SIZE unit="sq_mi">145935</SIZE>  
  </ROW>  
  <ROW >  
    <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