魔天记忘语小说,完美世界有声小说全集,我欲封天耳根小说零

作者： xiongcc，來源：PostgreSQL學徒

前言

距離 PostgreSQL 17 正式發布已近半年，按照每年發布一個大版本的慣例，PostgreSQL 18 預計將在 2025 年底發布。距離正式發布還有一段時間，社區的開發工作仍在如火如荼地進行中。

雖然本文中列舉的許多新特性最終可能會有變化，但這并不妨礙我們展望 PostgreSQL 18 中可能引入的新特性，讓我們一覽為快 ~

可觀測性

pg_stat_all_tables

在 pg_stat_all_tables 中新增了 (auto) vacuum 和 (auto) analyze 的相關耗時指標，這對于我們診斷 VACUUM 問題的時候無疑大有裨益。

內存上下文

在 pg_backend_memory_contexts 視圖中新增了 type、path 和 parent 三個字段，關于內存上下文就不再贅述，感興趣的可以閱讀：https://smartkeyerror.com/PostgreSQL-MemoryContext[1]

pg_stat_checkpointer

在 pg_stat_checkpointer 視圖中，新增了 num_done 字段，pg_stat_checkpointer 中現有的 num_timed 和 num_requested 計數器用于跟蹤已完成和跳過的檢查點，但無法僅計數已完成的檢查點。

因為在 PostgreSQL 中，檢查點也有 skip 機制，當非停庫、REDO 完成或者強制觸發檢查點時，如果數據庫沒有寫入操作，則直接返回，不需要再去遍歷一下 shared buffer 去刷臟，提升檢查點的性能。

 /*
  * If this isn't a shutdown or forced checkpoint, and if there has been no
  * WAL activity requiring a checkpoint, skip it. The idea here is to
  * avoid inserting duplicate checkpoints when the system is idle.
  */
 if((flags & (CHECKPOINT_IS_SHUTDOWN | CHECKPOINT_END_OF_RECOVERY |
         CHECKPOINT_FORCE)) ==0)
  {
   if(last_important_lsn == ControlFile->checkPoint)
    {
      END_CRIT_SECTION();
      ereport(DEBUG1,
          (errmsg_internal("checkpoint skipped because system is idle")));
     return;
    }
  }

但是現有的 num_timed 是無法區分的，所以此次提交引入了 num_done 計數器，它僅跟蹤已完成的檢查點，從而更容易查看實際執行了多少個檢查點。

Note that checkpoints may be skipped if the server has been idle since the last one, and this value counts both completed and skipped checkpoints

pg_stat_database

pg_stat_database 中新增了如下兩個字段

?parallel_workers_to_launch?parallel_workers_launched

顧名思義，看到這個數據庫中并行的使用情況。

另外，在 pg_stat_statements 中也新增了額外兩個類似指標

pg_stat_subscription_stats

主要新增了一些用于觀察沖突的列：

?confl_insert_exists?confl_update_origin_differs?confl_update_exists?confl_update_missing?confl_delete_origin_differs?confl_delete_missing

在日志中也有所體現：

pg_stat_get_backend_io

新增了 pg_stat_get_backend_io 函數，用于返回指定后端進程的 I/O 統計信息

結合 pg_stat_activity，如虎添翼：

postgres=#SELECT*
      FROMpg_stat_get_backend_io( pg_backend_pid() )
     WHEREbackend_type='client backend'
      ANDobject='relation'
      ANDcontext='normal';
-[ RECORD1]--+---------------
backend_type  | client backend
object     | relation
context    | normal
reads     | 122
read_time   | 0
writes     | 0
write_time   | 0
writebacks   | 0
writeback_time | 0
extends    | 49
extend_time  | 0
op_bytes    | 8192
hits      | 11049
evictions   | 0
reuses     |
fsyncs     | 0
fsync_time   | 0
stats_reset  |

統計信息

對于 ANALYZE，在 18 版本中可以看到資源消耗情況以及 WAL 的使用情況

