Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Pipelining in psql (PostgreSQL 18)

postgresql.verite.pro

Post reply on HN
Print view View original

31–40 of 41 posts

Re: Pipelining in psql (PostgreSQL 18)

#31

Post by namibj »

chris_pie wrote: post #17

I wish the author explained the difference between pipelines and multi-statement queries

There are no multi-statement queries in the binary protocol (where you get things like native cursors/pagination to efficiently iterate over result rows, and where you get the true parameter binding that is inherently robust against SQL injection.

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.

Re: Pipelining in psql (PostgreSQL 18)

#32

Post by BiteCode_dev »

WilcoKruijer wrote: post #2

I feel pipelines (or batches) are slept upon. So many applications use interactive transactions to ‘batch’ multiple queries, waiting for the result of each individual query. Network roundtrip is the biggest contributor to latency in most applications, and this makes it so much worse. Most Postgres drivers don’t even support batching, at least in the JavaScript world. In many cases it would be good to forego interacti…

Most of my big clients have about 10 intermediaries between them and the data: the antivirus, the browser, the VPN, the company proxy, the API gateway, their authentication layer, the virtualization layer, the application server, the microservice it requests and whatever data source this one requests.

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.

Re: Pipelining in psql (PostgreSQL 18)

#33

Post by sivakusayan »

kstrauser wrote: post #29
vlovich123 wrote: post #13

I’m pretty sure the reasoning and conclusion is way off on explaining the speed up: > The network is better utilized because successive queries can be grouped in the same network packets, resulting in less packets overall. > the network packets are like 50 seater buses that ride with only one passenger. The performance improvement is not likely to be because you’re sending larger packets, since most queries transfer…

I’m not sure that’s it either. PostgreSQL has a feature — don’t remember what it’s called — where multiple readers can share a serial table scan. 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…

> I’m not sure that’s it either. PostgreSQL has a feature — don’t remember what it’s called — where multiple readers can share a serial table scan.

Maybe referring to synchronize_seqscans?

https://www.postgresql.org/docs/current/runtime-config-compa...

Re: Pipelining in psql (PostgreSQL 18)

#34

Post by vlovich123 »

kstrauser wrote: post #29
vlovich123 wrote: post #13

I’m pretty sure the reasoning and conclusion is way off on explaining the speed up: > The network is better utilized because successive queries can be grouped in the same network packets, resulting in less packets overall. > the network packets are like 50 seater buses that ride with only one passenger. The performance improvement is not likely to be because you’re sending larger packets, since most queries transfer…

I’m not sure that’s it either. PostgreSQL has a feature — don’t remember what it’s called — where multiple readers can share a serial table scan. 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…

That literally isn’t what pipelining is about in general nor is it relevant to this benchmark which is an insertion workload. The performance benefit observed literally is the ability to start executing the second request even though the ACK for the first one hasn’t fully ACK’ed.

It’s also not true pipelining since you can’t send a follow up request that depends on the results of the previous incomplete request (eg look at capnproto promise pipelining). As such the benefit in practice is actually more limited, especially if instead here you use connection pooling and send the requests over different connections in the first place - I’d expect very similar performance numbers for the benchmark assuming you have enough connections open in parallel to keep the DB busy.

Re: Pipelining in psql (PostgreSQL 18)

#35

Post by koakuma-chan »

mpweiher wrote: post #24
WilcoKruijer wrote: post #14

Earlier quoted context omitted.

This analysis makes sense to me, but at the same time: we’re already switching between procedural and declarative when switching from [mainstream language] to SQL. This impedance mismatch (or awkwardness) is already there, might as well embrace it.

We are switching...but how and at what cost? We put SQL programs as strings into our other programs, often dynamically constructing them using procedure calls and then dispatching them using yet more procedure calls. If that weren't yikes enough, SQL injection bugs used to be the #1 exploited security vulnerabilities. It's gotten a little better, partly because of greater usr of ORMs. ORMs? https://blog.codinghorror.…

> It's gotten a little better, partly because of greater usr of ORMs.

No, just use prepared statements.

Re: Pipelining in psql (PostgreSQL 18)

#36

Post by stephen »

porsager wrote: post #26
stephen wrote: post #23

Earlier quoted context omitted.

Oh hello! Very happy to hear from you, and even happier to be wrong about your "AWOL-ness" (since I want to ship postgres.js to prod). :-) My assumption was just from, afaict, the general lack of triage on GitHub issues, i.e. for a few needs we have like tracing/APM, and then also admittedly esoteric topics like this stack trace fixing: https://github.com/porsager/postgres/issues/963#issuecomment... Fwiw I definitely…

