Thread: Does creating readOnly connections, when possible, free upresources in Postgres?

(resending to remove HTML formatting)

I have an API server and I'm trying to be conscientious managing Postgres's resources carefully. On the client side, I
havea Hikari Pool. 

Usually when I need a connection, I simply create a default read/write connection, even if I don't plan to make any
updatesor inserts or hold any locks. But most of my database connections are in fact read-only. 

I saw that when you create a JDBC connection, you can specify readOnly=true. Would doing so somehow help Postgres
manageits other connections? Perhaps Postgres, knowing that a connection is readOnly and will never even attempt to do
anupdate, will free up some internal resources for other connections. Is this accurate? 

"David Kremer" <jkorders@gmx.com> writes:
> I saw that when you create a JDBC connection, you can specify readOnly=true. Would doing so somehow help Postgres
manageits other connections? Perhaps Postgres, knowing that a connection is readOnly and will never even attempt to do
anupdate, will free up some internal resources for other connections. Is this accurate? 

No.

            regards, tom lane

Hi David,


 > I saw that when you create a JDBC connection, you can specify
 > readOnly=true. Would doing so somehow help Postgres manage its other
 > connections?


if you know that a certain connection will be ready-only, you could use 
a more aggressive pooling strategy.

Usually, a connection pool will return a worker thread to the pool when 
the client closes the connection.
pgbouncer for example offers a "statement" pooling strategy, meaning 
that a worker connection is returned to the pool after every completed 
SQL statement.
That way, a single Postgresql server thread can easily serve many client 
connections.
If a specific usecase fits into a single command, you can even use it 
for these writes!

Of course, you can't use server-side prepared statements, temporary 
tables, ... or anything else that depends on subsequent statements 
hitting the same server process.


For an application I'm running, we have dozens of GUI clients keeping a 
JDBC connection open all day checking for updates, and with statement 
pooling this is handled well by 1-3 server processes (auto-scaled by 
pgbouncer).


Best regards,

    -hannes

David Kremer wrote:
> I have an API server and I'm trying to be conscientious managing Postgres's
> resources carefully. On the client side, I have a Hikari Pool.
> 
> Usually when I need a connection, I simply create a default read/write connection,
> even if I don't plan to make any updates or inserts or hold any locks.
> But most of my database connections are in fact read-only.
> 
> I saw that when you create a JDBC connection, you can specify readOnly=true.
> Would doing so somehow help Postgres manage its other connections? Perhaps Postgres,
> knowing that a connection is readOnly and will never even attempt to do an update,
> will free up some internal resources for other connections. Is this accurate?

It won't free any resources, but it is still a good idea if you use the SERIALIZABLE
isolation level.

https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE:

  For optimal performance when relying on Serializable transactions for concurrency
  control, these issues should be considered:

  - Declare transactions as READ ONLY when possible.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com