其次還新增了 ONLY 關鍵字，目的是解決在處理分區表時，VACUUM 和 ANALYZE 操作的一些不便之處。默認情況下，Autovacuum 進程不會自動對分區表執行 ANALYZE，用戶必須手動執行。然而手動執行時，又會遞歸去分析每個分區，對于較大的分區表無疑會十分耗時，尤其是當表的列數很多時。為了解決這個問題，18 中引入了 ONLY 關鍵字，指定僅對主表進行操作，跳過對分區的處理。這樣，用戶可以避免在分區表上執行遞歸分析，節省時間。

這個行為讓我想起了在 Greenplum 中，有個針對根分區的 optimizer_analyze_root_partition 參數。

對于分區表，當在表上運行 ANALYZE 命令時收集根分區的統計信息。GPORCA 使用根分區統計信息來生成一個查詢計劃。而遺傳查詢優化器并不使用這些數據。

性能

Hash Right Semi Join

在 18 中，支持了 Hash Right Semi Join (也支持并行)，是的 Richard Guo 大佬。

以下是 17 中的例子，優化器選擇了基于大表 ticket_flights 進行 HASH，這無疑會消耗更多資源

=# EXPLAIN (costs off, analyze)
SELECT*FROMflights
WHEREflight_idIN(SELECTflight_idFROMticket_flights);
                     QUERY PLAN                     
------------------------------------------------------------------------------------------------
Hash Join (actual time=2133.122..2195.619 rows=150588 loops=1)
 Hash Cond: (flights.flight_id = ticket_flights.flight_id)
 -> Seq Scan on flights (actual time=0.018..10.301 rows=214867 loops=1)
 -> Hash (actual time=2132.969..2132.970 rows=150588 loops=1)
    Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 7343kB
    -> HashAggregate (actual time=1821.476..2114.218 rows=150588 loops=1)
       Group Key: ticket_flights.flight_id
       Batches: 5 Memory Usage: 10289kB Disk Usage: 69384kB
       -> Seq Scan on ticket_flights (actual time=7.200..655.356 rows=8391852 loops=1)
Planning Time: 0.325 ms
Execution Time: 2258.237 ms
(11 rows)

在 18 中，第四行我們可以看到，優化器選擇了 Parallel Hash Right Semi Join，基于 flights 去構建了 HASH，執行時間也有倍數的提升。

                     QUERY PLAN                      
---------------------------------------------------------------------------------------------------
Gather (actualtime=56.771..943.233rows=150590loops=1)
 Workers Planned:2
 Workers Launched:2
 -> Parallel Hash Right Semi Join (actualtime=41.754..909.894rows=50197loops=3)
    Hash Cond: (ticket_flights.flight_id = flights.flight_id)
    -> Parallel Seq Scan on ticket_flights (actualtime=0.047..221.511rows=2797284loops=3)
    -> Parallel Hash (actualtime=40.309..40.309rows=71622loops=3)
       Buckets:262144 Batches:1 Memory Usage:23808kB
       -> Parallel Seq Scan on flights (actualtime=0.008..6.631rows=71622loops=3)
Planning Time:0.555ms
Execution Time:949.831ms
(11rows)

Self-Join Elimination

當查詢中的表與自身進行內連接時，如果可以證明該連接在查詢結果中沒有實際作用，可以用掃描操作代替這個自連接。這種優化有助于減少查詢計劃的復雜度，特別是在涉及分區表時。該優化的主要效果包括：

?減少范圍表的長度：特別是對于分區表，消除不必要的自連接有助于減少表列表中的項數。?減少限制條件的數量：從而減少了選擇性估算，并可能提高查詢計劃的預測準確性。

這項優化通過替代自連接為更高效的掃描操作來減少查詢計劃的復雜性，尤其在處理分區表時具有顯著的性能優勢。搭配上 Hash Right Semi Join，使得 18 中的優化器能力更上一層樓。

UUID v7

在 18 中，另一個比較令人驚喜的是 v7 UUID 的支持 — 結合了以毫秒為單位的 Unix 時間戳和隨機位，提供唯一性和可排序性，UUID v7 采用時間戳作為生成 UUID 的核心部分，這意味著它是有序的。與 UUID v4 的隨機性不同，UUID v7 生成的 UUID 在時間上具有自然的順序。這樣的有序性在數據庫和分布式系統中具有重要優勢，特別是在數據插入、索引和查詢時，有序的 UUID 使得數據可以更好地分布和排序，避免了 UUID v4 生成的隨機分布可能導致的性能問題。

