PostgreSQL 10.0 preview sharding增强 - postgres_fdw 多节点异步并行执行

2 minute read

背景

PostgreSQL 10.0的sharding增强,比如聚合函数的下推算一个。

现在又带来了一个核武器级别的增强,postgres_fdw的异步化调用。

为什么说是核武器级别的呢,比如你有16个sharding节点(对应postgres_fdw的16个库),10.0以前,如果我需要执行一个QUERY涉及到所有的sharding节点时,每个sharding节点是串行执行的然后append结果。

10.0开始,会变成异步化执行,也就是可以并行执行,然后append结果再往下个NODE走。

这样效率会大大提高。

+Asynchronous Execution  
+----------------------  
+  
+In certain cases, it's desirable for a node to indicate that it cannot  
+return any tuple immediately but may be able to do at a later time.  This  
+might either because the node is waiting on an event external to the  
+database system, such as a ForeignScan awaiting network I/O, or because  
+the node is waiting for an event internal to the database system - e.g.  
+one process involved in a parallel query may find that it cannot progress  
+a certain parallel operation until some other process reaches a certain  
+point in the computation.  A process which discovers this type of situation  
+can always handle it simply by blocking, but this may waste time that could  
+be spent executing some other part of the plan where progress could be  
+made immediately.  This is particularly likely to occur when the plan  
+contains an Append node.  
+  
+To use asynchronous execution, a node must first request a tuple from an  
+async-capable child node using ExecAsyncRequest.  Next, it must execute  
+the asynchronous event loop using ExecAsyncEventLoop; it can avoid giving  
+up control indefinitely by passing a timeout to this function, even passing  
+-1 to poll for events without blocking.  Eventually, when a node to which an  
+asynchronous request has been made produces a tuple, the requesting node  
+will receive a callback from the event loop via ExecAsyncResponse. Typically,  
+the ExecAsyncResponse callback is the only one required for nodes that wish  
+to request tuples asynchronously.  
+  
+On the other hand, nodes that wish to produce tuples asynchronously  
+generally need to implement three methods:  
+  
+1. When an asynchronous request is made, the node's ExecAsyncRequest callback  
+will be invoked; it should use ExecAsyncSetRequiredEvents to indicate the  
+number of file descriptor events for which it wishes to wait and whether it  
+wishes to receive a callback when the process latch is set. Alternatively,  
+it can instead use ExecAsyncRequestDone if a result is available immediately.  
+  
+2. When the event loop wishes to wait or poll for file descriptor events and  
+the process latch, the ExecAsyncConfigureWait callback is invoked to configure  
+the file descriptor wait events for which the node wishes to wait.  This  
+callback isn't needed if the node only cares about the process latch.  
+  
+3. When file descriptors or the process latch become ready, the node's  
+ExecAsyncNotify callback is invoked.  

这个patch的讨论,详见邮件组,本文末尾URL。

PostgreSQL社区的作风非常严谨,一个patch可能在邮件组中讨论几个月甚至几年,根据大家的意见反复的修正,patch合并到master已经非常成熟,所以PostgreSQL的稳定性也是远近闻名的。

参考

https://commitfest.postgresql.org/13/856/

https://www.postgresql.org/message-id/flat/CA+TgmoaXQEt4tZ03FtQhnzeDEMzBck+Lrni0UWHVVgOTnA6C1w@mail.gmail.com#CA+TgmoaXQEt4tZ03FtQhnzeDEMzBck+Lrni0UWHVVgOTnA6C1w@mail.gmail.com

Flag Counter

digoal’s 大量PostgreSQL文章入口