🔗 Pipelining in psql (PostgreSQL 18)

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.