非 V7 的 UUID 其危害我已經寫過不少文章進行闡述了，那么在 18 以前如何實現 v7 呢？可以參照 Howtos 里面的相關文章：

?https://postgres-howto.cn/#/./docs/64?id=how-to-use-uuid[2]?https://postgres-howto.cn/#/./docs/65?id=uuid-v7-and-partitioning-timescaledb[3]

使用唯一索引檢測冗余的 GROUP BY 列

原本在 GROUP BY 包含關系表的所有主鍵列時，所有其他不屬于主鍵的列可以從 GROUP BY 子句中移除，因為這些列在功能上依賴于主鍵，并且主鍵本身足以確保組的唯一性。這個優化特性被擴展到不僅適用于主鍵索引，還支持任何唯一索引。也就是說，如果表上存在一個唯一索引，優化器可以使用該索引來移除 GROUP BY 中冗余的列。

針對這個，讓我想起了另一個內核知識點，我們知道，對于 group by，非聚合列必須包含在 group by 子句中，否則會報如下錯誤 xxx must appear in the GROUP BY clause or be used in an aggregate function

postgres=#createtabletest(idintprimarykey,info text);                                                                                                
CREATETABLE                                                                                                                       
postgres=#insertintotestvalues(1,'hello');                                                                                                       
INSERT01                                                                                                                                                                                                                                   
postgres=#insertintotestvalues(2,'world');                                                                                                       
INSERT01                                                                                                                                                                                                                                   
postgres=#insertintotestvalues(3,'postgres');                                                                                                     
INSERT01                                                                                                                        
postgres=#insertintotestvalues(4,'postgres');                                                                                                     
INSERT01                                                                                                                        
postgres=#select*fromtest;                                                                                                               
id| info                                                                                                                        
----+----------                                                                                                                      
 1 | hello                                                                                                                        
 2 | world                                                                                                                        
 3 | postgres                                                                                                                       
 4 | postgres                                                                                                                       
(4 rows)

當非聚合列不包含在 group by 子句中會報錯，但是如果按照主鍵的話，就不會報錯

postgres=#selectid,count(*)fromtestgroupbyinfo;                                                                                                   
ERROR: column"test.id" must appearintheGROUPBYclauseorbe usedinan aggregatefunction                                                                              
LINE1:selectid,count(*)fromtestgroupbyinfo;                                                                                                  
       ^        
postgres=#selectid,info,count(*)fromtestgroupbyid;                                                                                                 
id| info |count                                                                                                                   
----+----------+-------                                                                                                                  
 2 | world  |   1                                                                                                                   
 3 | postgres |   1                                                                                                                   
 4 | postgres |   1                                                                                                                   
 1 | hello  |   1                                                                                                                   
(4 rows)


postgres=# select id,info,count(*) from test group by id,info;                                                                                               
id |  info  | count                                                                                                                   
----+----------+-------                                                                                                                  
 2 | world  |   1                                                                                                                   
 3 | postgres |   1                                                                                                                   
 4 | postgres |   1                                                                                                                   
 1 | hello  |   1                                                                                                                   
(4 rows)

因為如果是按照主鍵進行分組，由于主鍵的原因，那么該行必然是唯一的，即使加上其他的列，也是固定的分組。但是比較可惜的是，截止目前只能是主鍵，唯一約束 + not null 也不行，雖然語義是一樣的，代碼里有說明

