Pipelining in Psql (postgresql 18)

So unless you are a lean startup, the reasons many products are horribly slow are very low hanging fruits no body are ever going to bother picking.

If you ever reach the time where pipelining is giving you a boost in perf, your app was already in a nice state.

It's so nice to be able to code on a baremetal server where my monolith has directly access to my postgres instance on my personal projects.

Suppose client A runs “select * from foo”, which has a thousand records. It can start streaming those results starting with row 1. Now suppose it’s on row 500 when client B runs the same query. Instead of starting over for B, it can start streaming results to B starting at row 501. Each time it reads a row, now it sends that to both clients.

Now when it finishes with row 1000, client A’s query is done. It starts back over with B on row 1 and continues through row 500.

Hypothetically, you can serve N clients with a total of 2 table scans if they all arrive before the first client’s scan is finished.

So that’s the kind of magic where I think this is going to shine. Queue up a few queries and it’s likely that several will be able to share the same underlying work.

"The network is better utilized because successive queries can be grouped in the same network packets, resulting in less packets overall"

For some reason, you don't believe it. OK, let's look at these wireshark network statistics when the test script runs inserting 100k rows, capturing the traffic on the Postgres port.

- case without pipelining (result of "tshark -r capture-file -q z io,stat,0"):

  ==================================== 
  | IO Statistics                    | 
  |                                  | 
  | Duration: 53.1 secs              | 
  | Interval: 53.1 secs              | 
  |                                  | 
  | Col 1: Frames and bytes          | 
  |----------------------------------| 
  |              |1                  | 
  | Interval     | Frames |   Bytes  | 
  |----------------------------------| 
  |  0.0 <> 53.1 | 200054 | 20304504 | 
  ====================================

  ======================================
  | IO Statistics                      |
  |                                    |
  | Duration: 2.209 secs               |
  | Interval: 2.209 secs               |
  |                                    |
  | Col 1: Frames and bytes            |
  |------------------------------------|
  |                |1                  |
  | Interval       | Frames |   Bytes  |
  |------------------------------------|
  | 0.000 <> 2.209 |  10885 | 12219449 |
  ======================================

Again, it's because the client buffers the queries to send in the same batch (this buffer seems to be 64k bytes currently), in addition to not waiting for the results of previous queries.

It has a separate client to server packet that forces previous ones to complete as it will make otherwise-asynchronous (because pipelining) error reporting forcefully serial.

Other than this which is arguably not needed for queries that don't expect errors enough to need early/eager exception throwing during the course of a transaction, it's inherently naturally pipelined as you can just fire two or more statements worth of parameter binding and result fetching back-to-back without blocking on anything.