Re: How to setup only one connection for the whole event loop?

Dominic:

On Fri, Feb 15, 2019 at 3:35 PM Dominic Gua�a
<organicchemistry_01(at)yahoo(dot)com(dot)ph> wrote:
> I am new to postgresql and I am creating a c program that can receive request from different users. I want to maximize the performance of postgresql so I intend to just create 1 connection that would service all queries of different users. How do I do this?
> Do I create a new connection each time there is a new request like how it goes in MySQL or can I just have one connection that is established in my `main` function?

Much more data is needed about your problem.

IF you are determined to use a single connection, you can stablish it
in main and keep it forever ( although it may acumulate some crud, but
there are methods to reset it and you can always reconnect every x
minutes ). No point in using a pooler here, if the DB is only for your
application or your connection is going to be really busy. OTOH, if
your connection is sporadic and you have more programs like yours
running against the same db it could help.

OTOH, a single connection should give you the fastest request response
time IN THE DB, but you need to queue requests on the connection. If
the requests for your program can come in paralell, using several
connections will give you better troughput. I mean, if you get 10
request 1 ms apart, it may happen that you need only 100ms db time for
each with a single connection, and 200ms using 10 connections, the 1st
case will have a average response time of nearly
(100+199+298..+991)/10 = 545.5 ms if my math is right, the second one
200.

Basically, to analyse this you need to state if the "requests" to your
C program are paralell. Postgres can do a lot of things in paralell,
specially if your server is multicore. If they are, using multiple
connections in your program will normally give you better throughput,
but you should limit the number of paralell connections to avoid
problems on spikes, there are many ways to do this, the simple way is
use a connection pooler and build a connection for each requests,
which is quite fast with things like a collocated pgbouncer instance,
or you can pool in your program, which is just the classic bounded
buffer with some syncinc stuff, if you want to squeeze the last
microsecond, but I would not recomend that ( anything DB related can
normally tolerate the minuscule delay of going to a properly
configured pooler, and the poolers give you a lot of things which are
difficult to get right, plus some bonus like resetting and recycling
connections, and you can easily test your program without it ).

That's is why I said more data is needed. If I narrow the problem to
"a c program which cannot process request in paralell, going against a
dedicated server", I'll say "single connection in main, potentially
recycling". If it is something like "C web server with DB server in
another machine", a pool will probably be better.

Francisco Olarte.