Re: Prepared Statements and Pooling

> I have considered opening a single connection to the server and letting all threads execute statements through it, and use prepared statements (serviced by postgresql, not JDBC or DBCP). Or simply opening 50 connections and establishing prepared statements and then handing them off to the threads. Is there a reason I shouldn't do this? I read that the JDBC driver for postgres is thread safe.

Connection pooling reduces the overhead for creating a new connection, and allows Postgres to reuse execution plans for prepared statements. Both things will gain you a few milliseconds per query. For a small or medium sized project that is entirely irrelevant.

It is far more important to have stable and easy to understand code. To gain that, keep each connection open for as short a time as you can. Run a single query and then close it.

When the time comes where the few milliseconds matter, it is easy to add connection pooling without changing a single line of code. Postgres offers pgbouncer and pgpool for that job, but if DBCP can do it, that’s fine too.

Kind regards,
Andomar