Does creating readOnly connections, when possible, free up resources 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 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?
"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 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?
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