Note: In the original version of this post, there were some issues missed. These have been addressed at the bottom of the post.
I’d like to introduce to you a very cool feature introduced in PostgreSQL, the Pipeline Mode.
So just what exactly is Pipeline Mode? Pipeline Mode allows applications to send a query without having to read the result of the previously sent query. In short, it provides a significant performance boost by allowing multiple queries and results to be sent and received in a single network transaction.
As with all good ideas, there is precedent: one can emulate such behavior with a little application code wizardry. Alternatively known as “Batch Mode”, running asynchronous communications between a client and its server has been around for some time. There are a number of existing solutions batching multiple queries in an asynchronous fashion. For example, PgJDBC has supported batch mode for many years using the standard JDBC batch interface. And of course, there’s the old reliable standby dblink.
What distinguishes Pipeline Mode is that it provides an out-of-the-box solution greatly reducing the application code’s complexity handling the client-server session.
Traditional BATCH MODE Operations
Pipeline Mode
Although introduced in PostgreSQL 14, pipeline mode works against any currently supported version of postgres as the enhancement is in the LIBPQ which is used by the client and not the server itself!
And now for the bad news, of a sort; leveraging Pipeline Mode requires using “C” or an equivalent programming language capable of interfacing directly with LIBPQ. Unfortunately, there’s not too much out there yet in the way of ODBC development offering the requisite hooks taking advantage of this enhanced feature. Therefore, one is required to design and program the client-application session in the said programming language.
HINT: This is a great way for somebody to make a name for themselves and create a convenient interface to the LIBPQ Pipeline Mode.
Now that I’ve issued the requisite caveat, let’s talk about how this mechanism works.
Keeping things simple
Since each SQL statement is essentially idempotent it is up to the client logic to make sense of the results. Sending SQL statements and pulling out results that have no relation with each other is one thing but life gets more complicated when working with logical outcomes that have some level of interdependence.
It is possible to bundle asynchronous SQL statements as a single transaction. But as with all transactions, failure of any one of these asynchronously sent SQL statements will result in a rollback for all the SQL statements.
Of course, the API does provide error handling in the case of pipeline failures. In the case of a FATAL condition, when the pipeline itself fails, the client connection is informed of the error thus flagging the remaining queued operations as lost. Thereafter normal processing is resumed as if the pipeline was explicitly closed by the client, and the client connection remains active.
For the C programmer at heart, here’s a couple of references that I can share with you:
Remember to check with the postgres documentation which has more to say here.
Hello all, apologies for the tardy reply. I wrote this blog just before leaving on a company adventure into the Mountains of Ecuador at the beginning of February. It’s taken me a while to catch up since my return and I’d like to address some of the comments posted here as well as elsewhere.
The funny thing about blogged comments is although we’re always looking for approval that tells us that we’re doing our job, sometimes we realize that there are issues that have been missed and need to be addressed a little more in-depth.
The first thing that needs to be said is in the way of a correction to the blog posting i.e. after SQL statements have been sent, responses from the postgres server DO NOT come back out of sequence-based upon their completion. Although SQL statements can be sent in a non-blocking manner, once they arrive at the server they are handled and returned in a sequential manner.
Doing a little online research brought us to this little gem of a discussion, https://github.com/brianc/node-postgres/pull/2706, that says it quite nicely:
“Important: pipelining has nothing to do with query execution concurrency/parallelism. With or without pipe-lining mode, the PostgreSQL server is executing the queries sequentially (while using parallelism capabilities if enabled), pipe-lining just allows both sides of the connection to work concurrently when possible, and to minimize round-trip time.”
The pipeline essentially makes the network file descriptor non-blocking allowing the client to submit multiple requests in a non-blocking way. All the requests are going through the same SOCK_FD and the client needs now to monitor the associated SOCK_FD (select, poll, epoll, io_uring, kqueue, etc…).
Putting it another way “Pipeline mode allows us to send everything as a train” making pipeline mode a definite plus in situations where the Round-Trip Time (RTT) is significant.
We validated the observations using two methods; the first, and easy way, was running pgbench in pipeline mode and yes it does support it in postgres 14. The second method was looking into the libpq source code while running some simple queries.
Here’s an example of what I mean; suppose we send a series of queries with SELECT pg_sleep() having alternating values of one second and a higher value. Query 1, with a sleep of one second, returns right away, and query 2 returns twenty-seven seconds after that. But query 3 is blocked until after query two completes. The other ones follow the same execution pattern returning only after the previous query has fully cycled. And even though they are immediately sent to the server they still return sequentially:
|
1 |
Query 1: SELECT repeat('0', 10), pg_sleep(1) -- returns after 1 second<br><br>Query 2: SELECT repeat('1', 10), pg_sleep(27) -- returns after 1+27 seconds<br><br>Query 3: SELECT repeat('2', 10), pg_sleep(1) -- returns after 1+27+1 seconds<br><br>Query 4: SELECT repeat('3', 10), pg_sleep(21) -- returns after 1+17+1+21 seconds<br><br>Query 5: SELECT repeat('4', 10), pg_sleep(1) -- returns after 1+17+1+21+1 seconds |
Another way of putting it is to say that if the second SQL statement takes longer than all the other ones following it’s still going to be returned as the 2nd query and NOT the last one to return i.e. sequential rather than async.
I’ll be honest and say that this was a fast investigation so it’s easy to have missed something.
In conclusion, I’d like to compliment Alvarro and the team working on this technology. It holds great promise but overall, at this point in time at least, deciding to use this mechanism makes sense for very specific use cases.
