Pipelining in psql (PostgreSQL 18)
17 hours ago
- #Performance
- #PostgreSQL
- #Database
- Pipelining in PostgreSQL allows clients to send queries without waiting for previous results, improving throughput.
- It enables parallel processing by the client, network, and server, optimizing network packet usage.
- Supported since PostgreSQL 7.4 (2003), but libpq-based drivers like psycopg3 started supporting it in 2021 (PostgreSQL 14).
- PostgreSQL 18 introduced psql commands (\startpipeline, \endpipeline, \syncpipeline, \getresults) for easier pipelining in SQL scripts.
- Pipelining creates an implicit transaction; if a query fails, changes since the last sync point are rolled back.
- Performance tests show significant speedups (1.5x–71x) across different network conditions (localhost, LAN, WAN).
- Pipelining is ideal for batches of independent queries, avoiding the complexity of large multi-row queries.
- The feature is backward-compatible; upgrading psql (client) suffices even if the server isn’t PostgreSQL 18.