[pgpool-general: 671] Re: pgpool 3.1.2 - reusing named prepared statements

Tatsuo Ishii ishii at postgresql.org
Thu Jun 28 17:02:45 JST 2012

> On Wed, Jun 27, 2012 at 6:42 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>> I am evaluating postgres connection poolers (pgbouncer and pgpool),
>>> specifically I’m looking for a way to allow reusing prepared
>>> statements on the backend connections across all frontend client
>>> connections.
>>> The use-case is for a handful of complex but performance critical
>>> queries which get executed by short lived client processes.  So
>>> ideally client app code would connect to pooler, and check if the
>>> prepared statement already exists for this connection (via
>>> pg_prepared_statement table).  If it doesn’t the client will create
>>> the statement(s), and execute them, then disconnect, but leaving the
>>> prepared statement allocated on the backend for use by a subsequent
>>> client connection.
>>> It doesn’t seem to work with pgpool (I found pgpool was calling
>>> DEALLOCATE in the connection cleanup code, regardless of whether it
>>> was configured in the reset_query_list configuration).
>>> Is this a known behavior?
>> Yes. Pgpool is designed to be as much transpaent as possible to
>> clients. That says, if client disconnects to pgpool, all per
>> PostgreSQL backend process resources including prepared statments
>> should disappear.
>> BTW, everytime you use a prepared stament, you are going to check
>> pg_prepared_statements? I'm not sure if it's cheaper than the cost to
>> parese a query.
> I see, that makes sense to keep it completely transparent for general
> use.  My case is for a highly specialized app, which allows for this
> more aggressive behavior.  As for the pg_prepared_statements query, I
> have done a quick prototype where I hacked out the DEALLOCATE call,
> and am getting approximately 85% TPS increase with the strategy I
> outlined above (this is on top of an initial 100% TPS increase just by
> using the pooler).

Impressive result.

> Do you see any potential technical problems or pitfalls from running a
> modified version of pgpool such as this?  Perhaps I should send to the
> pgpool-hackers mailer too...

For now I don't see any problems so far.
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

More information about the pgpool-general mailing list