Prepared Statements and Pooling

From: Mister Junk <junkmail3568540(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Prepared Statements and Pooling
Date: 2015-08-11 04:44:07
Message-ID: CAHBHD-1-xp1uZnch_8hvFnkLoRyC6MTv4W6s8ivO7cz1CKHj_Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm working on a project for school that requires a simple java server (a
ServerSocket creates Socket connections which I send JSON strings over)
which then communicates with a database. Obviously I have chosen
PostgreSQL.

I'm using Apache DBCP for connection pooling. The DataSource is handed off
to each thread. Each thread then makes a Connection and then creates
statements, executes them, and then closes.

I'm using prepared statements to prevent SQL injection, but I have done
some reading and it seems like using Prepared statements COULD improve
performance. I understand the concept, but I do not know how to implement
this. JDBC has PreparedStatements. Do these correspond with the Prepared
Statements at the database level in postgres?

I read about the postgres prepared statements that they only work while a
connection is kept open. But in my application, each thread makes it's own
connection and then closes it. So I wouldn't be able to capitalize on the
prepared statement. However, it has occurred to me that since I am using
DBCP to pool connections, the Java Connection that I create is actually a
connection that is being kept open by the pool. Does the DBCP system
(poorly documented) manage the prepared statements?

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.

What's really frustrating is that I can't find anything to read about
this. I set up DBCP, but how can I be sure my connection pool is working.
How can I tell if prepared statements are really functioning by executing a
prepared statement, as opposed to parsing and planning each time I run a
query?

Where can I learn more about this? Any direct answers are also very
appreciated.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andomar 2015-08-11 07:32:20 Re: Prepared Statements and Pooling
Previous Message Albe Laurenz 2015-08-10 14:52:19 Re: conn = PQconnectdb(conninfo);