Thread: It would be nice to clarify is there any point in select queries pipelining
It would be nice to clarify is there any point in select queries pipelining
From
PG Doc comments form
Date:
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!
Re: It would be nice to clarify is there any point in select queries pipelining
From
Laurenz Albe
Date:
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