Postgres 14: It's The Little Things (Kerstiens) [LWN.net]

Craig Kerstiens highlights some of the "little things" featured in the upcoming PostgreSQL 14 release.

And now in PostgreSQL 14 there is this seemingly small update, pipeline mode, which, according to the docs, allows applications to send a query without having to read the result of the previously sent query. Taking advantage of the pipeline mode, a client will wait less for the server, since multiple queries/results can be sent/received in a single network transaction.

to post comments

Postgres 14: It's The Little Things (Kerstiens)

Posted Sep 22, 2021 20:11 UTC (Wed) by sbaugh (guest, #103291) [Link] (8 responses)

I found it surprising that the PostgreSQL client library is adding support for pipelined requests, because lots of people are hostile towards the use of pipelining in HTTP clients. But reading a bit more, it looks like the PostgreSQL implementation is more full-fledged promise-pipelining support, which requires protocol-level support, not HTTP-style "just spam a bunch of requests down the pipe and expect the results to come back in the same order". So it's not as surprising as it seemed at first.

Postgres 14: It's The Little Things (Kerstiens)

Posted Sep 22, 2021 20:41 UTC (Wed) by mbunkus (subscriber, #87248) [Link] (1 responses)

Seeing how both pipelining & multiplexing (firing off multiple requests simultaneously & receiving the replies in any order) are part of HTTP/2 and HTTP/2 being firmly established by now, your comments seems slightly out of date. Or am I missing something?

Postgres 14: It's The Little Things (Kerstiens)

Posted Sep 22, 2021 20:52 UTC (Wed) by flussence (guest, #85566) [Link]

They were probably comparing it to HTTP/1.1 pipelining, which to be fair was extremely bad. HTTP/2 has its own share of performance problems but not enough to warrant that kind of reaction.

In any case it'll be really nice to have one connection per application in the distant future; I'm running one that has a hardcoded check to yell at you if you give it less than 10(!)

Postgres 14: It's The Little Things (Kerstiens)

Posted Sep 22, 2021 20:57 UTC (Wed) by mokki (subscriber, #33200) [Link] (2 responses)

The article mentions that there are no server side changes for this feature. Thus it is exactly the HTTP 1.1 style pipelining. The server does not even read the next statement from the incoming TCP buffer before it has written the last bytes out.

When I implemented the same pipeling in https://github.com/alaisi/postgres-async-driver 6 years ago the benchmarks showed that one pipeline connection running fast simple selects gave the performance of 4 non pipelined connections.
Setup was gigabit local network between client and server and query was "select 1" statements.

Postgres 14: It's The Little Things (Kerstiens)

Posted Sep 23, 2021 13:22 UTC (Thu) by sbaugh (guest, #103291) [Link] (1 responses)

Yeah but I'm inferring from lines in https://www.postgresql.org/docs/14/libpq-pipeline-mode.html like:

>It's fine for one operation to depend on the results of a prior one; for example, one query may define a table that the next query in the same pipeline uses. Similarly, an application may create a named prepared statement and execute it with later statements in the same pipeline.

that the protocol to speak to PostgreSQL already has unique request IDs that are specified by the user and echoed back on the responses, which is a big difference from HTTP 1.1 pipelining.

Postgres 14: It's The Little Things (Kerstiens)

Posted Sep 23, 2021 13:53 UTC (Thu) by mokki (subscriber, #33200) [Link]

Unfortunately there is no request id in the v3 protocol. The ordering is just a result of what happens when the server does not read nor begin processing the next statement before the previous one has been fully processed and all output written to the client.

https://www.postgresql.org/docs/current/protocol.html mentions in cancelling request section it mentions that server does not check for new input on the connection before the previous one is completed.

HTTP pipelining

Posted Sep 23, 2021 20:09 UTC (Thu) by tialaramex (subscriber, #21167) [Link] (2 responses)

Two things blow up HTTP/1.1 pipelining, and don't affect PostgreSQL (or at least, only in weird corner cases)

1. This was insoluble and is why 1.1 pipelining is disabled in most clients. Real, supposedly production quality HTTP servers existed that could not get this right. Typically behind the scenes there's some concurrency bug, where pipelining causes multiple threads to be trying to answer your pipelined HTTP requests in parallel. That's definitely not what the protocol design says, but the bad designs which allowed this "worked" fine if you don't do pipelining so, guess what the workaround is.

2. Middleboxes. Even if every server is doing this correctly somehow, maybe there's a PipeliningWorks flag and you finally got the vendors who were getting it wrong NOT to enable the flag on their server, the Middleboxes ruin the party by of course adding the same behaviour even when servers aren't defective. This worsens the first problem by giving the server vendor an excuse, their behaviour is actually correct, see, this Cisco branded Security Device exhibits exactly the same behaviour and it has Security right in the name.

PostgreSQL *is* the server and they presumably know their server code doesn't get this wrong, eliminating (1). Middleboxes do not understand the PostgreSQL protocol so although they might block your connection or try to talk HTTP to it, they are unlikely to make this particular feature break so (2) isn't likely either.

HTTP pipelining

Posted Sep 27, 2021 0:21 UTC (Mon) by NYKevin (subscriber, #129325) [Link] (1 responses)

Fortunately, if you're doing HTTPS (and frankly, in 2021 you really should be doing HTTPS), then middleboxes are only involved to the extent that either the server or the client explicitly invites them to be involved. Which means that you can collapse them into "the server" or "the client" because one or the other first party is actually responsible for that middlebox. Sure, maybe you're routing traffic through Cloudflare or somebody else, but they're "the server" from the end user's perspective, so you have to care about what Cloudflare is doing with your traffic and whether they are handling it correctly.

HTTP pipelining

Posted Oct 4, 2021 20:32 UTC (Mon) by tialaramex (subscriber, #21167) [Link]

If you speak TLS 1.3 only (or I suppose QUIC) then this is more or less true. The TLS specification is pretty clear that what you've described is, and always has been, the only correct way to implement a TLS middlebox. Build a client and a server, bolt them together with whatever nonsense you fancy in the middle, and you're compliant.

Unfortunately a LOT of TLS 1.2 and earlier products don't do that, because it's expensive and they don't care. The main reason that changed in TLS 1.3 is that instead of non-compliant products causing mayhem, which of course the vendors would blame on anybody else but themselves, they now don't work at all.

There was actually a window, after TLS 1.3 shipped for about a year, when some products did still get it wrong and, rather than cause those products to malfunction Google at least (and I presume Mozilla and Apple?) silently crippled a feature called Downgrade Prevention so that they'd stay working until their vendors could get customers upgraded. I was a little worried that vendors would keep foot dragging and this would become an indefinite problem, but Google stayed true to their word. Specifically, implementing TLS requires random numbers. So a compliant middlebox needs a LOT of random numbers to be both client and server in each connection. You should probably seed a PRNG from a high quality source of entropy and use that. But, the cheap (non-compliant) option is to just copy-paste the numbers the "real" client and server send. If anybody in the world implements TLS 1.3 this blows up, as it was entitled to do because you didn't comply with the design. Of course all the time _before_ it blew up it was a gaping security hole in your system, but that's neither here nor there when your goal is to sell the most snakeoil for the highest price.