public inbox for [email protected]help / color / mirror / Atom feed
Read-only connectios optimizatios 4+ messages / 4 participants [nested] [flat]
* Read-only connectios optimizatios @ 2025-01-25 14:55 Edson Richter <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Edson Richter @ 2025-01-25 14:55 UTC (permalink / raw) To: [email protected] <[email protected]> Scenario: -PostgreSQL 13 latest version; -I have some reporting users, with "SELECT,REFERENCES" permissions on all tables in the public schema. -Connections are established using the jdbc "readonly" attribute. Does PostgreSQL perform any optimization on queries in this scenario to avoid establishing locks? Or are these queries treated like any other? Thanks 😊 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Read-only connectios optimizatios @ 2025-01-25 18:01 Laurenz Albe <[email protected]> parent: Edson Richter <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Laurenz Albe @ 2025-01-25 18:01 UTC (permalink / raw) To: Edson Richter <[email protected]>; [email protected] <[email protected]> On Sat, 2025-01-25 at 14:55 +0000, Edson Richter wrote: > -Connections are established using the jdbc "readonly" attribute. > > Does PostgreSQL perform any optimization on queries in this scenario to avoid > establishing locks? Or are these queries treated like any other? The only difference that I am aware of is that read-only transactions at the SERIALIZABLE isolation level can release predicate locks earlier, which can benefit performance. But I don't think that you need to worry: reading transactions only take an ACCESS SHARE lock on tables, which won't conflict with data modifications. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Read-only connectios optimizatios @ 2025-01-25 22:12 peter plachta <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: peter plachta @ 2025-01-25 22:12 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Edson Richter <[email protected]>; [email protected] You can still block vacuum from running if you have long running (or very aggressive) read transactions. I don’t think they are very helpful or performant from a Postgres engine perspective. They can be helpful in application development because they will fail if devs attempt any mutations inside read only (from what I recall). Sent from my iPhone > On Jan 25, 2025, at 10:01 AM, Laurenz Albe <[email protected]> wrote: > > On Sat, 2025-01-25 at 14:55 +0000, Edson Richter wrote: >> -Connections are established using the jdbc "readonly" attribute. >> >> Does PostgreSQL perform any optimization on queries in this scenario to avoid >> establishing locks? Or are these queries treated like any other? > > The only difference that I am aware of is that read-only transactions at the > SERIALIZABLE isolation level can release predicate locks earlier, which can > benefit performance. > > But I don't think that you need to worry: reading transactions only take an > ACCESS SHARE lock on tables, which won't conflict with data modifications. > > Yours, > Laurenz Albe > > ^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: [EXT] Re: Read-only connectios optimizatios @ 2025-01-27 17:41 Wong, Kam Fook (TR Technology) <[email protected]> parent: peter plachta <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Wong, Kam Fook (TR Technology) @ 2025-01-27 17:41 UTC (permalink / raw) To: peter plachta <[email protected]>; Laurenz Albe <[email protected]>; +Cc: Edson Richter <[email protected]>; [email protected] <[email protected]> Just a thought if you are moving to AWS Aurora Postgres. You can point the read queries/transaction/long running read queries to the reader node. That will not block the autovacuum process. -----Original Message----- From: peter plachta <[email protected]> Sent: Saturday, January 25, 2025 4:12 PM To: Laurenz Albe <[email protected]> Cc: Edson Richter <[email protected]>; [email protected] Subject: [EXT] Re: Read-only connectios optimizatios External Email: Use caution with links and attachments. You can still block vacuum from running if you have long running (or very aggressive) read transactions. I don’t think they are very helpful or performant from a Postgres engine perspective. They can be helpful in application development because they will fail if devs attempt any mutations inside read only (from what I recall). Sent from my iPhone > On Jan 25, 2025, at 10:01 AM, Laurenz Albe <[email protected]> wrote: > > On Sat, 2025-01-25 at 14:55 +0000, Edson Richter wrote: >> -Connections are established using the jdbc "readonly" attribute. >> >> Does PostgreSQL perform any optimization on queries in this scenario >> to avoid establishing locks? Or are these queries treated like any other? > > The only difference that I am aware of is that read-only transactions > at the SERIALIZABLE isolation level can release predicate locks > earlier, which can benefit performance. > > But I don't think that you need to worry: reading transactions only > take an ACCESS SHARE lock on tables, which won't conflict with data modifications. > > Yours, > Laurenz Albe > > ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-01-27 17:41 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-01-25 14:55 Read-only connectios optimizatios Edson Richter <[email protected]> 2025-01-25 18:01 ` Laurenz Albe <[email protected]> 2025-01-25 22:12 ` peter plachta <[email protected]> 2025-01-27 17:41 ` Wong, Kam Fook (TR Technology) <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox