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

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