Thread: Read-only connectios optimizatios

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

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

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