PostgreSQL 16 五大更新-權限管理、邏輯複寫、使用體驗與效能升級(實測提升 300 % !)
內容目錄
PostgreSQL 開發團隊於 2023 年 9 月 24 日釋出了最新的 PostgreSQL 16 版開源資料庫,該版本在平行查詢、批次處理和邏輯複寫有顯著的效能提升,使其在處理大量數據時更為高效。此版本在 SQL/JSON 語法上提供更進階的支援,並提供更靈活的權限管理設定,讓使用者依據自身需求定義存取規則。另外,PostgreSQL 16 也優化了 VACUUM 和 ANALYZE 指令,並新增 pg_stat_io 系統表來加強效能監控。
PostgreSQL 16 五大升級
一、權限管理(Privilege Administration)
PostgreSQL 16 對權限管理進行深度優化與調整。在 PostgreSQL 15 的架構中,所有核心權限都集中於超級使用者,這種管理模式雖然對單一使用者或小型開發團隊來說直觀且高效,但當應用於大型組織,尤其在使用者眾多且各具特定權限需求的狀況下,這種集中化的權限管理方式往往不太靈活。
為了解決上述問題,Postgres 16 修改了 CREATEROLE 指令,讓使用者只能在具有 ADMIN OPTION 的角色下授予權限。這項更新讓系統管理員能夠更精確地分配的權限,另外,PostgreSQL 16 還加強了對 pg_hba.conf 和 pg_ident.conf 設定檔的管理,讓使用者對用戶和資料庫名稱進行正規表示法匹配,或用外部設定檔指定規則,增加管理效率。
PostgreSQL 16 還新增針對客戶端安全性的連線參數,例如透過 require_auth 讓客戶端指定想要的驗證方式,以及透過 sslrootcert=”system” 參數,讓 PostgreSQL 使用客戶端作業系統內建的憑證頒發機構。另外,此次改版也支援 Kerberos 的認證代理,讓 postgres_fdw 和 dblink 擴充套件使用憑證連接到其他受信服務。
二、邏輯複寫(Logical Replication)
PostgreSQL 16 對邏輯複寫進行多項性能優化。其中一項是為備援資料庫(Stand By)導入 Logical Decoding(邏輯解碼)功能,讓使用者在執行資料交易時,快照資料交易狀態並將其儲存於 WAL 檔,這種方式能夠有效降低使用檢查點(Check Point)的需求,而使用者能透過新的 pg_log_standby_snapshot() 函數來完成。
另外,PostgreSQL 16 利用平行查詢技術處理大型資料交易。當主節點正在執行資料交易時,系統能夠進行平行處理。基準測試(Benchmark Testing)顯示,PostgreSQL 16 與前幾個版本相比,執行 Bulk Insert 的效率提升了將近 40%。
PostgreSQL 16 還更新了其他邏輯複寫效能,包括初始化時的位元資料複製技術、對沒有主鍵(Primary Key)的表格,訂閱者(logical replication subscriber)可以使用 B-Tree 索引查詢資料列,從而取代序列掃描。在特定情境中使用者甚至能透過二進制格式增加初始表的同步效率。在資安方面,為確保資料的安全性與完整性,PostgreSQL 16 只允許資料表擁有者在執行邏輯複寫時使用 SELECT 和 DML 語法,這種更嚴格的權限管理策略要求訂閱者在複寫集(Replicatoin Set)中具有 SET ROLE 權限或具備超級用戶權限。
三、提升開發者使用體驗
PostgreSQL 16 新增了更多 SQL/JSON 標準語法,其中包括由 JSON 格式組成的建構子和述詞,例如:JSON_ARRAY()、JSON_ARRAYAGG() 以及 IS JSON。另外,PostgreSQL 16 還導入使用底線作為分隔符號(例如:5_432_000)和小數點以外的字符功能(例如:0x1538、0o12470 和 0b1010100111000)。
在 psql 工具方面,PostgreSQL 16 新增了許多指令。其中,bind 指令可以協助開發者準備參數化查詢,並透過該語法替代參數,例如:SELECT $1::int + $2::int bind 1 2 g。
另外,PostgreSQL 16 還內建對 ICU (國際統一編碼)的支援,這項功能會根據使用者的主機環境來確定文本排序方式,並允許使用者指定 ICU 排序規則。
四、效能優化
PostgreSQL 16 透過優化查詢執行能力,大幅提升平行查詢結果。這次更新讓 FULL Join 和 RIGHT Join 能在並行模式下執行,而 aggregate group 和 parallel aggregate 指令則能提升 SQL 的運行效率。在 PostgreSQL 16 中 SELECT DISTINCT 查詢指令新增了增量排序(Incremental Sorting)功能,以提升資料處理效率。
另外,此次更新也改進了 Right 和 Outer 處理 Anti Join 的功能,讓使用者更容易查詢到資料表中不存在的資料列。
無論是單一或並行運算, 當使用 COPY 指令執行批次載入時,效能能夠提升高達 300% 。而 PostgreSQL 也為使用 libqp 的客戶端新增負載平衡機制,並對 Vacuum 語法進行優化,從而減少使用全表凍結(Full Table Freeze)的必要性,這代表未來 PostgreSQL 資料庫的高可用性和讀寫分離將更容易實現。
另外,PostgreSQL 16 在 x86 和 ARM 架構中使用 SIMD 增加 CPU 效能,因此在處理 ASCII 、 JSON 字串和陣列時能提高效能。
PostgreSQL 16 也同時進行了其他性能優化,例如對 RANGE 和 LIST 進行分區查詢以及允許使用者控制執行 ANALYZE / VACUUM 指令使用的 Shared Buffer 大小。
歐立威科技實測 Vacuum_buffer_usage_limit
維運作業上,新加入的參數 vacuum_buffer_usage_limit ,提供管理者更具彈性的 Vacuum 作業資源使用控制。以下為歐立威科技進行的 Vacuum 效能控制測試結果(測試資源規格:2 Cores、記憶體 16 G、Shared_buffers 4 GB)。
【測試資源規格】
記憶體 2 Cores、16 G、Shared_buffers 4 GB
【完整結果整理】
vacuum_buffer_usage_limit |
128 KB |
256 KB |
512 KB |
16 MB |
32 MB |
64 MB |
128 MB |
512 MB |
1 GB |
0 |
10,000,000筆 |
00:36.140 |
00:37.562 |
00:42.059 |
00:41.784 |
00:36.708 |
00:38.253 |
00:45.894 |
00:41.521 |
00:36.907 |
00:43.541 |
50,000,000筆 |
17:43.174 |
09:07.084 |
13:20.967 |
04:14.966 |
04:45.293 |
05:15.165 |
04:08.615 |
03:57.306 |
04:02.515 |
04:01.309 |
100,000,000筆 |
50:03.429 |
22:57.062 |
28:12.120 |
11:36.249 |
10:03.580 |
10:10.014 |
10:50.820 |
11:35.666 |
10:23.680 |
10:40.886 |
- 表格大小 16 G時,參數設定 32 MB達到 Vacuum 最快之最小值。
- 表格大小 7974 MB時,參數設定 512 MB達到 Vacuum 最快之最小值。
- 表格大小 1595 MB時,因資料量太小,參數設定後 Vacuum 速度差異不大,實際效果可能取決於主機的實際狀況。
【觀察數據】
【測試結論】
1. Vacuum 操作的時間會受到表格數據量的影響,大表格需要更長的時間來完成 Vacuum。
2. 調整 vacuum_buffer_usage_limit 參數可以影響 Vacuum 的速度,但這並非絕對規則, 實際效果可能取決於主機的實際狀況。
3. 單一 Vacuum 作業使用 shared buffer 的上限約為 32 MB。這可能是 Vacuum 程式本身功能的瓶頸,有一個開口向上的曲線,形成一個 Vacuum 最快之最小值。
4. 建議將 vacuum_buffer_usage_limit 調整為 0 使 Vacuum 作業接近最快之最小值。
5. 此參數可以依 Session 進行設定,也可以直接在資料庫設定檔中指定參數大小。對於處理大表的 VACUUM FULL,可以透過 Session 的方式設定,以達到彈性調整的效果。
五、監控效能
PostgreSQL 16 對監控效能進行優化,並新增 pg_stat_io 視觀表,為使用者提供更完整的 IO 監測資訊,此外,它也能夠紀錄資料表上一次執行 Seq Scan 和 Index Scan 的時間,以及監測記憶體中資料行的搬移行為,並評估搬移機率。
在 pg_locks 視觀表中,PostgreSQL 16 加入了推測鎖定(Speculate Lock)的資訊,並導入能夠針對特殊後台設備進行分析與追蹤的功能。最後,PostgreSQL 16 也加入多種新的等待事件(Wait Event),讓 16 版的監控效能相較以往更加全面。
PostgreSQL 16 在 pg_stat_all_tables 系統表中新增用於紀錄表格或索引最後執行掃描的時間欄位。為了提高 auto_explain 的可讀性,PostgreSQL 16 會紀錄傳遞到參數化語句中的值。另外,此次改版優化了用於執行 pg_stat_statements 和 pg_stat_activity 的查詢追蹤演算法,以提高查詢的精確性。
歐立威科技持續追蹤與掌握 PostgreSQL 16 的最新動態。若有任何關於 PostgreSQL 應用、技術或建置的問題,歡迎立即諮詢,讓我們的專業團隊為您解答並提供支援。