This blogpost is about understanding the network interaction between a postgres client and a postgres server. YugabyteDB implements the postgres server on top of its distributed storage, and therefore acts identical to standard postgres.

The first thing to look at is postgres query processing. Any query executed with postgres needs to go through a number of phases before it return results. These steps are:

• Parse (syntactic): break down text into a parse tree.
• Parse (semantic): validate existence, etc. of parse tree elements.
• Rewrite: convert parse tree to query trees.
• Plan: convert query tree to planned statement nodes for most efficient execution.
• Execute: use planned statement nodes to execute.

It's beyond the scope of this article to go through these in great detail, but the important part is there are a number of steps that must be taken by the postgres server before SQL represented as text is executed as SQL and the result is processed and returned [1].

SQL is sent to the postgres server via the postgres protocol. There are multiple postgres protocol versions (2 and 3) [2].

Once a connection is established, it allows the client to perform a number of tasks, which are controlled by the requests that it send. For performing a query and getting the results back, there are two common ways to do so, based on the type of request: using the simple query message: using a 'Q' or query message [3], and using the extended query messages, using 'P', 'B' and 'E' messages, for parse, bind and execute [4].

The simple query protocol is, as the name suggests, really simple: a 'Q' message, along with the query is sent to the postgres server indicating the simple query protocol and all the steps are performed, and one or more messages are sent back to the client. This means the total response time of the query on the client is all of the steps of parse, rewrite, plan and execute on the server side, plus the latencies of sending the query request and getting all the response packets.

The extended query protocol is more comprehensive, and breaks up query execution into 3 steps:

• Parse
• Bind
• Execute

As you see, these are slightly different than the postgres server side execution steps of parse, rewrite, plan and execute. The extended protocol query steps group some of the server side work, in the following way:

• Extended query protocol 'Parse': server side parse syntactic, parse semantic and rewrite steps.
• Extended query protocol 'Bind': server side plan step.
• Extended query protocol 'Execute': server side execute step.

For a given client SQL text to execute each of the steps must be performed. However, once a query exists on the server side using the extended query protocol parse step, it can be used by the bind and execute steps multiple times. This is commonly called a prepared statement.

Another important thing to realise is that the different steps of parse, bind and execute do not need individual packets/messages from the client to be performed by the postgres server. If that were true, there commonly wouldn't be any merit in using the extended protocol over the simple query protocol, because in lots of cases the network latency of performing the steps would make the simple query protocol outperform the extended query protocol.

The extended query protocol allows to send the parse, bind and execute requests along with the SQL inside a single packet, or over multiple packets as the client sees fit. The way the postgres server knows when to perform work is when it sees a sync ('S') message after one or more of the parse, bind and execute messages [5].

Using the flexibility and fine-grained control of the extended query protocol, a postgres client can parse a prepared statement, and then bind and execute multiple times, allowing more granular ways of executing SQL against a postgres server, allowing more efficiency than using the simple query protocol. Of course this depends on the efficiency and implementation of the extended query protocol by the client.

A postgres client is mostly unaware of the different execution steps on the server side. A simple query protocol execution is always unaware, because all steps are always performed before a response is returned to the client. For an extended query protocol execution it is dependent on how the parse, bind and execute messages are sent together with a sync message. Some implementations of a client explicitly perform parse as a definition of a prepared statement, and thus makes the parse stage measurable, but it's also very common to have bind and data rows as the result of execution be combined in the request, and the responses of bind successful and data returned by execution combined in the server response.

The postgres 'psql' and YugabyteDB 'ysqlsh' command-line tools always perform execution via the simple query protocol. For other database clients protocol usage is dependent on the implementation: most programming languages allow to choose simple or extended protocol communication.

The server side response time of execution of both the simple query protocol and extended query protocol can be seen by setting the postgres parameter 'log_min_duration_statement' or YugabyteDB tablet server flag 'ysql_log_min_duration_statement', and is set as a threshold of total query response time in milliseconds. Setting it to 0 logs all queries, setting it to -1 disables logging. The log line that is the result of setting it shows a single line for simple query execution, and lists the steps and latency of parse, bind and execute for extended query protocol.

[1]
https://www.postgresql.org/docs/11/overview.html
[2]
https://www.postgresql.org/docs/11/protocol.html
[3]
https://www.postgresql.org/docs/11/protocol-flow.html#id-1.10.5.7.4
[4]
https://www.postgresql.org/docs/11/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY
[5]
https://beta.pgcon.org/2014/schedule/attachments/330_postgres-for-the-wire.pdf