21–30 of 41 posts
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…
I wish the author explained the difference between pipelines and multi-statement queries
I really want to use pipelining for our "em.flush" of sending all INSERTs & UPDATEs to the db as part of a transaction, b/c my initial prototyping showed a 3-6x increase: https://joist-orm.io/blog/initial-pipelining-benchmark/ If you're not in a transaction, afaiu pipelining is not as applicable/useful b/c any SQL statement failing in the pipeline fails all other queries after it, and imo it would suck for separate/u…
I'm right here - what are you missing?
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 sympathize with issue triage being time-consuming/sometimes a pita, i.e. where a nontrivial/majority of issues are from well-meaning but maybe naive users asking for free support/filing incorrect/distracting issues.
I don't have an answer, but just saying that's where my impression came from.
Thanks for replying!
Earlier quoted context omitted.
My guess is that this is because our default way of expressing code execution is the procedure call, meaning the default unit of code that we can later is the procedure, which needs to execute synchronously. That's what our programming languages support directly, and that's just how "things are done". Everything else both feels weird and also truly is awkward to express because our programming languages don't really…
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.
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.com/object-relational-mapping-is-t...
Earlier quoted context omitted.
I'm right here - what are you missing?
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…
Jeez.
That said, I hope node-postgres can support this soon. As it stands, every single query you add to a transaction adds a serial network roundtrip which is devastating not just in execution time but how long you're holding any locks inside the transaction.
Earlier quoted context omitted.
I'm right here - what are you missing?
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…
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…
That was a pretty nasty assumption you made about them though: That they're MIA because they're upset that their pet project isn't as popular as they'd like. Jeez. That said, I hope node-postgres can support this soon. As it stands, every single query you add to a transaction adds a serial network roundtrip which is devastating not just in execution time but how long you're holding any locks inside the transaction.
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…
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.
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 they are referring to.
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…
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.
How do I handle, say 100K concurrent transactions in an OLTP database? Here are my learnings that make this difficult,
- a transaction has a one-to-one mapping with a connection
- a connection can only process one transaction at at time, so pooling isn't going to help.
- database connections are "expensive"
- a client can open at maximum, 65k connections as otherwise it would run out of ports.
A 100k connections isn't that crazy; say you have 100k concurrent users and each one needs a transaction to manage it's independent state. Transactions are useful as they enforce consistency.