Are there benefits to using a JDBC connection pool with embedded databases?

Question

Are there any advantages to use something like HikariCP with embedded databases like SQLite, H2 or HSQLDB? (the last two can also run in server mode but I'm not interested in that)

Apart from the performance benefit (that I suppose would be negligible with embedded databases) I'm also interested in other facilities provided by the connection pool that could make the code more concise and/or robust.

I think that this question is different from this other one in that it's more specific since it focuses on embedded databases, and, to a lesser degree, on HikariCP.

Answer 1

Connection pools exist primarily because opening a new connection from scratch is an expensive operation. Typically, it involves TCP/IP handshake, encryption and protocol negotiation and authentication. There is some overhead when closing connection, too.

So this ultimately boils down to implementation: is opening a new connection slow enough to warrant a reuse? A connection pool makes sense if the total time for opening connections is considerable compared to time consumed by other operations, or if opening a connection incurs a critical latency. For embedded databases, the difference should be minimal as they run in the same memory space as the program itself.

However, a connection pool also has a useful side effect because it limits the maximum number of simultaneous connections. On a server with no connection pooling, an attacker can easily send a flood of requests, exhausting memory or causing a denial of service.

From perspective of code clarity and abstraction, connection pools are also nice, because they are completely transparent. If you someday decide to move from embedded to client/server, you don't have to change anything.

Answer 2

I can think of only one case,

If you use in development embedded database and in production not embedded database

An embedded database is useful during the development phase of a project

Then you can reuse same code for connection pooling with different database without code change (using dependency injection).

In Spring case choose the second option:

Spring Jdbc's embedded database support can be extended in two ways:

1. Implement EmbeddedDatabaseConfigurer to support a new embedded database type, such as Apache Derby.

2. Implement DataSourceFactory to support a new DataSource implementation, such as a connection pool, to manage embedded database connections.