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

11–20 of 41 posts

Re: Pipelining in psql (PostgreSQL 18)

#11

Post by byroot »

codesnik wrote: post #7
codesnik wrote: post #6

activerecord in Rails has async mode, which allows you to queue several requests and read results later. But those will go through the connection pool, and will be executed in separate connections, separate transactions, and separate PostgreSQL server processes. I wonder if using pipelining instead, on a driver level (app code would be the same), would be a better approach in general, or at least easier on db instanc…

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 doesn't really have proper pipelining support.

Re: Pipelining in psql (PostgreSQL 18)

#12

Post by mpweiher »

tmountain wrote: post #3
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…

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…

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...

See also: https://news.ycombinator.com/item?id=45367519

Re: Pipelining in psql (PostgreSQL 18)

#13

Post by vlovich123 »

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 very little data and the benchmark the conclusion is drawn from definitely is transferring near 0 data. The speed up comes from removing waiting on a round trip ack of a batch from executing subsequent queries; the number of network packets is irrelevant.

Re: Pipelining in psql (PostgreSQL 18)

#14

Post by WilcoKruijer »

mpweiher wrote: post #12
tmountain wrote: post #3

Earlier quoted context omitted.

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…

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.

Re: Pipelining in psql (PostgreSQL 18)

#15

Post by ako »

mxey wrote: post #10
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.

You don’t even need driver support, you can use https://www.postgresql.org/docs/current/sql-do.html

Yes, exactly, sql do is the Postgres way of executing an anonymous block.

Re: Pipelining in psql (PostgreSQL 18)

#18

Post by stephen »

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/unrelated web requests that "share a pipeline" to have one request fail the others -- but for a single txn/single request, these semantics are what you expect anyway.

Unfortunately in the TypeScript ecosystem, the node-pg package/driver doesn't support pipelining yet, instead this "didn't quite hit mainstream adoption and now the author is AWOL" driver does: https://github.com/porsager/postgres

I've got a branch to convert our TypeScript ORM to postgres.js solely for this "send all our INSERTs/UPDATEs/DELETEs in parallel" perf benefit, and have some great stats so far:

https://github.com/joist-orm/joist-orm/pull/1373#issuecommen...

But it's not "must have" for us atm, so haven't gotten time to rebase/ship/etc...hoping to rebase & land the PR by eoy...

Re: Pipelining in psql (PostgreSQL 18)

#19

Post by tclancy »

tmountain wrote: post #3
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…

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…

That last line is incredibly cruel. We should hang out. I like you.

Re: Pipelining in psql (PostgreSQL 18)

#20

Post by porsager »

stephen wrote: post #18

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?
Post reply on HN