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 <pplachta@xxxxxxxxx> Sent: Saturday, January 25, 2025 4:12 PM To: Laurenz Albe <laurenz.albe@xxxxxxxxxxx> Cc: Edson Richter <edsonrichter@xxxxxxxxxxx>; pgsql-performance@xxxxxxxxxxxxxxxxxxxx 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 <laurenz.albe@xxxxxxxxxxx> 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 > >
