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