CYBERTEC Logo

Pipeline mode for better PostgreSQL performance on slow networks

03.2022 / Category: / Tags: |
pipeline mode in real life
© Laurenz Albe 2022

 

It is known that high network latency is bad for database performance. PostgreSQL v14 has introduced “pipeline mode” for the libpq C API, which is particularly useful to get decent performance over high-latency network connections. If you are using a hosted database in “the cloud”, then this article might be interesting for you.

The PostgreSQL extended query protocol

To understand pipeline mode, we have to understand the message flow between client and server. Using the extended query protocol, statements get processed as follows:

  • a “Parse” message with the statement is sent to the server
  • a “Bind” message with parameter values is sent to the server
  • an “Execute” message is sent to the server, requesting query results

The database transaction is finished by sending a “Sync” message. These messages are typically sent in a single TCP packet. All of the above messages are generated by a call to the PQexec or PQexecParams functions of libpq. In the case of a prepared statement, the “Parse” step is separated from the “Bind” and “Execute” steps.

After “Sync” has been sent, the client waits for a response from the server. The server processes the statement and replies with

  • a “ParseComplete” message
  • a “BindComplete” message
  • “Data” or “NoData” messages, depending on the type of statement
  • a “CommandComplete” message to indicate that the statement finished processing

Finally, the server sends a “ReadyForQuery” message to indicate that the transaction is complete and it is ready for more. Again, these messages are typically sent in a single TCP packet.

How pipeline mode works

Pipeline mode is nothing new on the frontend/backend protocol level. It just relies on the fact that you can send more than one statement before sending “Sync”. This allows you to send multiple statements in a single transaction without waiting for a response from the server. What is new is support for this from the libpq API. PostgreSQL v14 introduced the following new functions:

  • PQenterPipelineMode: enter pipeline mode
  • PQsendFlushRequest: sends a “Flush” message to tell the server to start sending back responses to previous requests right away (otherwise, the server tries to bundle all responses into a single TCP packet)
  • PQpipelineSync: sends a “Sync” message – you must call this explicitly
  • PQexitPipelineMode: leave pipeline mode
  • PQpipelineStatus: shows if libpq is in pipeline mode or not

The statements themselves are sent using the asynchronous query execution functions PQsendQuery, PQsendQueryParams and PQsendQueryPrepared, and after the “Sync” message has been sent, PQgetResult is used to receive the responses.

Since all that does not rely on new features in the frontend/backend protocol, you can use pipeline mode with older versions of the PostgreSQL server.

Performance advantages of pipeline mode

Let's assume the case of a simple money transfer using a table like

To transfer money from one account to the other, we have to run a transaction like

With normal processing, that makes four round trips from the client to the server, so the whole transaction will incur eight times the network latency.

normal mode postgrasql performance

Using the pipeline mode, you can get away with only twice the network latency:

  • the second UPDATE statement can be sent immediately after the first one
  • there is no need for an explicit transaction, since a pipeline automatically is a single transaction

pipeline mode for better postgrasql performance

A code sample using pipeline mode

This is C code that can be used to process the above transaction. It uses a prepared statement stmt for this UPDATE statement:

In order to focus on the matter at hand, I have omitted the code to establish a database connection and prepare the statement.

Measuring the speed improvement with pipeline mode

To verify the improvement in speed , I used the tc utility on my Linux system to artificially add a 50 millisecond latency to the loopback interface:

This can be reset with

I measured the time spent in the above function, as well as the time for a function that used no pipeline and an explicit transaction:

no pipeline (8 times network latency) pipeline (2 times network latency)
first attempt 406 ms 111 ms
second attempt 414 ms 104 ms
third attempt 414 ms 103 ms

With short SQL statements like these, the speed gain from pipelining is almost proportional to the client-server round trips saved.

An alternative way to get a similar speedup

If you don't want to use the libpq C API or directly speak the frontend/backend protocol, there is another way to get a similar performance improvement: you can write a PL/pgSQL function or procedure.

This will also run inside a single transaction and is just as fast, because

  • you have only a single client-server round trip for the CALL statement
  • execution plans for SQL statements in PL/pgSQL are cached

Writing a PL/pgSQL procedure is in this case probably the simpler solution. However, pipeline mode allows you precise control over the message and data flow between client and server, which you won't get with a function.

You may not be able to use this solution in case you operate in an environment where database functions are an anathema. But then, strictly held religious beliefs can be expected to make you suffer occasionally!

Conclusion

Pipeline mode, new with the libpq C API in PostgreSQL v14, allows for considerable performance improvements with laggy network connections. Since it doesn't use new features in the frontend/backend protocol, it can be used with old server versions as well. Often, similar performance gains can be achieved using PL/pgSQL functions.

If you are interested in data manipulation performance, you may want to read my article about HOT updates.

5 2 votes
Article Rating
Subscribe
Notify of
guest
3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Shawn123
Shawn123
2 years ago

Why not to use pipeline mode by default?
Or, when not to use pipeline? any caveat?

laurenz
laurenz
2 years ago
Reply to  Shawn123

That would break existing code.
Now, you can write PGsendQuery - PGgetResult - PGsendQuery - PGgetResult, but that would be an error in pipeline mode (you'd have to send an explicity "sync" in between to close the first pipeline.

Pavlo Golub
Pavlo Golub
2 years ago
Reply to  Shawn123

Actually, that's rare situation when you need to send several statements one by one without fetching results

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    3
    0
    Would love your thoughts, please comment.x
    ()
    x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram