Pipelining in psql (PostgreSQL 18)

Summary

What is pipelining in Postgres? Pipelining is a client-side feature supported by the network protocol that basically consists of not waiting for the results of previously sent queries before sending the next. This increases the throughput in two ways: The client, network and server can work in parallel. For instance, the network may transmit the results of the (N-1)th query while the server executes the Nth query and the client sends the (N+1)th query, all this at the same time. The network is better utilized because successive queries can be grouped in the same network packets, resulting in less packets overall. Pipelining is possible since version 7.4 (released in 2003), which introduced the extended query protocol. But it’s only since 2021, with PostgreSQL 14, that it can be used through libpq, the client-side C library. Since then, some libpq-based drivers like psycopg3 have started to support it. With PostgreSQL 18, released last week, psql, the command line client comes equipped with commands to use pipelining in SQL scripts, making it even more accessible. While this addition is not part of the highlighted features of that release, it can provide huge gains in query throughput, as we’re going to see in a simple test. psql commands The pipeline is started with \startpipeline, and in the most simple case, followed by the SQL queries and ended with \endpipeline. If intermediate results are needed, we can use \syncpipeline to force a synchronisation point and \getresults to fetch all results up to that point. Also, starting a pipeline creates an implicit transaction. If a query fails, all the changes since the start (or before the last synchronization point) will be rolled back. If you know about the \; syntax to group several queries in the same request, there are similarities between this technique and pipelining: they’re both used to reduce server round-trips and have the same semantics with regard to transactions. In a way, pipelining is the evolution in the ...