Given that there is just one simple SQL statement (example: select id from foo) that I need to execute on my Postgres 15 database, I am curious to know the performance impact of executing it using Play API's db.withTransaction vs using db.withConnection?
I suppose the former would be costlier since the database would need to start a transaction but I was hoping to get more details on the performance cost of using the former vs the latter.
According to the postgresql docs:
PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.
So, even if you don't use a transaction, it will still use a transaction. Having said that, I think the withTransaction version issues more statements, and given that it's just a simple select, I think you can use the withConnection version and never look back. But... you didn't tell us about this select statement. Are you just checking the state of the connection pool? Or do you need to be worried about the isolation level?
withConnectionandwithTransactioncomes from play API for databases that gives you a jdbc connection. Then you can use the connection directly or you can use different libs such as anorm or slick. Without an specific case, is hard to say which approach has better performance