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 視觀表，為使用者提供更完整的 IＯ 監測資訊，此外，它也能夠紀錄資料表上一次執行 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 應用、技術或建置的問題，歡迎立即諮詢，讓我們的專業團隊為您解答並提供支援。
