Search code examples
javamysqlpostgresqltransactionsjdbi

Is there any significant downside to just always using transactions in JDBI?

For tables that will never get too large, is there any downside to just using JdbiExecutor::executeInTransaction instead of JdbiExecutor::execute?

I'm trying to simplify a very large and complex piece of code so that new developers can onboard more quickly. If there isn't a significant difference in performance (which, even then, it would be premature optimization to address it here) then I'd like write a utility that just always uses JdbiExecutor::executeInTransaction.

Solution

  • PostgreSQL server operates in autocommit mode, so using explicit transactions will not only cause COMMIT statements to be sent to the database, but also BEGIN statements that start an explicit transaction.

    That means you could have up to 2 extra client-server round trips per transaction.

    Using explicit transactions only if you need to bundle multiple statements into a transaction might improve your performance.

    I would run a simple benchmark to see if the impact is noticeable; if not, go ahead and simplify your code.