RE: [EXT] Re: Read-only connectios optimizatios

Just a thought if you are moving to AWS Aurora Postgres.  You can point the read queries/transaction/long running read
queriesto the reader node.  That will not block the autovacuum process.  
 

-----Original Message-----
From: peter plachta <pplachta@gmail.com> 
Sent: Saturday, January 25, 2025 4:12 PM
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Edson Richter <edsonrichter@hotmail.com>; pgsql-performance@lists.postgresql.org
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
theyare 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
(fromwhat I recall).
 

Sent from my iPhone

> On Jan 25, 2025, at 10:01 AM, Laurenz Albe <laurenz.albe@cybertec.at> 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
> 
>