4 ms·

I feel pipelines (or batches) are slept upon. So many applications use interactive transactions to ‘batch’ multiple queries, waiting for the result of each indi

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 interactive transactions and instead execute all read-only queries at once, and another write batch after doing processing on the obtained data. That way, the amount of roundtrips is bounded. There are some complications of course, like dealing with concurrency becomes more complicated. I’m currently prototyping a library exploring these ideas.

tmountain 1y ago
Batching in general is slept upon. So many queue systems support batch injection, and I have seen countless cases where a poorly performing system is “fixed” simply by moving away from incremental injection. This stuff is usually on page two of the docs, which explains why it’s so overlooked…
- mpweiher 1y ago
  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 allow us to express it well. And usually by the time we figure out that we need a more reified, batch-oriented mechanism. (the one on page 2) it is too late, the procedural assumptions have been deeply baked into the code we've written so far. See Can programmers escape the gentle tyranny of call/return? by yours truly. https://www.hpi.uni-potsdam.de/hirschfeld/publications/media/Weiher_2020_CanProgrammersEscapeTheGentleTyrannyOfCallReturn_AcmDL.pdf https://www.hpi.uni-potsdam.de/hirschfeld/publications/media... See also: https://news.ycombinator.com/item?id=45367519 https://news.ycombinator.com/item?id=45367519
  - WilcoKruijer 1y ago
    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.
    - mpweiher 1y ago
      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.com/object-relational-mapping-is-the-vietnam-of-computer-science/ https://blog.codinghorror.com/object-relational-mapping-is-t...
      - koakuma-chan 1y ago
        > It's gotten a little better, partly because of greater usr of ORMs. No, just use prepared statements.
        mpweiher 1y ago
        "partly"
- tclancy 1y ago
  That last line is incredibly cruel. We should hang out. I like you.
ako 1y ago
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.
- mxey 1y ago
  You don’t even need driver support, you can use https://www.postgresql.org/docs/current/sql-do.html https://www.postgresql.org/docs/current/sql-do.html
  - ako 1y ago
    Yes, exactly, sql do is the Postgres way of executing an anonymous block.
- hombre_fatal 1y ago
  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 they are referring to.
  - ako 1y ago
    In a plsql block you can use parameters, ref cursors or arrays to return tabular data and do if/then/else/while logic.
mxey 1y ago
I have started to use batching with the Go pgx driver for simple transactions of multiple inserts. Since a batch is automatically a transaction, it’s actually fewer lines of code.
denysonique 1y ago
postgres.js leverages pipelining.
BiteCode_dev 1y ago
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.

tmountain 1y ago

Batching in general is slept upon. So many queue systems support batch injection, and I have seen countless cases where a poorly performing system is “fixed” simply by moving away from incremental injection. This stuff is usually on page two of the docs, which explains why it’s so overlooked…

mpweiher 1y ago
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 allow us to express it well. And usually by the time we figure out that we need a more reified, batch-oriented mechanism. (the one on page 2) it is too late, the procedural assumptions have been deeply baked into the code we've written so far. See Can programmers escape the gentle tyranny of call/return? by yours truly. https://www.hpi.uni-potsdam.de/hirschfeld/publications/media/Weiher_2020_CanProgrammersEscapeTheGentleTyrannyOfCallReturn_AcmDL.pdf https://www.hpi.uni-potsdam.de/hirschfeld/publications/media... See also: https://news.ycombinator.com/item?id=45367519 https://news.ycombinator.com/item?id=45367519
- WilcoKruijer 1y ago
  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.
  - mpweiher 1y ago
    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.com/object-relational-mapping-is-the-vietnam-of-computer-science/ https://blog.codinghorror.com/object-relational-mapping-is-t...
    - koakuma-chan 1y ago
      > It's gotten a little better, partly because of greater usr of ORMs. No, just use prepared statements.
      - mpweiher 1y ago
        "partly"
tclancy 1y ago
That last line is incredibly cruel. We should hang out. I like you.

ako 1y ago

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.

mxey 1y ago
You don’t even need driver support, you can use https://www.postgresql.org/docs/current/sql-do.html https://www.postgresql.org/docs/current/sql-do.html
- ako 1y ago
  Yes, exactly, sql do is the Postgres way of executing an anonymous block.
hombre_fatal 1y ago
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 they are referring to.
- ako 1y ago
  In a plsql block you can use parameters, ref cursors or arrays to return tabular data and do if/then/else/while logic.

mxey 1y ago

I have started to use batching with the Go pgx driver for simple transactions of multiple inserts. Since a batch is automatically a transaction, it’s actually fewer lines of code.

denysonique 1y ago

postgres.js leverages pipelining.

BiteCode_dev 1y ago

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.