Thanks a lot. You're spot on about issue triage etc. I haven't had the time to keep up, but I read all issues when they're created and deal with anything critical. I'm using Postgres.js myself in big deployments and know others are too. The metrics branch should be usable, and I could probably find time to get that part released. It's been ready for a while. I do have some important changes in the pipeline for v4, bu…

Great to hear you're using postgres.js in prod/large deployments! That sort of real-world-driven usage/improvements/roadmap imo leads to the best results for open source projects.

Also interesting about a potential v4! I'll keep lurking on the github project and hope to see what it brings!

Re: Pipelining in psql (PostgreSQL 18)

#37

Post by mpweiher »

koakuma-chan wrote: post #35
mpweiher wrote: post #24

Earlier quoted context omitted.

We are switching...but how and at what cost? We put SQL programs as strings into our other programs, often dynamically constructing them using procedure calls and then dispatching them using yet more procedure calls. If that weren't yikes enough, SQL injection bugs used to be the #1 exploited security vulnerabilities. It's gotten a little better, partly because of greater usr of ORMs. ORMs? https://blog.codinghorror.…

> It's gotten a little better, partly because of greater usr of ORMs. No, just use prepared statements.

"partly"

Re: Pipelining in psql (PostgreSQL 18)

#38

Post by evanelias »

byroot wrote: post #11
codesnik wrote: post #7

Earlier quoted context omitted.

ah, of course it have been discussed already https://discuss.rubyonrails.org/t/proposal-adding-postgres-p...

Yes, the need isn't exactly the same. `load_async` use case if for known slow-ish queries, hence for which you want actual parallelization on the server. Since that discussion on the forum, I talked more about pipelining with some other core devs, and that may happen in some form or another in the future. The main limiting factor is that most of the big Rails contributors work with MySQL, not Postgres, and MySQL does…

re: MySQL, strictly speaking, that isn't true; proper pipelining was introduced starting in MySQL 5.7, ten years ago. However it requires using the newer "X Protocol", which isn't widely supported by third-party drivers, nor is it supported in MariaDB. So adoption has been poor.

Re: Pipelining in psql (PostgreSQL 18)

#39

Post by ako »

hombre_fatal wrote: post #28
ako wrote: post #4

Earlier quoted context omitted.

I would expect most drivers to support (anonymous) stored procedures so you can batch/pipeline multiple queries into one statement to be executed by the database. Probably more a problem of developers not knowing how to use databases properly, not so much a limitation of technology.

People don't do that because when you're writing insert/update queries, you tend to want to write logic based on the value of intermediate results, and also you can't return tabular data from a DO block (they operate as a function returning void). You also can't use parameterized values like $1, $2. It seems more niche than you're suggesting. Though I wish people would write app layer pseudocode to demonstrate what t…

In a plsql block you can use parameters, ref cursors or arrays to return tabular data and do if/then/else/while logic.

Re: Pipelining in psql (PostgreSQL 18)

#40

Post by dverite »

vlovich123 wrote: post #13

I’m pretty sure the reasoning and conclusion is way off on explaining the speed up: > The network is better utilized because successive queries can be grouped in the same network packets, resulting in less packets overall. > the network packets are like 50 seater buses that ride with only one passenger. The performance improvement is not likely to be because you’re sending larger packets, since most queries transfer…

The blog post says as the top:

"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 | 
  ====================================
- case with pipelining:

  ======================================
  | 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 |
  ======================================
So compared to needing 2 packets per query in the non-pipelining case, the pipelining case needs about 10 times less.

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.

Post reply on HN