/*
* remove_useless_groupby_columns
*   Remove any columns in the GROUP BY clause that are redundant due to
*   being functionally dependent on other GROUP BY columns.
*
* Since some other DBMSes do not allow references to ungrouped columns, it's
* not unusual to find all columns listed in GROUP BY even though listing the
* primary-key columns would be sufficient. Deleting such excess columns
* avoids redundant sorting work, so it's worth doing. When we do this, we
* must mark the plan as dependent on the pkey constraint (compare the
* parser's check_ungrouped_columns() and check_functional_grouping()).
*
* In principle, we could treat any NOT-NULL columns appearing in a UNIQUE
* index as the determining columns. But as with check_functional_grouping(),
* there's currently no way to represent dependency on a NOT NULL constraint,
* so we consider only the pkey for now.
*/
staticvoid
remove_useless_groupby_columns(PlannerInfo *root)
{
  Query   *parse = root->parse;
  Bitmapset **groupbyattnos;
  Bitmapset **surplusvars;
  ListCell  *lc;
 int    relid;

值得一提的是，在 16 中支持了 any_value，用于解決這種問題。

pg_set_relation_stats

截止最新版 17，PostgreSQL 中還沒有官方方法來手動調整優化器統計信息，在 18 中已經可以初步做到了

以這篇文章的例子為例https://www.dbi-services.com/blog/postgresql-18-tweaking-relation-statistics/[4]

postgres=#createtablet ( aint, b text );
CREATETABLE
postgres=#insertintotvalues(1,'aa');
INSERT01
postgres=#insertintotselecti,'bb'fromgenerate_series(2,100) i;
INSERT099
postgres=# analyze t;
ANALYZE
postgres=#createindex iont(b);
CREATEINDEX
postgres=# d t
        Table"public.t"
Column| Type |Collation|Nullable|Default
--------+---------+-----------+----------+---------
a   | integer |      |     |
b   | text  |      |     |
Indexes:
  "i" btree (b)


postgres=# select relpages,reltuples from pg_class where relname = 't';
relpages | reltuples
----------+-----------
    1 |    100
(1 row)


postgres=# explain select * from t where b = 'aa';
         QUERY PLAN          
-------------------------------------------------
Seq Scan on t (cost=0.00..2.25 rows=1 width=7)
 Filter: (b = 'aa'::text)
(2 rows)

雖然有索引，但是只有一個數據塊并且只有一行滿足條件，優化器認為走順序掃描更快，現在可以通過 pg_set_relation_stats 調整統計信息 (臨時的，手動或自動分析都會覆蓋)，讓優化器走了索引掃描。

postgres=# select * from pg_set_relation_stats('t'::regclass,1,1000000);
pg_set_relation_stats
-----------------------
t
(1row)
postgres=# x
Expanded displayisoff.
postgres=# select relpages,reltuples from pg_classwhererelname= 't';
relpages|reltuples
----------+-----------
    1 |   1e+06
(1row)
postgres=# explain select * from twhereb ='aa';
             QUERY PLAN              
-----------------------------------------------------------------
Index Scan using i on t (cost=0.17..183.18rows=10000width=7)
 Index Cond: (b ='aa'::text)
(2rows)

真不錯，看似一小步，實則是一大步！在德哥的吐槽大會上，有一期[5]也是吐槽優化器的，有一段內容如下：

VACUUM

autovacuum_vacuum_max_threshold

新增了一個 autovacuum_vacuum_max_threshold 參數，PostgreSQL 默認使用 autovacuum_vacuum_threshold 和 autovacuum_vacuum_scale_factor 兩個參數來計算何時對表進行自動清理。這兩個參數通常適用于小型表，使其更頻繁地進行 VACUUM 操作，以確保性能。然而，對于非常大的表，即使更新操作的絕對數量較多，按照比例計算，更新操作所占的比例仍然可能較低，導致這些表不太可能觸發自動清理。

autovacuum_vacuum_max_threshold 解決了這個問題，簡單粗暴，允許指定一個絕對的更新次數閾值，一旦表中的更新次數達到該閾值，就會觸發自動清理操作。這可以確保對于那些更新數量很大的表，VACUUM 操作不會因為表的相對更新比例較低而被推遲。

autovacuum_max_workers

現在修改 autovacuum_max_workers 不需要重啟了，直接 reload 即可

其次在日志中可以看到 delay time 了

track_cost_delay_timing

另外新增了一個 track_cost_delay_timing 參數，啟用后，將記錄基于成本的清理延遲統計信息的時間，用于清理和分析操作，并將在 pg_stat_progress_analyze 和 pg_stat_progress_vacuum 中的 delay_time 列中可見，不過在計時較差的平臺上，也可能會造成較大的性能影響，因此默認是關閉的。