[rt-users] Changing database connection options?

[hubert depesz lubaczewski]

On Tue, Dec 14, 2010 at 10:43:18AM -0500, Kevin Falcone wrote:
> I'm delineating the right way to do it.
> 
> > $dbh->{'pg_server_prepare'} = 0;
> > from rt?
> > 
> > Reason - server side prepared statements are rarely useful, usually they
> > do not speed things up, and they break some setups.
> > 
> > Of course I can modify my own copy of rt, but patching it again and
> > again after every upgrade seems to be suboptimal.
> 
> I'm leery to make a change like that for RT without knowing much more
> about why I should be making it.

I am willing to provide all kind of information if you'd need.

the biggest problem with server side prepared statements is described
with examples in here:
http://www.depesz.com/index.php/2008/05/10/prepared-statements-gotcha/

they (server side prepared statements) make sense if you:
1. use complicated queries with many joins
2. in the time of single db connection you run given query many times
3. if you change parameters to the query - you don't change the ones
that can influence execution plan.

all in all - (in my opinion) chances of this are very slim, and in rt -
i haven't seen anything even resembling such queries.

on the other hand - there is program called pgbouncer. which is
connection pooler with great ability to reuse connection as soon as it
finishes transaction.

this is great from performance and resources-consumption standpoint, but
breaks if you use server side prepared statements - because they are
being used across many transactions.

finally - when using short, simple queries - prepared statements can
actually work worse than normal queries, because they need more
roundtrips client<->server to do the same task.

Best regards,

depesz

-- 
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz at depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007