pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedIt would be nice to clarify is there any point in select queries pipelining
2+ messages / 2 participants
[nested] [flat]
* It would be nice to clarify is there any point in select queries pipelining
@ 2024-01-25 04:46 PG Doc comments form <noreply@postgresql.org>
2024-01-25 14:45 ` Re: It would be nice to clarify is there any point in select queries pipelining Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 2+ messages in thread
From: PG Doc comments form @ 2024-01-25 04:46 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: evgeny.v.smirnov@gmail.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/protocol-flow.html
Description:
Greeting!
Please consider the following exchange with a PG database (Kotlin + Reactor+
r2dbc-postgresql):
```
295 1.063166 127.0.0.1 50591 127.0.0.1 32797 PGSQL 111
>Q ---> BEGIN ISOLATION LEVEL REPEATABLE READ, READ WRITE
296 1.063219 127.0.0.1 32797 127.0.0.1 50591 TCP 56
32797 → 50591 [ACK] Seq=1 Ack=56 Win=6373 Len=0 TSval=3266177882
TSecr=3728690767
301 1.069912 127.0.0.1 32797 127.0.0.1 50591 PGSQL 73
<C/Z
302 1.069938 127.0.0.1 50591 127.0.0.1 32797 TCP 56
50591 → 32797 [ACK] Seq=56 Ack=18 Win=6370 Len=0 TSval=3728690774
TSecr=3266177889
712 1.099829 127.0.0.1 50591 127.0.0.1 32797 PGSQL 551
>Q ------> select * from ... (#2)
713 1.099858 127.0.0.1 32797 127.0.0.1 50591 TCP 56
32797 → 50591 [ACK] Seq=18 Ack=551 Win=6365 Len=0 TSval=3266177919
TSecr=3728690804
715 1.099985 127.0.0.1 50591 127.0.0.1 32797 PGSQL 551
>Q ------> select * from ... (#4)
717 1.100009 127.0.0.1 32797 127.0.0.1 50591 TCP 56
32797 → 50591 [ACK] Seq=18 Ack=1046 Win=6358 Len=0 TSval=3266177919
TSecr=3728690804
719 1.100082 127.0.0.1 50591 127.0.0.1 32797 PGSQL 551
>Q ------> select * from ... (#1)
720 1.100106 127.0.0.1 32797 127.0.0.1 50591 TCP 56
32797 → 50591 [ACK] Seq=18 Ack=1541 Win=6350 Len=0 TSval=3266177919
TSecr=3728690804
722 1.100164 127.0.0.1 50591 127.0.0.1 32797 PGSQL 551
>Q ------> select * from ... (#3)
723 1.100192 127.0.0.1 32797 127.0.0.1 50591 TCP 56
32797 → 50591 [ACK] Seq=18 Ack=2036 Win=6342 Len=0 TSval=3266177919
TSecr=3728690804
735 1.114695 127.0.0.1 32797 127.0.0.1 50591 PGSQL 424
<T/D/C/Z ------> Results for #2
737 1.114741 127.0.0.1 50591 127.0.0.1 32797 TCP 56
50591 → 32797 [ACK] Seq=2036 Ack=386 Win=6364 Len=0 TSval=3728690818
TSecr=3266177933
773 1.121732 127.0.0.1 32797 127.0.0.1 50591 PGSQL 1468
<T/D/D/D/D/D/D/D/D/D/D/D/D/C/Z ------> Results for #4
774 1.121757 127.0.0.1 50591 127.0.0.1 32797 TCP 56
50591 → 32797 [ACK] Seq=2036 Ack=1798 Win=6342 Len=0 TSval=3728690826
TSecr=3266177941
785 1.126793 127.0.0.1 32797 127.0.0.1 50591 PGSQL 594
<T/D/D/D/C/Z ------> Results for #1
786 1.126820 127.0.0.1 50591 127.0.0.1 32797 TCP 56
50591 → 32797 [ACK] Seq=2036 Ack=2336 Win=6334 Len=0 TSval=3728690831
TSecr=3266177946
805 1.135197 127.0.0.1 32797 127.0.0.1 50591 PGSQL 497
<T/D/D/C/Z ------> Results for #3
806 1.135222 127.0.0.1 50591 127.0.0.1 32797 TCP 56
50591 → 32797 [ACK] Seq=2036 Ack=2777 Win=6327 Len=0 TSval=3728690839
TSecr=3266177954
847 1.138848 127.0.0.1 50591 127.0.0.1 32797 PGSQL 68
>Q ------> COMMIT
848 1.138876 127.0.0.1 32797 127.0.0.1 50591 TCP 56
32797 → 50591 [ACK] Seq=2777 Ack=2048 Win=6342 Len=0 TSval=3266177958
TSecr=3728690843
853 1.144624 127.0.0.1 32797 127.0.0.1 50591 PGSQL 74
<C/Z
```
The application code is the following (for the sake of clarity):
```
val transactionalOperator = TransactionalOperator.create(
transactionManager,
DefaultTransactionDefinition() .apply {
isolationLevel = TransactionDefinition.ISOLATION_REPEATABLE_READ
}
)
val users = Flux.defer {
Flux.create {
it.next(1)
it.next(2)
it.next(3)
it.next(4)
it.complete()
}
.parallel(4)
.runOn(Schedulers.parallel())
.flatMap {
databaseClient.sql(
"""
select * from …
""".trimIndent())
.map { row, _ -> EntityConverter().convert(row) }
.all()
.reduce(EntityReducer())
}
}.`as`(transactionalOperator::transactional)
```
I’m aware that Postgres doesn’t support cursor(portal) multiplexing so its
impossible to fetch data for two selects simultaneously but should I make
use of selects pipelining when I want to make the most of the DB? I mean to
say does a single Postgres backend employs concurrent processing and its
possible for a DB to transmit results for a query #2 (see the traffic) and
concurrently parse/rewrite/plan/optimize the subsequent (pipelined) queries
(issued from the same transaction). Neither chapter 55 nor 52 expand on it
(or it’s hard to spot at least).
Thanks!
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: It would be nice to clarify is there any point in select queries pipelining
2024-01-25 04:46 It would be nice to clarify is there any point in select queries pipelining PG Doc comments form <noreply@postgresql.org>
@ 2024-01-25 14:45 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2024-01-25 14:45 UTC (permalink / raw)
To: evgeny.v.smirnov@gmail.com; pgsql-docs@lists.postgresql.org
On Thu, 2024-01-25 at 04:46 +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/protocol-flow.html
>
> Please consider the following exchange with a PG database (Kotlin + Reactor+
> r2dbc-postgresql):
>
> [trace of a pipelined message flow]
>
> The application code is the following (for the sake of clarity):
>
> [some Java code]
>
> I’m aware that Postgres doesn’t support cursor(portal) multiplexing so its
> impossible to fetch data for two selects simultaneously but should I make
> use of selects pipelining when I want to make the most of the DB? I mean to
> say does a single Postgres backend employs concurrent processing and its
> possible for a DB to transmit results for a query #2 (see the traffic) and
> concurrently parse/rewrite/plan/optimize the subsequent (pipelined) queries
> (issued from the same transaction). Neither chapter 55 nor 52 expand on it
> (or it’s hard to spot at least).
No, PostgreSQL is single-threaded (with the exception of parallel query execution).
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-01-25 14:45 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-25 04:46 It would be nice to clarify is there any point in select queries pipelining PG Doc comments form <noreply@postgresql.org>
2024-01-25 14:45 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox